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's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
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
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:
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.