Hi guys, I've written some code that finds four letter words that don't
have repeating letters (ex: Foot wouldn't count, two O's) The problem
is that it takes a long time to generate them all. Below is my code, if
anyone wants to take a look at it and suggest performance tweaks that'd
be great. If you have a different method/approach to getting the list
of 4 letter words that might be faster I'd like to hear that too.
Sub TotalRebuild()
GoodToGo = MsgBox("This will rebuild the whole wordlist, and it takes a
Long time, over 10 minutes. Are you sure?", vbOKCancel, "Warning, this
takes forever")
If GoodToGo = vbCancel Then End
Application.ScreenUpdating = False
'Application.WindowState = xlMinimized
'Application.Visible = False
Range("A:A").ClearContents 'clear column A so we can put the words in
For One = 65 To 90 'Set up loop for the first letter
o = Chr(One) ' variable o holds first letter
For Two = 65 To 90 'set up loop for second letter
t = Chr(Two) ' variable t holds second letter
For Three = 65 To 90 'set up loop for third letter
r = Chr(Three) 'variable r holds third letter
For Four = 65 To 90 'set up loop for fourth letter
f = Chr(Four) 'variable f holds fourth letter
dupe = 0 'reset duplicate variable
If o = t Then dupe = 1 ' if letters repeat, then
dupe = 1
If o = r Then dupe = 1
If o = f Then dupe = 1
If t = r Then dupe = 1
If t = f Then dupe = 1
If r = f Then dupe = 1
If dupe = 0 Then ' if no leters repeat, then put
the letters together
word = o & t & r & f
If Application.CheckSpelling(word) = True Then
' spellcheck the word
rw = rw + 1 'increment what row we put the
word in
Cells(rw, 1) = word 'put the word in the
row rw, column 1
End If
End If
Next Four
Next Three
Next Two
Next One
'Application.Visible = True
'Application.WindowState = xlMaximized
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
Andrew Taylor - 14 Dec 2006 23:23 GMT
The most obvious optimisation is not to go into the inner
loops as soon as you know you've already got a repeated
letter. So after
For Two = 65 To 90 'set up loop for second letter
t = Chr(Two) ' variable t holds second letter
add
If o <> t then
with matching End if just before Next Two
and after the line r=Chr(Three)
add
if r<>o and r<>t (+ matching End if)
This hugely cuts down the number of iterations, and in the
innermost loop you only need to check that f is not equal
to any one of o,t,r.
A generally quicker approach is probably to find a word
list (see http://wordlist.sourceforge.net/ for some
sources) and run through that looking for suitable words.
> Hi guys, I've written some code that finds four letter words that don't
> have repeating letters (ex: Foot wouldn't count, two O's) The problem
[quoted text clipped - 65 lines]
>
> End Sub
John Coleman - 15 Dec 2006 02:15 GMT
The problem is the line
If Application.CheckSpelling(word) = True Then
No amount of tweaking can help if the algorithm involves calling it
hundreds of thousands of times (which you would have to do even if you
try optimizations like only testing strings that have at least one
vowel). Over 99% of the execution time is taken up in evaluating that
condition. To see this, replace
If Application.CheckSpelling(word) = True Then
By (for example)
If word Like A*Z Then
And your code should take mere seconds to execute (I don't know about
*your* code - but I had a similar speed up in my failed attempt to
tweak your code - you should see something similar). I don't know what
the problem with CheckSpelling is - but it isn't very useful for large
collections of words.
If you have already run this code once - why not write the results to a
text file which you can open when needed?
HTH
-John Coleman
> Hi guys, I've written some code that finds four letter words that don't
> have repeating letters (ex: Foot wouldn't count, two O's) The problem
[quoted text clipped - 65 lines]
>
> End Sub
Nick - 15 Dec 2006 03:09 GMT
Yes, I knew that the vast majority of the time is spent on the
CheckSpelling(word) part. I had also thought about checking for
duplicated letters as the loops played out (as suggested) but hadn't
thought hard about how to do that. I can see where that would save some
time. Going and getting a word list would have made the exercise moot,
and the goal was to generate the list, not just go download it.
John, I'm not sure how "If word like A*Z Then" would be similar to
spell checking. At some point in the process I have to make sure the
word is in the Office dictionary. Do you know a way of cutting out some
of the generated "words" prior to checking them against the
spellchecker?
> The problem is the line
>
[quoted text clipped - 24 lines]
>
> -John Coleman
John Coleman - 15 Dec 2006 04:46 GMT
"If word like "A*Z" " isn;t similar to spell checking - it was just a
random boolean condition designed to *replace* spell checking to
confirm that it was in fact spell checking which was the bottle neck.
Here is my attempt at a tweak, featuring a rare appearance of a GoTo:
Sub MakeWordList()
Dim i As Long, j As Long, k As Long, l As Long
Dim letters As Variant, word As String
Dim R As Range, counter As Long
Application.ScreenUpdating = False
Range("A:A").ClearContents
Set R = Range("A1")
For i = 65 To 90
For j = 65 To 90
If j = i Then GoTo continuej
For k = 65 To 90
If k = j Or k = i Then GoTo continuek
For l = 65 To 90
If l = k Or l = j Or l = i Then GoTo continuel
word = Chr(i) & Chr(j) & Chr(k) & Chr(l)
If HasVowels(word) Then
If Application.CheckSpelling(word) = True Then
R.Offset(counter).Value = word
counter = counter + 1
End If
End If
continuel:
Next l
continuek:
Next k
continuej:
Next j
Next i
Application.ScreenUpdating = True
End Sub
Function HasVowels(S As String) As Boolean
HasVowels = (S Like "*A*") Or (S Like "*E*") Or _
(S Like "*I*") Or (S Like "*O*") Or _
(S Like "*U*") Or (S Like "*Y*")
End Function
I was assuming at first that you wanted English words and not
abbreviations -but the Excel SpellCheck allows for all sorts of
non-word abbreviations like ABMS (for American Board of Medical
Specialies). If you want these - then my HasVowels function doesn't
really make sense (why include such things only when they have a
vowel?) and should be removed. If you don't want such abbreviations-
you need something other than the default dictionary.
The above code only assembles potential words when they have no repeats
- so you only need to look at 26*25*24*23 rather than 26^4. Also - if
you are happy throwing away the ones without vowels, it saves an
additional 20*19*18*17 calls to Application.CheckSpelling.
HTH
-John
> Yes, I knew that the vast majority of the time is spent on the
> CheckSpelling(word) part. I had also thought about checking for
[quoted text clipped - 37 lines]
> >
> > -John Coleman
Nick - 15 Dec 2006 14:49 GMT
The idea of checking for a vowel before spellchecking will probably
yield the best performance increase. Thanks.
> "If word like "A*Z" " isn;t similar to spell checking - it was just a
> random boolean condition designed to *replace* spell checking to
[quoted text clipped - 99 lines]
> > >
> > > -John Coleman
Andrew Taylor - 15 Dec 2006 12:22 GMT
> Yes, I knew that the vast majority of the time is spent on the
> CheckSpelling(word) part. I had also thought about checking for
> duplicated letters as the loops played out (as suggested) but hadn't
> thought hard about how to do that. I can see where that would save some
> time. Going and getting a word list would have made the exercise moot,
> and the goal was to generate the list, not just go download it.
Fair enough, but you're already implicitly using a word list:
namely the one used by the Office spell-checker.
> John, I'm not sure how "If word like A*Z Then" would be similar to
> spell checking. At some point in the process I have to make sure the
[quoted text clipped - 30 lines]
> >
> > -John Coleman
Dana DeLouis - 15 Dec 2006 07:06 GMT
> ...and suggest performance tweaks that'd be great.
Hi. Don't know if this idea would be any faster.
How about the first loop being 1 to 5, since we know we must have at least
one Vowel (v).
Hard code all 24 permutations so that we can sequence in our vowel.
Again, don't know if this general outline is faster though.
For p = 1 To 5
v = Choose(p, "a", "e", "i", "o", "u")
For i = 65 To 88
For j = i + 1 To 89
For k = j + 1 To 90
'Test all 24 Permutations here
'1 {v, i, j, k}
'2 {v, i, k, j}
'3...etc
'24 {k, j, i, v}

Signature
HTH :>)
Dana DeLouis
Windows XP & Office 2003
> Hi guys, I've written some code that finds four letter words that don't
> have repeating letters (ex: Foot wouldn't count, two O's) The problem
[quoted text clipped - 65 lines]
>
> End Sub