VBA — Bubble Sort

A bubble sort is a technique to order items in an array. They can be set in either ascending or descending order. It is possible to output items in an array to Excel and then call the Sort method on that range. However, this is not best practice.

We have the following array:

Dim sortArray(9) as Integer
sortArray(0) = 100
sortArray(1) = 20
sortArray(2) = 30
sortArray(3) = 70
sortArray(4) = 10
sortArray(5) = 40
sortArray(6) = 90
sortArray(7) = 80
sortArray(8) = 60
sortArray(9) = 50
For i = 0 to Ubound(sortArray)
   For x = Ubound(sortArray) to i + 1 Step - 1
      if sortArray(x) < sortArray(i) Then
         holdInt = sortArray(x)
         sortArray(x) = sortArray(i)
         sortArray(i) = holdInt    
      end if 
   Next x
Next i

The code should sort the array in ascending order. The outer loop runs through every item in the array from index 0 to the upper bound 9. The inner loop runs from the upper bound 9 to i + 1. Step-1 indicates that the iteration will be negative. So step-1 would go from 9 to 1.

For example:

// The first loop iteration
i = 0 in the first loop
sortArray(i) = 100
x = the int in inner loop which runs from 9 to 1
100 is checked against each item in those indices
If the number at index x is less than the number at index i
Then the item at x is switched with the item at i
The holdInt variable is used to store the value at x
Once we set the item at x equal to the item at i
The value at x is gone, we use holdInt to store that value
Now we set the item at i to the holdInt item
This process continues for each index in the sortArray
Once the first loop is complete the item at index 0 will be 10
The next loop i will be 1
The inner loop will run from 9 to 2
There is no need to run from 9 to 1 or 9 to 0
The item at 0 is already the lowest value
The item at 1 is the current index being evaluated
There is no need to check it against itself