Bubble Sort In VBA
Bubble sort is not the fastest sorting method but it is the simplest code and easiest to modify and scale. The scalable solution below can be expanded to any number of sort columns.
These examples assume that Option Base is set to the default 0. (First column of the array is column zero)
Reverse the greater than sign to sort a column in descending order.
Sort a 2 dimensional array on 1 column
This example sorts a two dimensional array named ArrayName on the first column (column 0). The sort is ascending. Reverse the > sign in the fourth row for a descending sort.
SortColumn1=0
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
If Condition1 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next
Sort a 2 dimensional array on 2 columns
This example sorts a two dimensional array named ArrayName on the first (column 0) and fourth (Column 3) columns.
SortColumm1=0
SortColumn2=3
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
Condition2=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)
If Condition1 or Condition2 then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next
Sort a 2 dimensional array on 3 columns
SortColumm1=0
SortColumn2=3
SortColumn3=5
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
Condition2=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)
Condition3=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) and _
ArrayName(j, SortColumn3) > ArrayName(j + 1, SortColumn3)
If Condition1 or Condition2 or Condition3 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next
Sort a 2 dimensional array on 4 columns
SortColumm1=0
SortColumn2=3
SortColumn3=5
SortColumn4=1
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
Condition2=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) > ArrayName(j + 1, SortColumn2)
Condition3=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) and _
ArrayName(j, SortColumn3) > ArrayName(j + 1, SortColumn3)
Condition4=ArrayName(j, SortColumn1) = ArrayName(j + 1, SortColumn1) and _
ArrayName(j, SortColumn2) = ArrayName(j + 1, SortColumn2) and _
ArrayName(j, SortColumn3) = ArrayName(j + 1, SortColumn3) and _
ArrayName(j, SortColumn4) < ArrayName(j + 1, SortColumn4)
If Condition1 or Condition2 or Condition3 or Condition4 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next