Help speeding up my code
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
Nick wrote:
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
|