Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Generating Permutations (Excel 2000>)

    The GetString subroutine prompts the user for a string. If the length of the string is greater than 1 and less than 8, the GetPermutations subroutine is called --which then calls itself. The permutations are stored in column A of the worksheet.

    My Q:
    Once I have the permutations, is there a way to delete all invalid words so that only valid dictionary based words remain. Something like running a spell check that simply deletes the "entirerow" where an incorrectly spelt words is???


    Option Explicit
    Dim CurrentRow

    Sub GetString()
    Dim InString As String
    InString = InputBox("Enter text to permute:")
    If Len(InString) < 2 Then
    MsgBox "Cannot permutate one character!"
    Exit Sub
    End If
    If Len(InString) >= 8 Then
    MsgBox "Too many permutations!"
    Exit Sub
    Else
    ActiveSheet.Columns(1).Clear
    CurrentRow = 1
    Call GetPermutation("", InString)
    End If
    End Sub

    Sub GetPermutation(x As String, y As String)
    ' The source of this algorithm is unknown
    Dim i As Integer, j As Integer
    j = Len(y)
    If j < 2 Then
    Cells(CurrentRow, 1) = x & y
    CurrentRow = CurrentRow + 1
    Else
    For i = 1 To j
    Call GetPermutation(x + Mid(y, i, 1), _
    Left(y, i - 1) + Right(y, j - i))
    Next
    End If
    End Sub
    Regards,
    Rudi

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating Permutations (Excel 2000>)

    Rudi,

    Try this:

    Sub CheckSpelling()
    CurrentRow = 1
    Do Until IsEmpty(Cells(CurrentRow, 1))
    If Application.CheckSpelling(Cells(CurrentRow, 1)) Then
    CurrentRow = CurrentRow + 1
    Else
    Sheet1.Rows(CurrentRow).EntireRow.Delete
    End If
    Loop
    End Sub
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating Permutations (Excel 2000>)

    Can't offer any info on spellchecker output, Rudi. But I was thinking of an alternative approach. There are several <img src=/S/free.gif border=0 alt=free width=30 height=15> DOS commandline anagram generators out there. All those I've seen will write output only to screen or text file. If you use the latter, then a bit of Windows Scripting from your macro, it shouldn't be hard to achieve the result you're after. This may not be what you're trying to achieve at all though. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Alan

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating Permutations (Excel 2000>)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> If you're after an example, Rudi, I believe Wordsmith.org's Internet Anagram Server is the original (although not necessarily the best - it throws up some very odd words sometimes!).
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Thx for the replies Dave and Alan. I know my request simulates anagram programs. I was asking this Q more for the sake of the VBA code than to create an Excel based anagram utility.

    Dave, I'll give your cade a try as soon as I get the chance.
    Tx
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating Permutations (Excel 2000>)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    Not having used GetSpelling before, my main discovery was that there are several GetSpelling functions. The one I used, application.GetSpelling is a "genuine" VBA function in that it returns true or false, whereas <!t>[Range]<!/t>.GetSpelling runs the usual spell checker (by displaying the spell checker dialogue box).

    Hope this helps,
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Hi Rudi

    Maybe you should use a mixture of Dave's code and your code as you have obviously limited your code so that the return fits into an excel spreadsheet (40320). By doing your spell check prior to writing it into the next cell can say if it is valid or not, further this will allow you to have longer words and use less of the spreadsheet.
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating Permutations (Excel 2000>)

    Nice find there Dave. Well worth a bookmark I'd say! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Alan

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Wow...thx Dave. This is some great advice. I'll test this out tonight when I get home.
    Cheers
    Regards,
    Rudi

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Long time no talk...howzit buddy!!!!!

    Interesting suggestion. I will try it out and post some working code when I get time to experiment with this tonight!
    Tx
    Regards,
    Rudi

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Rudi

    I had a play with this tonight and I think I have a workable solution...just but it will need a tweak here and there:

    1) Try the word TIMES and you get a good hit

    2) Try TOOLS and you get a repitition as there is 2 O's in the word

    I suggest that now it works you could output to txt but this works and cuts down the row return.

    So big congrats to Waggers as well for giving the spellcheck idea
    Jerry

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Hi Jezza,
    I created 2 variations of the code. Just modify the commented areas to create the two variations. Your suggestion works well, but it will make the code run for quite some time if you start adding words of 9 or 10 or 11 characters. The permutations become rather large!!!

    This was really a great exercise in code...
    Thx again to Dave for that great tip with Application.CheckSpelling.

    PS: Dave...you saisd in you post; Aplication.GETSPELLING??? I assume that is a misprint?

    Cheers
    Regards,
    Rudi

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    Hi Rudi

    Almost similar but not enough to quibble about <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Yep fun exercise
    Jerry

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    As a BTW try your new excel application with supersonic and roastmules <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    and no cheating using wordsmith.org!!!!!!!
    Jerry

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating Permutations (Excel 2000>)

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> for sure!!!!
    I quit the macro after a couple of minutes. There are just too many permutations to hang around waiting to see what comes up!!!!
    So what was the purpose of that exercise?
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •