Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting list boxes in VBA (Word 2000 VBA)

    I am in the process of creating what to me is a complex VBA project for Word 2000 because of the need for the functionality and most of all the desire to learn. When I get stumped I am coming to you guys.

    All I am trying to do is cause a list box to sort its contents (single column) alphabetically. It is amazing to me that this is so easy in VB (just a property), but seems to require a lot in VBA (But I guess they are both done by Microsoft <img src=/S/smash.gif border=0 alt=smash width=30 height=26>). I have seen some suggestions for situations similar to my own which require the use of an array. I have tried to work with this, but I think my lack of understand of some of the syntax is preventing me from getting this to work.

    If someone could help me with that, or if they know of a better way, I would sure appreciate the help!! If you can please provide good comments so I will understand how this is working.

    If you suggest arrays, I want to understand:

    -what kind of array I should use (integer, variant, etc.).
    -how to add a dynamic number of items to the array (I assume I would add all of the contents of the list box to the array whether they be 2 or 20).
    -how to sort them once I get them there ("WordBasic.SortArray myArray()" caused in invalid procedure or argument error).
    -then how to take the sorted values and put them back in the list box (I am assuming that I will clear the list box before putting the sorted values back in).

    I know I'm asking a lot, or at least it seems like a lot to me, but it will really help my understanding of VBA to get a little bit of handle on arrays/sorting. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Thanks!!
    Troy

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    You're on the right track. Use arrays.

    Your 'nvalid procedure error'is caused by incorrect syntax. Remember, the SortArray method is a "function", so you need parens:

    wordbasic.sortarray(myarray())

    Type your array to whatever fits.

    If you don't add all the items at once, use Redim Preserve to grow the array w/o losing existing items.

    Push the array values into your list with

    ListOrComboBox.List = myarray()
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    Thanks for your response. I tried to follow what you gave me but I am getting a run time error.

    Let me give you what I have:

    ListElements is my array.
    lbIncludeTextIn is my list box.

    This is my code:

    Dim ListElements() As Variant 'Dimming my array.

    ListElements() = lbIncludeTextIn.List 'Adding list items to my array.
    WordBasic.SortArray (ListElements()) 'Sorting the array.
    lbIncludeTextIn.Clear 'Emptying the current contents of the list box.
    lbIncludeTextIn.List = ListElements() 'Filling list box with sorted contents of array.
    End Sub

    Thanks for your help!!
    Troy

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    Here's a way to sort it manually (using a bubble sort):

    <pre>Private Sub cmdSort_Click()

    Dim i As Integer
    Dim blnSorted As Boolean
    Dim strTemp As String

    blnSorted = False
    Do Until blnSorted
    For i = 0 To lbIncludeTextIn.ListCount - 2
    If lbIncludeTextIn.List(i) > lbIncludeTextIn.List(i + 1) Then
    strTemp = lbIncludeTextIn.List(i)
    lbIncludeTextIn.List(i) = lbIncludeTextIn.List(i + 1)
    lbIncludeTextIn.List(i + 1) = lbIncludeTextIn.List(i)
    Exit For
    End If

    If i = lbIncludeTextIn.ListCount - 2 Then
    blnSorted = True
    End If
    Next
    Loop
    End Sub</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    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: Sorting list boxes in VBA (Word 2000 VBA)

    > ListElements() = lbIncludeTextIn.List 'Adding list items to my array

    Unfortunately, the dynamic array is not that dynamic.

    Something more like this:

    <pre>Dim strArray() As String, intCounter As Integer
    ReDim strArray(Me.ListBox1.ListCount - 1)
    For intCounter = 0 To Me.ListBox1.ListCount - 1
    strArray(intCounter) = Me.ListBox1.List(intCounter)
    Next
    WordBasic.sortarray strArray()
    Me.ListBox1.List = strArray()</pre>


  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    What Kevin said in his original reply.

    I have had no success with WordBasic Sort since I moved to Word97.

    I broke the original problem down into smaller chunks a la kevin.

    I now have a set of utility functions that "move listbox to array" "sort array on nth column", "move array to listbox", along with "locate item in array", "locate item in listbox", "append item to listbox" and so on.

    It's a drawn-out process, but worth it once it is done.

    I should add that i have several variants on the Sort utilities - case-senesitive or not, Long, Character etc.

  7. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    You know what? This actually works, MOSTLY.

    How do I get it not to consider the case? For instance, I want "Test1" to come before "test2". Right now "test2" comes first because it has a lowercase "t".

    One other question, just for my learnin': What does the "Me." do when you add it before the list box name as you did 4 times below?

    Thanks!!
    Troy

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    Chris,
    Thanks for your reply. Unfortunately, I cannot access the link you included. If I put the full link it says "Page not found". If I put in part of the link, it says I am not authorized to open the page.

    I'd love to see these utilities.

    Thanks!!
    Troy

  9. #9
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    <P ID="edit" class=small>Edited by TroyWells on 23-Aug-01 11:37.</P>Geoff,
    Please see problem in 3.b below
    In the interest of learning <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>, I had a few questions about the code below:
    1. Could you give a breif explanation of what a "Bubble Sort" is? I have heard this term before, but did not understand it.
    2. What is the advantages/disadvantages of this code over what "jscher2000" posted below yours? Or is it just a different approach?
    3. I don't quite get what is happening in your first "If" statement. Obviously, this is the guts of the Bubble Sort, but I'm not sure I get what is happening. As I look at it, it seems you are looking at the first item in the list and comparing it to the second item. If the first item is greater (further down the alphabet or has a higher character code), you are switching the places of the first and second item in the list and so on to the end of the list.
    a. What is the purpose of the "strTemp = lbIncludeTextIn.List(i)" statement. It doesn't appear that you use "strTemp" later anywhere, so why do you define it here?
    b. In the statement "lbIncludeTextIn.List(i) = lbIncludeTextIn.List(i + 1)", what keeps "List(i)" from overwriting "List(i+1)",
    and visa-versa in "lbIncludeTextIn.List(i + 1) = lbIncludeTextIn.List(i)"? In fact, in my testing that seems to be exactly what happens!! Every item in "lbIncludeTextIn" is now named the same as what was previously the first item in the list.

    Thanks for your help!!
    Troy

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    > I cannot access the link you included

    My fault, and my apologies. The web site got changed last week and the idiot who changed it forgot to update my signature here. I'll have to have a talk with his boss ....


    In the meantime the upgraded signature here should work.



    >I'd love to see these utilities.

    I think that I have previously posted the source code to some of them here in the VBA forum. I mentioned the scope/number of them in response to your original query: yes, we end up writing a slew of routines. I suspect that the VBA design team didn't think through to the developer-as-user very well. Here we have these lovely GUI forms, and can ToolBox combo bars and listboxes with ease, but there's no inbuilt utility code to dop the basic work on the items.

  11. #11
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    I think we may be halfway there. I can get to your home page and look around (impressive!) <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>, but when I try to download anything, I get a page that says "Page not found".

    Thanks again for your help!!
    Troy

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    Hmmmm. Also I can't get there from the email they sent me (my original method of accessing my site).

    It maybe that netfirms is temporarily down. Let me try again and get back to you. I apologise for the inconvenience.


    <pre>Welcome to Netfirms Christopher Greaves,

    Congratulations on your new website hosted by Netfirms.com.
    Please print or save this e-mail because it contains
    important information about your website.

    Your new website is located at:
    http://greaves.netfirms.com
    </pre>


  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    Troy,

    1. A bubble sort is a simple sort algorithm. You go through the array until two elements are out of order, swap them, and start at the beginning of the array again- until the whole array is sorted.

    2. It's a different approach to JScher's code. It probably runs slower than an inbuilt function. But you could adapt this approach for something where the Wordbasic Sortarray does not work. And I'm not sure if Wordbasic commands are going to be sorted in the future.

    3. Whops. Change the line before "Exit for" to read<pre> lbIncludeTextIn.List(i + 1) = strTemp
    </pre>

    So the whole routine is<pre>Dim i As Integer
    Dim blnSorted As Boolean
    Dim strTemp As String

    blnSorted = False
    Do Until blnSorted
    For i = 0 To lbIncludeTextIn.ListCount - 2
    If lbIncludeTextIn.List(i) > lbIncludeTextIn.List(i + 1) Then
    strTemp = lbIncludeTextIn.List(i)
    lbIncludeTextIn.List(i) = lbIncludeTextIn.List(i + 1)
    lbIncludeTextIn.List(i + 1) = strTemp
    Exit For
    End If

    If i = lbIncludeTextIn.ListCount - 2 Then
    blnSorted = True
    End If
    Next
    Loop
    </pre>

    which might make it clearer- and work properly! The purpose is to swap the 2 elements- then exit the loop so that we can start again at the beginning of the list. It's not the most efficient way to sort an array- but it's the most easily codeed, and the effiiciency does not matter at all for the size of the arrays we're talking about here.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    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: Sorting list boxes in VBA (Word 2000 VBA)

    The t/T problem is inherent in SortArray(), so I can't help you there. I am going by the Word Developer's Kit for Word 6.0, so if anyone knows better, please speak up.

    "Me." refers to the object in which the code is embedded, the UserForm in this case. As Howard recently reminded me, it isn't necessary, just as you don't need to use Application. or Word. before Selection. However, typing Me. will pop up a useful list of all the controls in your form, as well as the various properties and methods that apply to the form.

    <img src=/w3timages/blackline.gif width=33% height=2>

    There is a very detailed discussion of array sorting, and a handy function, in Chapter 4 of Getz & Gilbert's VBA Developer's Handbook, but the license prohibits sharing the code. I didn't test it to see if it solves the t/T issue.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting list boxes in VBA (Word 2000 VBA)

    An additional note. You said, in another part of the thread,
    <hr>I want "Test1" to come before "test2". Right now "test2" comes first because it has a lowercase "t".<hr>
    . To achieve that in the code, try this:<pre>Dim i As Integer
    Dim blnSorted As Boolean
    Dim strTemp As String

    blnSorted = False
    Do Until blnSorted
    For i = 0 To lbIncludeTextIn.ListCount - 2
    ' Following line changed
    If lCase$(lbIncludeTextIn.List(i)) > _
    lCase$(lblIncludeTextIn.List(i + 1)) Then
    strTemp = lbIncludeTextIn.List(i)
    lbIncludeTextIn.List(i) = lbIncludeTextIn.List(i + 1)
    lbIncludeTextIn.List(i + 1) = strTemp
    Exit For
    End If

    If i = lbIncludeTextIn.ListCount - 2 Then
    blnSorted = True
    End If
    Next
    Loop
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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
  •