VBA Sort Array

To sort an array in VBA, you need to write a code where you can match the first element of the array with the next one and inter-change them if the first one needs to come before. You need a FOR LOOP (For Next) for this and the UCASE function.

In this tutorial, we will see both ways (A-Z and Z-A) to sort elements of an array.

Sort an Array (A-Z)

In the below code, you have an array with five elements, and these elements have values starting from E to A, and now we need to sort in A-Z order.

Steps to Sort an Array

1. First, you need to write a nested loop with For Next using the upper and lower bounds of the array.
2. After that, within that loop, you need to use the VBA IF Statement to compare the two elements.
3. From here, if the first element needs to come before the next element you need to interchange them saving their values in variables.
4. In the end, use the “Next” keyword to close the loop.

Option Base 1
Sub vba_sort_array_a_to_z()

Dim myArray() As Variant
ReDim myArray(5)
Dim i As Integer
Dim j As Integer
Dim Temp As String

myArray(1) = "E"
myArray(2) = "D"
myArray(3) = "C"
myArray(4) = "B"
myArray(5) = "A"

'sorting array from A to Z
For i = LBound(myArray) To UBound(myArray)
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) > UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

Debug.Print myArray(1)
Debug.Print myArray(2)
Debug.Print myArray(3)
Debug.Print myArray(4)
Debug.Print myArray(5)

End Sub

Make sure to activate the Immediate Window to see the result there.

Sort an Array (Z-A)

In the same way, you can sort from Z-A. In the earlier method, we compared the first element with the next element in the sequence, but in this code, you need to do the opposite. So if the second (next in sequence) element is greater than the first one the code will interchange them.

Option Base 1
Sub vba_sort_array_z_to_a()

Dim myArray() As Variant
ReDim myArray(5)
Dim i As Integer
Dim j As Integer
Dim Temp As String

myArray(1) = "A"
myArray(2) = "B"
myArray(3) = "C"
myArray(4) = "D"
myArray(5) = "E"

'sorting array from A to Z
For i = LBound(myArray) To UBound(myArray)
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) < UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

Debug.Print myArray(1)
Debug.Print myArray(2)
Debug.Print myArray(3)
Debug.Print myArray(4)
Debug.Print myArray(5)

End Sub