Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts

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

    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 <A target="_blank" HREF=http://www.msofficepro.com/features/default.asp>here</A>, 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

  2. #17
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)) < UCase(varTestVal)
    lngLow = lngLow + 1
    Loop
    End If

    If NoCase = False Then
    Do While varArray(lngHigh) > 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

  3. #18
    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 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 <img src=/S/doh.gif border=0 alt=doh width=15 height=15>. 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

  4. #19
    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)

    <hr>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<hr>
    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #20
    New Lounger
    Join Date
    Aug 2001
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Page 2 of 2 FirstFirst 12

Posting Permissions

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