ExcelBanter - Can this be done??

Ok, here you go Warren:

From the code you will see that you need a button called
"CommandButton1" and the word must be entered in Sheet1 in
cell B9. Right now the code can only "handle" 10 letter words
and time wise can only handle 7 letter words. At the bottom
I have copied some calculations on predicted time for 1-15 letter
words based on my slow laptop calculation speeds. Enjoy! Let me
know if it works and if you do anything to make it better/faster.

-Chris
---------------------------------
In sheet 1:
Private Sub CommandButton1_Click()

runme 'runs "runme" module

End Sub
----------------------------------
In a module called "checkspell":

Sub spellcheck()

Set objWord = Excel.Application

If objWord.CheckSpelling(wordtotest) Then
Sheet2.Cells(counter, 1).Value = wordtotest
counter = counter + 1
Else
counter = counter
End If

End Sub
----------------------------------
In a module called "Letterx":
Public char1, char2, char3, char4, char5, char6, char7, char8, char9, char10
Public a, b, c, d, e, f, g, h, i, j
Public wordtotest
Sub letter1()
a = 1
counter = counter
numberstochar
wordtotest = char1
spellcheck
End Sub
Sub letter2()
For a = 1 To 2
For b = 1 To 2
If a = b Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2
spellcheck
End If
Next b
Next a
End Sub
Sub letter3()
For a = 1 To 3
For b = 1 To 3
For c = 1 To 3
If a = b Or a = c Or b = c Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3
spellcheck
End If
Next c
Next b
Next a
End Sub
Sub letter4()
For a = 1 To 4
For b = 1 To 4
For c = 1 To 4
For d = 1 To 4
If a = b Or a = c Or a = d Or _
b = c Or b = d Or _
c = d Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 & char4
spellcheck
End If
Next d
Next c
Next b
Next a
End Sub
Sub letter5()
For a = 1 To 5
For b = 1 To 5
For c = 1 To 5
For d = 1 To 5
For e = 1 To 5
If a = b Or a = c Or a = d Or a = e Or _
b = c Or b = d Or b = e Or _
c = d Or c = e Or _
d = e Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 & char4 &
char5
spellcheck
End If
Next e
Next d
Next c
Next b
Next a
End Sub

Sub letter6()
For a = 1 To 6
For b = 1 To 6
For c = 1 To 6
For d = 1 To 6
For e = 1 To 6
For f = 1 To 6
If a = b Or a = c Or a = d Or a = e Or a = f
Or _
b = c Or b = d Or b = e Or b = f Or _
c = d Or c = e Or c = f Or _
d = e Or d = f Or _
e = f Then
counter = counter
Else:
numberstochar
wordtotest = char1 & char2 & char3 &
char4 & char5 & char6
spellcheck
End If
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
Sub letter7()
For a = 1 To 7
For b = 1 To 7
For c = 1 To 7
For d = 1 To 7
For e = 1 To 7
For f = 1 To 7
For g = 1 To 7
If a = b Or a = c Or a = d Or a = e Or a
= f Or a = g Or _
b = c Or b = d Or b = e Or b = f Or b = g
Or _
c = d Or c = e Or c = f Or c = g Or _
d = e Or d = f Or d = g Or _
e = f Or e = g Or _
f = g Then
counter = counter
Else:
numberstochar
End If
Next g
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
Sub letter8()
For a = 1 To 8
For b = 1 To 8
For c = 1 To 7
For d = 1 To 8
For e = 1 To 8
For f = 1 To 8
For g = 1 To 8
For h = 1 To 8
If a = b Or a = c Or a = d Or a = e Or a
= f Or a = g Or a = h Or _
b = c Or b = d Or b = e Or b = f Or b =
g Or b = h Or _
c = d Or c = e Or c = f Or c = g Or c =
h Or _
d = e Or d = f Or d = g Or d = h Or _
e = f Or e = g Or e = h Or _
f = g Or f = h Or _
g = h Then
counter = counter
Else:
numberstochar
End If
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a
End Sub
----------------------------------
In a module called "num2char":
Sub numberstochar()
If numchar = 1 Then
char1 = Mid(word, a, 1)
ElseIf numchar = 2 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
ElseIf numchar = 3 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
ElseIf numchar = 4 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
ElseIf numchar = 5 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
ElseIf numchar = 6 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
ElseIf numchar = 7 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
char7 = Mid(word, g, 1)
wordtotest = char1 & char2 & char3 & char4 & char5 & char6 & char7
spellcheck
ElseIf numchar = 8 Then
char1 = Mid(word, a, 1)
char2 = Mid(word, b, 1)
char3 = Mid(word, c, 1)
char4 = Mid(word, d, 1)
char5 = Mid(word, e, 1)
char6 = Mid(word, f, 1)
char7 = Mid(word, g, 1)
char8 = Mid(word, h, 1)
wordtotest = char1 & char2 & char3 & char4 & char5 & char6 & char7 &
char8
spellcheck
End If

End Sub
----------------------------------
In a module called "Run":
Public numchar As Integer
Public word As String
Public counter
Sub runme()
Dim start, finish, elapsed
start = Timer
counter = 1
word = Sheet1.Range("b9").Value
numchar = Len(word)
If numchar = 1 Then
letter1
ElseIf numchar = 2 Then
letter2
ElseIf numchar = 3 Then
letter3
ElseIf numchar = 4 Then
letter4
ElseIf numchar = 5 Then
letter5
ElseIf numchar = 6 Then
letter6
ElseIf numchar = 7 Then
letter7
ElseIf numchar = 8 Then
letter8
ElseIf numchar 8 Then
MsgBox ("Please choose a word with 8 characters or less.")
End If
finish = Timer
elapsed = finish - start
Sheet1.Range("D1").Value = elapsed
End Sub
------------------------------------
# letters iterations pred sec pred min pred hours pred days pred years
1 1 -0.009222578 -0.00015371 -2.56183E-06 -1.06743E-07 -2.92446E-10
2 2 0.012688987 0.000211483 3.52472E-06 1.46863E-07 4.02365E-10
3 6 0.100335247 0.001672254 2.78709E-05 1.16129E-06 3.18161E-09
4 24 0.494743416 0.008245724 0.000137429 5.7262E-06 1.56882E-08
5 120 2.598253648 0.043304227 0.000721737 3.00724E-05 8.23901E-08
6 720 15.7451926 0.262419877 0.004373665 0.000182236 4.99277E-07
7 5040 110.4031531 1.840052551 0.030667543 0.001277814 3.50086E-06
8 40320 883.4431636 14.72405273 0.245400879 0.010225037 2.80138E-05
9 362880 7951.237545 132.5206258 2.208677096 0.092028212 0.000252132
10 3628800 79512.65566 1325.210928 22.08684879 0.920285366 0.00252133
11 39916800 874639.5236 14577.32539 242.9554232 10.12314263 0.027734637
12 479001600 10495674.63 174927.9104 2915.465174 121.4777156 0.332815659
13 6227020800 136443770.5 2274062.842 37901.04736 1579.210307 4.32660358
14 87178291200 1910212788 31836879.79 530614.6632 22108.9443 60.57245014
15 1.30767E+12 28653191814 477553196.9 7959219.948 331634.1645 908.5867521
*I don't know if that will copy and paste back into excel or not....

"WarrenC" wrote:

Chris,

Please post the code. I would be very interested in it. From what I
remember with that Basic program I wrote years ago, 5 letters took
hours and 6 letters took almost overnight. Your's is much improved.

Warren

--
WarrenC
------------------------------------------------------------------------
WarrenC's Profile: http://www.excelforum.com/member.php...o&userid=35524
View this thread: http://www.excelforum.com/showthread...hreadid=553709