A VBA bubble sort repeatedly compares neighboring values and swaps them when they are out of order, until the list is sorted. The macro below sorts a selected, contiguous, one-column range of numeric values in ascending order; it is useful for learning the algorithm, though Excel’s built-in Sort is usually better for real-world data.
What bubble sort means in VBA
Bubble sort is a simple sorting algorithm that moves through a sequence, comparing each pair of adjacent items. If a pair is reversed, the algorithm swaps the items. It then makes another pass, continuing until the sequence is in order. The name comes from the way larger values move toward the end of an ascending list during successive passes.
VBA does not provide a built-in BubbleSort function. To use the method, you write a procedure that stores values, compares them, swaps them, and writes the result back. The same logic can sort numbers in an array or values read from worksheet cells.
Bubble sort is especially useful as a teaching example because its steps are visible and its code is short. It is rarely the right choice for sorting a large worksheet, because the number of comparisons grows rapidly as the list gets longer.
How the algorithm works
Consider the list 5, 2, 4. On the first pass, bubble sort compares 5 and 2 and swaps them, producing 2, 5, 4. It then compares 5 and 4 and swaps those values, producing 2, 4, 5. A second pass confirms the order. A version with an early-exit check can stop after that pass because no swap was needed.
For a list of n items, the first pass may compare n−1 pairs, the next pass n−2 pairs, and so on. This gives bubble sort quadratic average and worst-case running time, commonly written O(n²). If the data is already sorted, a version that detects a pass with no swaps can finish in O(n) time. The basic algorithm needs little extra working space, but the worksheet example below loads the selected cells into a VBA array, which uses memory proportional to the number of cells.
- Start at the first item and compare it with the item immediately after it.
- Swap the pair if the first value belongs after the second.
- Continue to the end of the unsorted portion of the list.
- Repeat passes, shortening the unsorted portion each time.
- Stop early if a complete pass makes no swaps.
VBA code to sort a selected column
This macro sorts numeric values in ascending order. Select one contiguous range in a single worksheet column before running it. It rejects formulas and nonnumeric cells so it does not silently replace formulas or apply an unintended comparison to text. Excel dates read through Value2 are numeric serial values, so they sort chronologically when the cells contain dates.
Option Explicit
Sub BubbleSortSelectedNumbersAscending()
Dim rng As Range
Dim data As Variant
Dim i As Long
Dim j As Long
Dim temp As Variant
Dim swapped As Boolean
' The selection must be a worksheet range.
If TypeName(Selection) <> "Range" Then
MsgBox "Select a one-column range of numbers first."
Exit Sub
End If
Set rng = Selection
' Keep the input shape and contents predictable.
If rng.Areas.Count <> 1 Or rng.Columns.Count <> 1 Then
MsgBox "Select one contiguous range in a single column."
Exit Sub
End If
If rng.Rows.Count < 2 Then
MsgBox "Select at least two cells."
Exit Sub
End If
data = rng.Value2
' This example accepts numeric constants, not formulas or text.
For i = 1 To UBound(data, 1)
If rng.Cells(i, 1).HasFormula Then
MsgBox "The selected range must not contain formulas."
Exit Sub
End If
If Not IsNumeric(data(i, 1)) Or VarType(data(i, 1)) = vbString Then
MsgBox "Every selected cell must contain a number."
Exit Sub
End If
Next i
' Each pass leaves the largest remaining value at the end.
For i = 1 To UBound(data, 1) - 1
swapped = False
For j = 1 To UBound(data, 1) - i
If CDbl(data(j, 1)) > CDbl(data(j + 1, 1)) Then
temp = data(j, 1)
data(j, 1) = data(j + 1, 1)
data(j + 1, 1) = temp
swapped = True
End If
Next j
If Not swapped Then Exit For
Next i
' Write the sorted values back to the selected cells.
rng.Value2 = data
End Sub
The loop compares values as numbers using CDbl, rather than comparing their displayed formatting. A cell formatted to show two decimal places is still sorted by its underlying value. The macro leaves the cells’ existing formatting in place, but assigning the array back to the range writes values, not formulas.
How to run the macro safely
Use the desktop version of Excel for Microsoft 365 on Windows. Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z. The macro writes new values into the selected cells, so a backup is the simplest way to preserve the original order.
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if you need to keep the VBA code.
- Open the VBA editor with Alt+F11, or choose Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
- In the editor, choose Insert > Module, then paste the code into the module.
- Return to the worksheet and select the numeric cells to sort. Include only the values you want reordered.
- Run the procedure from the VBA editor, or use Alt+F8 in Excel, select BubbleSortSelectedNumbersAscending, and choose Run.
On Mac, Excel also has a Visual Basic Editor and supports VBA macros in desktop workbooks. Keyboard shortcuts can differ by keyboard and function-key settings; use the Developer tab’s Visual Basic command if the Windows shortcut does not open the editor. Excel for the web cannot run VBA macros, so open the workbook in desktop Excel to use this code. Only enable macros in workbooks you trust.
Changing the sort order or data type
To sort in descending order, reverse the comparison in the inner loop from greater-than to less-than. With that change, smaller values move toward the end on each pass. The rest of the loop can stay the same.
To sort text, remove the numeric validation and compare the values as strings instead. Text ordering can depend on comparison settings and the characters involved, so decide whether the intended order is case-sensitive and how blanks should behave before adapting the procedure. A single comparison rule is not automatically suitable for numbers, text, blanks, errors, and dates mixed in one range.
If you are sorting a whole table by one column, do not select just that column in the macro above. Sorting one column alone can separate the values from the other fields in their rows. Use Excel’s table or range sort so complete records move together, or write a different macro that reads and sorts the full rows.
Stability and duplicate values
A sorting algorithm is stable if equal keys keep their original relative order. This bubble sort is stable because it swaps only when the left value is strictly greater than the right value. Equal values are not swapped past one another. Stability matters when the original order among ties contains useful information, such as a prior sort by another field.
Stability only helps when the data and comparison rule are well-defined. If you need to sort records by department and then by surname, sorting the entire range using appropriate keys is clearer than relying on a sequence of isolated column sorts. Excel’s built-in Sort dialog lets you add multiple levels, which is generally a more practical way to express that requirement.
Common problems and limitations
- Nothing sorts: Check that the selected range has at least two rows, is contiguous, and occupies only one column. The example intentionally displays a message and exits when those conditions are not met.
- It rejects cells that look numeric: Values stored as text are rejected, even when they display as numbers. Convert them to actual numbers first if numeric ordering is intended.
- Formulas disappear: The macro refuses ranges containing formulas. If you remove that check, writing the array back with Value2 will replace formula expressions with their calculated values.
- Other columns no longer match: The code sorts only the selected cells. Do not use it on one field of a multi-column record; sort the full rows instead.
- It is slow on a long list: Quadratic growth makes bubble sort a poor fit for large data. Choose a built-in sort or a more efficient algorithm for substantial lists.
Bubble sort versus Excel’s built-in Sort
For normal worksheet work, Excel’s built-in sorting is usually the better option. Select a cell in the data, then use Data > Sort A to Z for ascending order or Data > Sort Z to A for descending order. For more control, use Data > Sort to choose a column, order, and additional sort levels. When sorting a range rather than an Excel table, check that Excel has identified the full data region and whether the first row contains headers.
Excel’s sort works with worksheet ranges and supports multi-column sorting, whereas the example macro is deliberately limited to numeric values in one selected column. The built-in command is more suitable for larger lists and entire records. Use VBA bubble sort when you specifically need to learn or demonstrate the algorithm, or when a small, controlled sequence must be sorted as part of a larger VBA exercise.
Frequently Asked Questions
Does VBA have a built-in bubble sort?
No. VBA does not include a built-in BubbleSort function. You write the comparison-and-swap loops yourself, or use a worksheet sorting feature such as Data > Sort.
Can this macro sort text alphabetically?
Not as written. It validates numeric values and compares them as numbers. A text version needs string comparisons and a deliberate rule for case, blanks, and other values.
Can I sort an entire table with this code?
No. This procedure accepts one column and writes values only to that selection. For a table, use Excel’s Sort command or adapt the algorithm to move complete rows together.
Will the macro preserve formulas and undo with Ctrl+Z?
The supplied code refuses a selection containing formulas, and VBA actions generally cannot be undone with Ctrl+Z. Save a copy before running a macro, especially one that writes values back to cells.
Is bubble sort good for large Excel lists?
Usually not. Its average and worst-case work grows approximately with the square of the item count. Excel’s built-in sorting or a more efficient algorithm is a better choice for larger lists.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Change Pivot Table Source: Code and TipsTo change a PivotTable’s source with VBA, create a PivotCache for the new worksheet range or Excel table,…
- Excel VBA: Loop Through All Open Workbooks in a MacroTo loop through every workbook currently open in desktop Excel, use a For Each loop over Application.Workbooks. Declare…
- VBA Copy Paste Formats: Methods and ExamplesIn Excel VBA, copy formatting without changing cell contents by copying a source range and pasting with Paste:=xlPasteFormats.…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a specified range and return the first matching cell;…
- VBA Range Column: How to Reference ColumnsIn VBA, reference a whole worksheet column with Columns("C") or Range("C:C"); to get the column containing a particular…
- VBA Macros in Google Sheets: What Works InsteadNo. Google Sheets cannot run VBA macros: VBA is Microsoft Excel’s programming language, and Sheets uses Google Apps…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.