Sorting data using VBA: Bubble Sort

 2020-06-14    Sorting    3    173

BubbleSort: Sorting small one- or two-dimensional arrays (<= 1000 items):
The BubbleSort algorithm is suitable for sorting small arrays, usually with 1000 items or less.
BubbleSort is a stable sort method, something that might be important when you are sorting data with multiple columns.
When you use a stable sort method items with equal sort keys are returned in the same order in the sorted result as they had before being sorted.
BubbleSort can be painfully slow when sorting large arrays.
Use BubbleSort1 on one-dimensional arrays and BubbleSort on two-dimensional arrays.
BubbleSort can also be used to sort two-dimensional arrays that has a header row.

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

See this post if you need information about how to read from and write to worksheet cells using array variables (including code for the Array_ReplaceErrors macro).

Sub BubbleSort1(varArray As Variant, Optional blnCompareText As Boolean = False, Optional blnSortAscending As Boolean = True)
' updated 2012-09-02 by OPE
' sorts a one-dimensional array in ascending or descending order
' fast on small arrays, slow on very large arrays (n^2 comparisons)
' example: BubbleSort1 varArrayVariable
Dim n As Long, blnSwap As Boolean, i As Long, varTemp As Variant
    If Not IsArray(varArray) Then Exit Sub
    n = UBound(varArray, 1) - LBound(varArray, 1) + 1
    If n < 2 Then Exit Sub
    
    Do
        blnSwap = False
        For i = LBound(varArray, 1) + 1 To UBound(varArray, 1)
            If blnSortAscending Then
                If blnCompareText Then
                    blnSwap = StrComp(varArray(i - 1), varArray(i), vbTextCompare) > 0
                Else
                    blnSwap = varArray(i - 1) > varArray(i)
                End If
            Else
                If blnCompareText Then
                    blnSwap = StrComp(varArray(i - 1), varArray(i), vbTextCompare) < 0
                Else
                    blnSwap = varArray(i - 1) < varArray(i)
                End If
            End If
            If blnSwap Then
                varTemp = varArray(i - 1)
                varArray(i - 1) = varArray(i)
                varArray(i) = varTemp
            End If
            If blnSwap Then Exit For
        Next i
    Loop Until Not blnSwap
End Sub

Sub BubbleSort(varArray As Variant, Optional lngSortColumn As Long = -1, Optional blnCompareText As Boolean = False, _
    Optional blnHasHeaderRow As Boolean = False, Optional blnSortAscending As Boolean = True)
' updated 2012-09-02 by OPE
' sorts a two-dimensional array in ascending or descending order based on the content in column lngSortColumn
' lngSortColumn must be an integer number >= LBound(varArray, 2) and <= UBound(varArray, 2), uses LBound(varArray, 2) if lngSortColumn < 0
' fast on small arrays, slow on very large arrays (n^2 comparisons)
' stable sort method, items with equal sort keys are returned in the same order in the sorted result as they had before being sorted
' example: BubbleSort varArrayVariable, 1
Dim n As Long, blnSwap As Boolean, i As Long, j As Long, varTemp As Variant, varCaption As Variant
    If Not IsArray(varArray) Then Exit Sub
    n = UBound(varArray, 1) - LBound(varArray, 1) + 1
    If n < 2 Then Exit Sub
    
    If lngSortColumn < 0 Then lngSortColumn = LBound(varArray, 2) ' sort by the first column item
    If lngSortColumn < LBound(varArray, 2) Or lngSortColumn > UBound(varArray, 2) Then Exit Sub
    
    If blnHasHeaderRow Then
        i = LBound(varArray, 1)
        varCaption = varArray(i, lngSortColumn) ' save original column header
        If blnCompareText Then
            If blnSortAscending Then
                varArray(i, lngSortColumn) = Space(15) & varArray(1, lngSortColumn)
            Else
                varArray(i, lngSortColumn) = Replace(Space(15), " ", Chr(255)) & varArray(1, lngSortColumn)
            End If
        Else
            If blnSortAscending Then
                varArray(i, lngSortColumn) = -1E+16 ' use -1E+16 when sorting ascending
            Else
                varArray(i, lngSortColumn) = Replace(Space(15), " ", Chr(255)) & varArray(1, lngSortColumn)
            End If
        End If
    End If
    
    Do
        blnSwap = False
        For i = LBound(varArray, 1) + 1 To UBound(varArray, 1)
            If blnSortAscending Then
                If blnCompareText Then
                    blnSwap = StrComp(varArray(i - 1, lngSortColumn), varArray(i, lngSortColumn), vbTextCompare) > 0
                Else
                    blnSwap = varArray(i - 1, lngSortColumn) > varArray(i, lngSortColumn)
                End If
            Else
                If blnCompareText Then
                    blnSwap = StrComp(varArray(i - 1, lngSortColumn), varArray(i, lngSortColumn), vbTextCompare) < 0
                Else
                    blnSwap = varArray(i - 1, lngSortColumn) < varArray(i, lngSortColumn)
                End If
            End If
            If blnSwap Then
                For j = LBound(varArray, 2) To UBound(varArray, 2)
                    varTemp = varArray(i - 1, j)
                    varArray(i - 1, j) = varArray(i, j)
                    varArray(i, j) = varTemp
                Next j
            End If
            If blnSwap Then Exit For
        Next i
    Loop Until Not blnSwap
    If blnHasHeaderRow Then
        i = LBound(varArray, 1)
        varArray(i, lngSortColumn) = varCaption ' restore original column header
    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, 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.