Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / December 2006

Tip: Looking for answers? Try searching our database.

Help speeding up my code

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Nick - 14 Dec 2006 23:03 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.