Data Analysis in Excel - Use VBA to sort lists with Bubble Sort

A common task when transforming data is sorting lists of values to bring them in the correct order.

Excel has it own built in sorting functionalities but it is often the case that sorting is an intermediate step when running a code therefore it is preferred to have VBA take care of it

This post will explain in great detail the Bubble Sort algorithm

As a general principle sorting means ordering the elements of a list (they can be values or strings) in ascending or descending order

We can imagine a list as a series of Buckets placed next to each other; in each Bucket there is a value (it  could also be a sting)

The logic of Bubble Sorts can be summariszed as follows:

1) Perform a number of Steps equal to the total number of Buckets minus 1 (continue reading to understand why)

2) Within each Step a loop through a number of Buckets is performed. The number of Buckets being analysed decreases as the number of Steps performed increases. It will get clearer as you read!

3) When loping through the Buckets  If the value in the current Bucket is higher than the value contained in the next Bucket, values are swapped;  otherwise they remain in their current Buckets 

Lets look at a visual explanation to better understand the logic

Bubble Sort - Step 1

Bubble Sort - Step 2 and following 

Once the logic of Bubble sort becomes clear, few key considerations warrant some attention:

1) The number of  Steps performed by Bubble Sort are equal to the number of Buckets minus 1. When the algorithm is left with only one Bucket to analyse it stops as the value in the last Bucket is correctly placed

2) The number of Buckets to be analysed at STEP 1 is the total number of Buckets decreased by 1

3) The number of Buckets to be analysed decrease by 1 each time a new Step is initiated 

It is assumed that in the sheet called Bubble in column  A there is a list of numbers to be sorted

The first step is to define the variables and identify the range in which the numbers are contained 

Sub BubbleSort()

    Dim arr () As Variant ' arr will be used to host the list of data

    Dim i As Integer, j As Integer ' i and j will be used to count the Steps and Buckets

    Dim temp As Integer ' temp will be used tin case the values within the Buckets need to be swap

    Dim size As Integer' size will contain the number of the last used row of column A

    Sheets("Bubble").Select'the sheet containing the data is selected

    size = Range("A1").End(xlDown).Row' last used row of column A is identified and placed in the variable size

arr = Range("A1:A" & size).Value 'The range containing all the values is placed in the variable arr

The code starts performing the Steps and within each Step the loop across the Buckets

This is achieved through a double loop. 

The first loop keeps memory of the Steps and the second loop of the number of Buckets to be analysed at each step

'The i variable keeps track of the number of Steps

'As explained the number of Steps is equal to the total number of Buckets minus 1 

' Therefore the For i Loop ends at the Upper Bound of arr -1 

For i = LBound(arr) To UBound(arr) - 1  

'The j variable keeps track of the number of Buckets that need to be analysed

'As explained the number of Buckets to be considered decreases by 1 each time a new Step is initiated

'Therefore the For j Loop ends at the Upper Bound of arr - i (i records the number of Steps executed)

   For j = LBound(arr) To UBound(arr) - i

'When the value in the current Bucket is less than the one in the next bucket the values are swaped

'between the two buckets

 If arr(j, 1) > arr(j + 1, 1) Then

                ' Swap values

                temp = arr(j, 1)

                arr(j, 1) = arr(j + 1, 1)

                arr(j + 1, 1) = temp

Else:End If

'The loop is repeated through the different Buckets

Next j

'The next Step is initiated 

Next i

'The sorted values are copied in column B of the sheet Bubble  

Range("b1:b" & size).Value = arr

End Sub