VBA array sort function?

ghz 7months ago ⋅ 66 views

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.

Answers

Implementing a sorting algorithm like QuickSort in VBA can be done efficiently. Here's a QuickSort implementation in VBA for sorting arrays:

QuickSort Implementation in VBA

This implementation will work with arrays in VBA and does not rely on Excel-specific functions, making it suitable for use in MS Project 2003.

Sub QuickSort(arr As Variant, ByVal low As Long, ByVal high As Long)
    Dim pivotIndex As Long

    If low < high Then
        pivotIndex = Partition(arr, low, high)
        QuickSort arr, low, pivotIndex - 1
        QuickSort arr, pivotIndex + 1, high
    End If
End Sub

Function Partition(arr As Variant, ByVal low As Long, ByVal high As Long) As Long
    Dim pivot As Variant
    Dim i As Long
    Dim j As Long
    Dim temp As Variant

    pivot = arr(high)
    i = low - 1

    For j = low To high - 1
        If arr(j) <= pivot Then
            i = i + 1
            ' Swap arr(i) and arr(j)
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
        End If
    Next j

    ' Swap arr(i + 1) and arr(high)
    temp = arr(i + 1)
    arr(i + 1) = arr(high)
    arr(high) = temp

    Partition = i + 1
End Function

Sub TestQuickSort()
    Dim myArray As Variant
    Dim i As Long

    ' Initialize the array
    myArray = Array(34, 7, 23, 32, 5, 62)

    ' Call QuickSort
    QuickSort myArray, LBound(myArray), UBound(myArray)

    ' Output the sorted array
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(i)
    Next i
End Sub

Explanation of the Code

  1. QuickSort Subroutine:

    • QuickSort takes an array arr, a starting index low, and an ending index high.
    • If low is less than high, it partitions the array and recursively sorts the two partitions.

  2. Partition Function:

    • Partition chooses the last element as the pivot and reorders the array so that all elements less than or equal to the pivot are on the left, and all elements greater than the pivot are on the right.
    • It then returns the index of the pivot element after partitioning.

  3. TestQuickSort Subroutine:

    • TestQuickSort initializes an array and calls the QuickSort subroutine.
    • It then prints the sorted array to the Immediate Window.

How to Use

  1. Insert the Code: Copy the above code into a new module in the VBA editor.
  2. Run the Test: Execute the TestQuickSort subroutine to see the sorting in action.

This QuickSort implementation should work efficiently for your sorting needs in MS Project 2003 without relying on any external libraries or Excel-specific functions.

#VB6 #vba