Sorting data using VBA: Shell Sort

 2020-06-14    Sorting    0   

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: 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.