Sorting data using VBA: Shell Sort
2020-06-14 Sorting 0 269
ShellSort: Sorting large one-dimensional arrays:
ShellSort is very fast and is suitable for sorting large one-dimensional arrays.
Use ShellSort when less memory use is important, ShellSort is a bit slower than QuickSort.
ShellSort 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 ShellSort(varArray As Variant, Optional blnCompareText As Boolean = False, Optional blnSortAscending As Boolean = True)
' updated 2015-08-01 by OPE
' sorts a one-dimensional array in ascending or descending order
' fast sorting (around 5% slower than QuickSort) but does not as use much stack space as QuickSort
' example: ShellSort varArrayVariable
Dim lngCount As Long, OK As Boolean, lngGap As Long, i As Long, j As Long, varTemp As Variant
If Not IsArray(varArray) Then Exit Sub ' no array
lngCount = UBound(varArray, 1) - LBound(varArray, 1) + 1
lngGap = lngCount
Do While lngGap > 1
lngGap = lngGap \ 2
Do
OK = True
For i = 1 To lngCount - lngGap
j = i + lngGap
If blnCompareText Then
If blnSortAscending Then
If StrComp(varArray(j), varArray(i), vbTextCompare) < 0 Then
varTemp = varArray(j)
varArray(j) = varArray(i)
varArray(i) = varTemp
OK = False
End If
Else
If StrComp(varArray(j), varArray(i), vbTextCompare) > 0 Then
varTemp = varArray(j)
varArray(j) = varArray(i)
varArray(i) = varTemp
OK = False
End If
End If
Else
If blnSortAscending Then
If varArray(j) < varArray(i) Then
varTemp = varArray(j)
varArray(j) = varArray(i)
varArray(i) = varTemp
OK = False
End If
Else
If varArray(j) > varArray(i) Then
varTemp = varArray(j)
varArray(j) = varArray(i)
varArray(i) = varTemp
OK = False
End If
End If
End If
Next i
Loop Until OK
Loop
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.