### Sorting data using VBA: Bubble Sort

###### 2020-06-14    Sorting    3    125

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

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