VBA Bubble Sort: How It Works and When to Use It in Excel

VBA & macros · Updated November 18, 2026

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.

  1. Start at the first item and compare it with the item immediately after it.
  2. Swap the pair if the first value belongs after the second.
  3. Continue to the end of the unsorted portion of the list.
  4. Repeat passes, shortening the unsorted portion each time.
  5. 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.

  1. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if you need to keep the VBA code.
  2. 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.
  3. In the editor, choose Insert > Module, then paste the code into the module.
  4. Return to the worksheet and select the numeric cells to sort. Include only the values you want reordered.
  5. 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.