Sorting data using VBA: Quick Sort

 2020-06-14    Sorting    0   

QuickSort: Sorting large one-dimensional arrays:
QuickSort is very fast and is suitable for sorting large one-dimensional arrays.
Use QuickSort when memory use is not a concern, use ShellSort when less memory use is important, ShellSort is a bit slower than QuickSort.
QuickSort is not a stable sort method, you would usually not want to adapt this sort method for use with two-dimensional arrays, use MergeSort.

NOTE: The sorting algorithm in this document is adapted for use in VBA based on detailed descriptions found in Wikipedia.

Sub QuickSort(varArray As Variant, Optional blnCompareText As Boolean = False, Optional blnSortAscending As Boolean = True, _
    Optional lngFirst As Long = -1, Optional lngLast As Long = -1)
' updated 2015-12-12 by OPE
' sorts a one-dimensional array variable in ascending or descending order
' very fast sorting on random data (n Log n comparisons), might use a lot of stack space
' example: QuickSort varArrayVariable
Dim lngLow As Long, lngHigh As Long, lngMiddle As Long, varMiddleVal As Variant, varTemp As Variant
    If Not IsArray(varArray) Then Exit Sub ' no array
    If UBound(varArray) - LBound(varArray) + 1 < 2 Then Exit Sub ' no need to sort

    If lngFirst = -1 Then lngFirst = LBound(varArray) ' initial lower bound
    If lngLast = -1 Then lngLast = UBound(varArray) ' initial upper bound
    
    If lngFirst < lngLast Then ' more than 1 item in the array
        lngMiddle = (lngFirst + lngLast) \ 2 ' the middle array item
        varMiddleVal = varArray(lngMiddle) ' the middle array item value
        lngLow = lngFirst
        lngHigh = lngLast
        Do
            If blnSortAscending Then
                If blnCompareText Then
                    Do While StrComp(varArray(lngLow), varMiddleVal, vbTextCompare) < 0
                        lngLow = lngLow + 1
                    Loop
                    Do While StrComp(varArray(lngHigh), varMiddleVal, vbTextCompare) > 0
                        lngHigh = lngHigh - 1
                    Loop
                Else
                    Do While varArray(lngLow) < varMiddleVal ' find first item >= middle array item
                        lngLow = lngLow + 1
                    Loop
                    Do While varArray(lngHigh) > varMiddleVal ' find first item <= middle array item
                        lngHigh = lngHigh - 1
                    Loop
                End If
            Else
                If blnCompareText Then
                    Do While StrComp(varArray(lngLow), varMiddleVal, vbTextCompare) > 0
                        lngLow = lngLow + 1
                    Loop
                    Do While StrComp(varArray(lngHigh), varMiddleVal, vbTextCompare) < 0
                        lngHigh = lngHigh - 1
                    Loop
                Else
                    Do While varArray(lngLow) > varMiddleVal ' find first item <= middle array item
                        lngLow = lngLow + 1
                    Loop
                    Do While varArray(lngHigh) < varMiddleVal ' find first item >= middle array item
                        lngHigh = lngHigh - 1
                    Loop
                End If
            End If
            If lngLow <= lngHigh Then ' swap the items
                varTemp = varArray(lngLow)
                varArray(lngLow) = varArray(lngHigh)
                varArray(lngHigh) = varTemp
                lngLow = lngLow + 1
                lngHigh = lngHigh - 1
            End If
        Loop While lngLow <= lngHigh
        If lngFirst < lngHigh Then QuickSort varArray, blnCompareText, blnSortAscending, lngFirst, lngHigh ' sort the lower half of the array
        If lngLow < lngLast Then QuickSort varArray, blnCompareText, blnSortAscending, lngLow, lngLast ' sort the upper half of the array
    End If
End Sub

Here are some of the options you can use to sort data in Excel using VBA: Stable sort methods:
RangeSort, TableSort, BubbleSort and MergeSort are stable sort methods:
items with equal sort keys are returned in the same order in the sorted result as they had before being sorted.

QuickSort and ShellSort are not stable sort methods and you would normally not want to use these with two-dimensional arrays.

See this post if you need information about how to read from and write to worksheet cells using array variables.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.