Sorting data using VBA: Quick Sort
2020-06-14 Sorting 0 320
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:
- Sorting two-dimensional arrays: SORT worksheet function
- Sorting a worksheet range: RangeSort
- Sorting a worksheet table: TableSort
- Sorting small arrays (<= 1000 items): BubbleSort
- Sorting a large one-dimensional array (> 1000 items): QuickSort (might use a lot of memory)
- Sorting a large one-dimensional array (> 1000 items): ShellSort (uses less memory than QuickSort)
- Sorting a large two-dimensional array (> 1000 items): MergeSort
RangeSort, TableSort, BubbleSort, MergeSort and the SORT worksheet function 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.