Introduction | Example | Tutorial | Applications

Introduction - VBA Bubble Sort

There are a number of ways to sort a VBA array. This tutorial presents a VBA bubble sort macro to show you how to sort small one-dimensional VBA arrays. The VBA bubble sort algorithm is not suitable for large arrays, say greater than a couple thousand entries, because it’s rather slow. If you’re looking for a super fast sorting algorithm for large arrays, check out the VBA quicksort macro.

I’ll explain the pros and cons of the bubble sort algorithm throughout this tutorial.

Example - VBA Bubble Sort

The Bubble Sort Macro

Sub BubbleSort(MyArray() As Variant)
'Sorts a one-dimensional VBA array from smallest to largest
'using the bubble sort algorithm.
Dim i As Long, j As Long
Dim Temp As Variant
 
For i = LBound(MyArray) To UBound(MyArray) - 1
    For j = i + 1 To UBound(MyArray)
        If MyArray(i) > MyArray(j) Then
            Temp = MyArray(j)
            MyArray(j) = MyArray(i)
            MyArray(i) = Temp
        End If
    Next j
Next i
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Tutorial - VBA Bubble Sort

About Bubble Sort

The bubble sort algorithm steps through each element in an array and compares it to the adjacent element. If the next element in the array is larger than the current element in the array, it swaps the two entries. It keeps doing this until no swaps remain and the array is completely sorted from smallest to largest.

Sorting arrays with the Bubble Sort algorithm is helpful, but how do you handle your array after it’s sorted? To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

Since bubble sort compares adjacent entries over and over, it is not efficient for large arrays. To loop through your array, bubble sort makes N*(N-1)/2 comparisons until your array is fully sorted, where N is the number of elements in your array. What does that mean in practice? That means that if you have an array with 1,000 elements, it can take almost half a million iterations to completely sort your array! The number of iterations is proportional to the square of the number of elements in your array.

Why use the VBA Bubble Sort algorithm

It should come as no surprise that VBA is not the fastest programming language out there. That makes the use of bubble sort even less attractive for large arrays. So why do people use the bubble sort algorithm? Because it’s easy to implement! Coding the logic for bubble sort in VBA is pretty simple. You can look at the example and intuitively understand how it works.

If you’re working with arrays that are smaller than a couple thousand entries, a good ole fashion VBA bubble sort algorithm will do the job just fine. It will sort your array from smallest to largest reasonably quickly so you can continue on with your data analysis. To implement the sorting algorithm into your projects even quicker, download wellsrPRO and select the bubble sort macro directly from your Excel ribbon whenever you need it.

How to Use the VBA Bubble Sort Macro

Let’s say you have a column of data in Column A of your spreadsheet. You store each element in a one-dimensional VBA array and you want to sort the array from smallest to largest. You may find yourself needing to sort your data like this if you want to perform some statistical analysis, for example, on the bottom 10% or middle 20% of data. Here’s how you would call the BubbleSort example macro:

Sub ProcessData()
'Example macro to show you how to add a column of data to an array
'and sort the data from smallest to largest using VBA Bubble Sort.
Dim MyData() As Variant
Dim i As Long, LastRow As Long
 
'Store column of data into array
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ReDim MyData(1 To LastRow)
For i = 1 To LastRow
    MyData(i) = Range("A" & i)
Next i
 
'Now sort your array using the VBA Bubble Sort macro
Call BubbleSort(MyData())
'
'From here on, your "MyData" array is sorted from smallest to largest
'
End Sub

The Call BubbleSort line calls the VBA BubbleSort macro presented at the top of this tutorial and sorts your array from smallest to largest. After this, you can do whatever you want with your sorted VBA array! For more information on how this demo macro works, check out these related VBA tutorials:

Application Ideas

I’ve incorporated the bubble sort algorithm into a number of my financial forecast spreadsheets. In these spreadsheets, I run Monte Carlo simulations on account balances over time and I analyze the probability of certain outcomes. To do this, I store the account balances of each iteration for each year into arrays using VBA and I sort them with the bubble sort sorting algorithm. Once sorted, I’m able to group the account balance outcomes into histograms or return average balances for a subset of outcomes.

That’s just one example of where a good sorting algorithm, like bubble sort, can come in handy. How do you plan on using this VBA sorting macro? Just remember, bubble sort is an easy algorithm to implement and understand, but it’s not going to be the fastest sorting algorithm you’ll find. For a speed test comparison between bubble sort and a super fast sorting algorithm, check out my VBA quicksort tutorial.

Before you go, please take a moment to share this article with your friends on Facebook and Twitter. Social media is a great way to grow the site and reach more users, so I genuinely appreciate it when you share my articles with friends on your favorite platform! :)

As always, when you’re ready to take your VBA to the next level, subscribe using the form below.