Excel VBA Bubble Sort for Arrays: Code and Explanation

VBA & macros · Updated November 21, 2026

To bubble-sort a one-dimensional VBA array, compare neighboring items and swap them when they are out of order; repeat until a full pass makes no swaps. The example below sorts an initialized array in place, in either ascending or descending order, and includes an early-exit check for arrays that are already sorted.

What bubble sort does

Bubble sort moves values toward their correct positions through repeated, adjacent comparisons. On an ascending pass, if the item on the left is greater than the item on the right, the procedure swaps them. After the first full pass, the largest value has reached the last position. The next pass can stop one position earlier, because that final value is already in order.

For example, starting with 8, 3, 6, 1, 5, the first comparison swaps 8 and 3. The next comparison swaps 8 and 6, then 8 and 1, and then 8 and 5. At the end of that pass, 8 is at the end. Further passes move the remaining values into place, giving 1, 3, 5, 6, 8.

The method is easy to follow because it changes only adjacent items and requires no second array. It is often used to teach sorting, or for a very small array when simple, self-contained code matters more than speed. It is usually not the right choice for large arrays.

Ready-to-run VBA code

In the Visual Basic Editor, place this code in a standard module. The demo uses the built-in Array function, which creates a zero-based array, and prints the sorted values to the Immediate window. The sorting procedure itself also works with other initialized, one-dimensional Variant arrays.

Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Public Sub DemoBubbleSort()
    Dim items As Variant
    Dim i As Long

    items = Array(8, 3, 6, 1, 5)
    BubbleSort items, True

    For i = LBound(items) To UBound(items)
        Debug.Print items(i)
    Next i
End Sub

Public Sub BubbleSort(ByRef values As Variant, _
                      Optional ByVal ascending As Boolean = True)
    Dim firstBound As Long
    Dim lastBound As Long
    Dim pass As Long
    Dim j As Long
    Dim temp As Variant
    Dim shouldSwap As Boolean
    Dim didSwap As Boolean

    ' This routine expects an initialized, one-dimensional array.
    firstBound = LBound(values)
    lastBound = UBound(values)
    If lastBound <= firstBound Then Exit Sub

    For pass = firstBound To lastBound - 1
        didSwap = False

        For j = firstBound To lastBound - (pass - firstBound) - 1
            If ascending Then
                shouldSwap = (values(j) > values(j + 1))
            Else
                shouldSwap = (values(j) < values(j + 1))
            End If

            If shouldSwap Then
                temp = values(j)
                values(j) = values(j + 1)
                values(j + 1) = temp
                didSwap = True
            End If
        Next j

        ' Stop early when this pass made no changes.
        If Not didSwap Then Exit For
    Next pass
End Sub

For the demo input, the Immediate window displays 1, 3, 5, 6, and 8, each on its own line. The optional second argument is True for ascending order and False for descending order. If you omit it, the optional argument defaults to ascending.

How to run the macro in Excel

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste in the code, and return to Excel. Run DemoBubbleSort from the editor with the cursor inside that procedure and press F5, or use Developer > Macros, select the macro, and choose Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

To see the result, open the Immediate window in the editor with Ctrl+G. The demo prints there; it does not write values into worksheet cells. To sort a different sample, change the values in the Array assignment and run the demo again. To reuse the sort from another macro, create an initialized one-dimensional array and call BubbleSort yourArray or BubbleSort yourArray, False.

On Excel for Mac, VBA is available in the desktop application, but the exact keyboard shortcut for the editor can vary by keyboard and function-key settings. Open the Visual Basic Editor from the Developer tab or Excel’s macro tools if the shortcut does not work. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use desktop Excel for these steps.

Array bounds and assumptions

The procedure gets the first and last valid indexes with LBound and UBound, rather than assuming that the array begins at zero or one. This matters because arrays declared with a custom lower bound can start at another index, while Array(…) normally creates a zero-based array. The inner loop uses those bounds to compare only valid neighboring elements and to avoid revisiting the already settled end of the array.

The example expects an initialized, one-dimensional array containing at least one item. Calling LBound or UBound on an uninitialized dynamic array can raise an error, so this procedure is not an empty-array checker. If an array might be uninitialized, check that condition in the calling code or add error handling designed for the way that array is created. A two-dimensional array also needs a different approach: this routine does not decide which column or row should determine the order.

The parameter is a Variant passed by reference, so the routine rearranges the supplied array rather than returning a separately sorted copy. A Variant can contain an array, including a Variant array created with Array. If your data is in a typed array such as a Long array, adapt the parameter and temporary variable to the intended type, or copy the values to a Variant array before calling this version. Do not pass a scalar value where an array is expected.

Values VBA can compare reliably

