Sorting data using VBA: Bubble Sort
2020-06-14 Sorting 3 268
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:
- 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.