• Sorting list boxes in VBA (Word 2000 VBA)

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Sorting list boxes in VBA (Word 2000 VBA)

    Author
    Topic
    #359400

    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 smash). 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. bow

    Thanks!!
    Troy

    Viewing 3 reply threads
    Author
    Replies
    • #538615

      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()

      • #538689

        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

        • #538690

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

          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
          • #538705

            Edited by TroyWells on 23-Aug-01 11:37.

            Geoff,
            Please see problem in 3.b below
            In the interest of learning brainwash, 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

            • #538799

              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

                    lbIncludeTextIn.List(i + 1) = strTemp
              

              So the whole routine is

              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
              

              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.

            • #538834

              An additional note. You said, in another part of the thread, [indent]


              I want “Test1” to come before “test2”. Right now “test2” comes first because it has a lowercase “t”.


              [/indent]. To achieve that in the code, try this:

              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
              
            • #539035

              Thanks to all who gave such wonderful and teaching advice. I learned a lot from all of you. Feel free to continue if you have something to add, but at this point, I think I am going to go with Geoff’s solution.

              HOWEVER, I found that I need to add a couple of lines or I ended up with an endless loop if I moved one item to a list that was already empty:

              ‘Added this first “If line and the corresponding “End If” below
              If lbIncludeTextIn.ListCount > 1 Then
              Dim blnSorted As Boolean
              Dim strTemp As String
              blnSorted = False
              Do Until blnSorted
              For i = 0 To lbIncludeTextIn.ListCount – 2
              If LCase$(lbIncludeTextIn.List(i)) > LCase$(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
              End If

              Thanks BigKev for the QuickSort demo. It was quite honestly over my head doh. I couldn’t quite figure out how it worked or where to put the list name.

              Thanks Gary for the referral to MS OfficePro. My trial period just expired, but I think I’m going to buy a subscription. Seems like there is a lot of good stuff there.

              Thanks also to jscher2000 for your posts. You started me off simple, which helped me understand the more complex posts of yourself and others.

              I look forward to seeing what you have Chris. Let us know when your site is back in working order!! Thanks also to Kevin for your initial post.

              You guys are the best!!
              Troy

            • #539045

              [indent]


              HOWEVER, I found that I need to add a couple of lines or I ended up with an endless loop if I moved one item to a list that was already empty


              [/indent]Well done Troy.

              Code which gets posted is not warranted, airtight or anything. If it’s not perfect, but you can find and fix the holes, you’re on your way to becomeing better at VBA. And we all learn something in the process.

        • #538691

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

          Unfortunately, the dynamic array is not that dynamic.

          Something more like this:

          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()
          • #538703

            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

            • #538831

              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.

              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.

            • #538842

              In the August 2001 issue of MS OfficePro magazine Romke Soldaat presented an all-purpose sorting routine.

              The article (and link to downloadable code) can be accesssed here, but you’ll need to be a subscriber (or register for a trial subscription) in order to access the article and code online.

              I haven’t tried the code yet but it looks worthwhile.

              Gary

    • #538701

      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.

      • #538707

        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

        • #538713

          > 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.

          • #538784

            I think we may be halfway there. I can get to your home page and look around (impressive!) bravo, but when I try to download anything, I get a page that says “Page not found”.

            Thanks again for your help!!
            Troy

            • #538786

              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.

              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
              
    • #538853

      Here’s a QuickSort Sub for sorting arrays. It will be faster than the bubble sort for large arrays. Note that the NoCase parameter is set to True by default. Set it to False if you want to sort by case. If you sort caseless then it will not sort by case within a case, if you know what I mean. e.g. the order of A and a are indeterminate. The NoCase parameter should be left as True or omitted if you are not sorting strings. You can also use this routine to sort parts of the array by using the lngFirst and lngLast parameters. If the whole array is to be sorted then omit these parameters in the call.
      Note also that the array is also one dimensional and it sorts only the whole record.

      Public Sub SortArray(varArray As Variant, _
      Optional NoCase As Boolean = True, _
      Optional lngFirst As Long = -1, _
      Optional lngLast As Long = -1)

      ‘ QuickSort algorithm used to sort the items
      ‘ in the varArray array.

      Dim lngLow As Long
      Dim lngHigh As Long
      Dim lngMiddle As Long
      Dim varTempVal As Variant
      Dim varTestVal As Variant

      If lngFirst = -1 Then lngFirst = LBound(varArray)
      If lngLast = -1 Then lngLast = UBound(varArray)

      If lngFirst < lngLast Then
      lngMiddle = (lngFirst + lngLast) / 2
      varTestVal = varArray(lngMiddle)
      lngLow = lngFirst
      lngHigh = lngLast
      Do

      If NoCase = False Then
      Do While varArray(lngLow) < varTestVal
      lngLow = lngLow + 1
      Loop
      Else
      Do While UCase(varArray(lngLow)) varTestVal
      lngHigh = lngHigh – 1
      Loop
      Else
      Do While UCase(varArray(lngHigh)) > UCase(varTestVal)
      lngHigh = lngHigh – 1
      Loop
      End If

      If (lngLow <= lngHigh) Then
      varTempVal = varArray(lngLow)
      varArray(lngLow) = varArray(lngHigh)
      varArray(lngHigh) = varTempVal
      lngLow = lngLow + 1
      lngHigh = lngHigh – 1
      End If
      Loop While (lngLow <= lngHigh)

      If lngFirst < lngHigh Then SortArray varArray, NoCase, lngFirst, lngHigh
      If lngLow < lngLast Then SortArray varArray, NoCase, lngLow, lngLast

      End If

      End Sub

      Regards,

      Kevin Bell

    • #539790

      This may help – Cut from code working in Word 2000

      If arrPeople(2, lngI) “” Then
      ReDim Preserve arrWorkers(lngW)
      arrWorkers(lngW) = arrPeople(2, lngI)
      lngW = lngW + 1
      End If
      Next lngI
      WordBasic.sortarray arrBosses
      WordBasic.sortarray arrWorkers

      Me.comPartnerContact.List = arrBosses
      Me.comContactPerson.List = arrWorkers

    Viewing 3 reply threads
    Reply To: Sorting list boxes in VBA (Word 2000 VBA)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: