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