The code uses VBA’s greater-than and less-than operators. It is best suited to arrays whose elements have a consistent, comparable type: all numbers, all dates, or all strings. A mixed array of numbers, text, empty values, error values, or Null can produce coercion surprises or a run-time error. In particular, worksheet error values are not ordinary numbers or text. Clean or validate the data before sorting if it may contain such values.

Numeric-looking text is still text unless it has been converted to a number. For instance, text values such as 2, 10, and 100 may sort alphabetically rather than by numeric magnitude. Convert them to numeric values first if numerical order is required. Similarly, dates should be actual VBA Date values or Excel date serial numbers, not inconsistent date strings whose interpretation may depend on regional settings.

String comparisons follow the module’s comparison setting. With the default Option Compare Binary, VBA compares strings using binary ordering; Option Compare Text makes text comparisons case-insensitive according to the system’s text-comparison rules. If the relative ordering of uppercase and lowercase letters matters, choose the comparison setting deliberately and keep it consistent with the desired result. This simple routine does not provide custom rules for accents, punctuation, or locale-specific business sorting.

Descending order, stability, and duplicates

The Boolean argument changes which adjacent pair is considered out of order. Ascending order swaps when the left value is greater; descending order swaps when the left value is smaller. Equal items are not swapped because neither strict comparison is true. For consistent values, that also means duplicates remain in their original relative order: bubble sort is stable in this implementation.

Stability can matter when each item represents a record and equal keys should retain a previous ordering. However, this code sorts the values in the array itself; it does not automatically keep separate arrays of names, dates, or other fields aligned. If records have multiple fields, represent each record together or sort the worksheet range by its key, rather than sorting one key array alone and leaving related data behind.

Speed and when to use another method

With n elements, ordinary bubble sort can make roughly n times n comparisons in the worst case, so its time complexity is O(n²). The early-exit flag improves the best case: an already sorted array needs one pass and then stops. It does not make a badly ordered large array efficient. The routine uses only a temporary Variant in addition to the input array, so its extra storage is constant, but the repeated comparisons and swaps still dominate for larger inputs.

For a handful of values, the code may be convenient and clear. For hundreds or thousands of values, consider a more efficient algorithm such as quicksort or merge sort, or use Excel’s built-in sorting facilities when the data belongs on a worksheet. Excel’s Sort tools can sort ranges by one or more columns and preserve entire rows, which is often safer than extracting a single column into an array and sorting it separately. A VBA array sort is more appropriate when values are already in memory and the procedure needs to order them before later calculations.

There is no universal item-count cutoff at which bubble sort becomes unacceptable; the cost depends on the data type, comparison work, and how often the macro runs. The practical point is its quadratic growth. Doubling the number of items can make the comparison workload about four times as large in a worst-case pattern. When the list grows or the code becomes part of a recurring process, replace the algorithm rather than relying on the early-exit check to compensate.

Common problems and practical fixes

  • Type mismatch: Inspect the array for mixed types, worksheet error values, or Null. Normalize values to a consistent type before sorting.
  • Subscript out of range: Check that the array is initialized and one-dimensional, and use its actual bounds. Do not assume every array starts at zero.
  • Nothing appears in the sheet: The demo writes to the Immediate window, not to cells. Add a separate loop to copy sorted values to a range if that is the intended output.
  • Related columns no longer match: Do not sort only one field when each value belongs to a row of data. Sort complete records or use the worksheet range sort.
  • Macro does not run: Use desktop Excel and check the workbook’s macro security settings. Only enable macros in files you trust; a macro can change workbook content and perform other actions.

When you do write results to cells, decide whether the array’s lower bound should map to the first worksheet row or whether it should be normalized to a zero- or one-based index first. Keep that mapping explicit to avoid off-by-one errors. Also remember that writing a sorted array back to a range overwrites the destination cells, which is another reason to keep a backup or write to a separate output area.

Frequently Asked Questions

How do I sort an array in descending order in VBA?
Pass False as the second argument, as in BubbleSort items, False. The procedure then swaps a pair when the left item is smaller than the right item, producing descending order.

Can this bubble sort handle a two-dimensional array?
Not as written. It compares adjacent elements along the default single dimension and assumes each index identifies one value. For row-based data, define a key column and swap every field in the corresponding rows together, or use Excel’s range sorting tools.

Does the procedure sort the original array?
Yes. It receives the Variant array by reference and swaps its elements in place. If you need to preserve the original order, copy the array first and sort the copy.

Can I use this code to sort text?
Yes, if the array contains comparable strings. The module’s Option Compare setting affects case-sensitive versus text-style comparisons. Mixed text and numbers, or values that only look numeric, may not produce the order you expect.

Why does bubble sort run slowly on a large array?
Its worst-case comparison count grows quadratically, O(n²). The early-exit check helps when data is already sorted or nearly sorted, but it does not change the worst-case growth. Choose a more efficient sorting method for substantial arrays or frequent runs.

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.