arrayssortingvbavb6ms-project

VBA array sort function?


I'm looking for a decent sort implementation for arrays in VBA. A Quicksort would be preferred. Or any other sort algorithm other than bubble or merge would suffice.

Please note that this is to work with MS Project 2003, so should avoid any of the Excel native functions and anything .net related.


Solution

  • Take a look here:
    Edit: The referenced source (allexperts.com) has since closed, but here are the relevant author comments:

    There are many algorithms available on the web for sorting. The most versatile and usually the quickest is the Quicksort algorithm. Below is a function for it.

    Call it simply by passing an array of values (string or numeric; it doesn't matter) with the Lower Array Boundary (usually 0) and the Upper Array Boundary (i.e. UBound(myArray).)

    Example: Call QuickSort(myArray, 0, UBound(myArray))

    When it's done, myArray will be sorted and you can do what you want with it.
    (Source: archive.org)

    Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
      Dim pivot   As Variant
      Dim tmpSwap As Variant
      Dim tmpLow  As Long
      Dim tmpHi   As Long
    
      tmpLow = inLow
      tmpHi = inHi
    
      pivot = vArray((inLow + inHi) \ 2)
    
      While (tmpLow <= tmpHi)
         While (vArray(tmpLow) < pivot And tmpLow < inHi)
            tmpLow = tmpLow + 1
         Wend
    
         While (pivot < vArray(tmpHi) And tmpHi > inLow)
            tmpHi = tmpHi - 1
         Wend
    
         If (tmpLow <= tmpHi) Then
            tmpSwap = vArray(tmpLow)
            vArray(tmpLow) = vArray(tmpHi)
            vArray(tmpHi) = tmpSwap
            tmpLow = tmpLow + 1
            tmpHi = tmpHi - 1
         End If
      Wend
    
      If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
      If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
    End Sub
    

    Note that this only works with single-dimensional (aka "normal"?) arrays. (There's a working multi-dimensional array QuickSort here.)