Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Alphabetical sorting (W97)

    Can anyone tell me if it is possible to get VBA to sort a selection of words that have been entered in fields on a user form into alphabetical order?

    The words are being entered one to a field, but need to be pasted into the document in alphabetical order and at the moment I'm selecting them and sorting them myself afterwards ...

    Many thanks
    Beryl M


  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Alphabetical sorting (W97)

    How about assigning them to a string array and using WordBasic.SortArray ?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    Sounds interesting - but I only came to programming with VBA in Oficce97 and, although I've heard mention of WordBasic, I don't know anything about it. Would you mind going into a little more detail, pls?

    Many thanks
    Beryl M


  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Alphabetical sorting (W97)

    Here's the general idea:

    Dim strArray(1 To 9) As String 'make space for 9 items
    strArray(1) = Me.TextBox1.Value
    ...
    strArray(9) = Me.TextBox9.Value
    WordBasic.SortArray strArray()
    ...
    Dim i As Integer
    With Selection
    For i = 1 To 9
    .TypeText strArray(i)
    .TypeParagraph
    Next
    End With

    The items should appear in alphabetical order in the document. Note that SortArray is somewhat unpredictable when it comes to sorting upper and lower case letters. I haven't played with it enough to figure out its strategy.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    That certainly sounds like it should do the trick ... it's the arrays that get me! <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> My friend who put up with the blue air when I was learning VBA at first* has given up repeating how to use them for me, it just won't sink in for some reason!

    Still, I'm always hopeful and keep trying ...

    *My company-at-the-time's idea of teaching a complete non-programmer to program from scratch was to buy me a copy of 'VBA programming for Dummies' and tell me to get on with it! The fact that I got anywhere at all was entirely due to said friend and the excellence of the Dummies books ...
    Beryl M


  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    Hi jscher, I've just been playing with your code and it works wonderfully, but there's just one thing I've been trying to do - the list of words can be anything from 20 to 40 words long, with no way of telling the exact number until the form is filled in. This doesn't matter until the words are pasted into the document; mine are separated by commas, and at the moment I'm ending up with a line of space, comma, space, comma up to the limit of 40.

    I've a vague feeling there's a type of loop that will run until a certain requirement is met, which is what I think I need here (ie loop until one of the fields is blank then stop) ... am I on the right track here?
    Beryl M


  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Alphabetical sorting (W97)

    If all the "real" data is contiguous from the beginning of the array, then you could just stop at the first blank:

    If Trim(strArray(i)) = vbNullString Then Exit For

    If there might be blanks anywhere in the array, testing the value of each element before inserting seems to be the only practical way to deal with the blanks.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    Again, that certainly sounds like it does the trick - but when I added the line to the end of the For loop (immediately before Next) it didn't work. I'm not sure what it did but it just gave me one comma (the separator between the items in the list) and no words at all!

    What am I doing wrong? <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    jscher

    I've been fiddling with this, trying to get it to work (latest being dimming a variable to use with trim) but it just returns nothing. If I use a breakpoint to see what the variable's value is, it says it's nothing!

    <pre> With Selection
    For i = 1 To 40
    Dim aString As String
    aString = strArray(i)
    If Trim(aString) = vbNullString Then
    Exit For
    Else
    .TypeText strArray(i)
    .TypeText Text:=", "
    End If
    Next
    End With</pre>


    Where am I going wrong ??!!! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Alphabetical sorting (W97)

    Maybe the array is empty? If you put a stop before your For loop and check out View,Locals Window in the editor, you should be able to see all of the elements of the array as they stand at the outset. If they are empty, then either there is a problem with the way they are assigned in, or you might have an extra Dim statement that is zapping the whole array in one go...

    Uh-oh, new thought, what if the SortArray is putting the blank elements at the beginning, so your loop exits when i = 1? In that case, try this loop instead:
    <pre> Dim aString As String
    With Selection
    For i = 1 To 40
    aString = strArray(i)
    If Trim(aString) <> vbNullString Then
    .TypeText aString & ", "
    End If
    Next
    End With</pre>

    Hope this helps.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Alphabetical sorting (W97)

    That was it! Many thanks, Jefferson. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Posting Permissions

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