VBA Sort Array

Sort an Array (A-Z)

In the below code, you have an array with five elements, and these elements have values from E to A, and now we need to sort them 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 after 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.

One more thing: “Option Base 1” makes the array start at 1 instead of 0. It must sit at the very top of the module, before any Sub, and you can use it only once in a module. So if you put both codes from this tutorial in the same module, keep only one “Option Base 1” line at the top.

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 Z to A
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

Sort an Array of Numbers

The codes above are made for text. If your array has numbers, don’t use them as they are. UCASE turns each number into text, and text is compared character by character, so 120 comes before 45 (because “1” comes before “4”). And the “Temp” variable is declared as a String, so every number it moves gets stored back as text.

For numbers, compare the values directly and declare “Temp” as a Variant:

Sub vba_sort_array_numbers()

Dim myArray As Variant
Dim i As Long
Dim j As Long
Dim Temp As Variant

myArray = Array(45, 7, 120, 3, 18)

'sorting numbers from smallest to largest
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

For i = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(i)
Next i

End Sub

To sort from largest to smallest, change “>” to “<” in the IF line. Here I have also used LBound and UBound to print the result, so the code works whether the array starts at 0 or 1.

Case-Sensitive and Case-Insensitive Sorting

By default, VBA compares text in binary mode, which means all the uppercase letters come before the lowercase ones. Without UCASE, “Zebra” would come before “apple” in an A-Z sort. That’s why the codes above use UCASE on both elements before comparing them.

There are two ways to choose how the comparison works:

• Case-insensitive: keep UCASE, or add “Option Compare Text” at the top of the module (before any Sub). With it, VBA treats “A” and “a” as equal, and you can drop UCASE from the code.
• Case-sensitive: remove UCASE from the IF line and don’t use “Option Compare Text”. Uppercase values then come before lowercase ones.

Sort a Large Array with QuickSort

The nested loop compares every element with every other element, which is fine for a few hundred values but gets slow with thousands. For a large array, you can use QuickSort. It splits the array around a middle value (pivot) and sorts each part again, so it needs far fewer comparisons.

First, add this procedure to a module:

Sub QuickSort(arr As Variant, ByVal first As Long, ByVal last As Long)

Dim pivot As Variant
Dim Temp As Variant
Dim low As Long
Dim high As Long

low = first
high = last
pivot = arr((first + last) \ 2)

Do While low <= high
    Do While arr(low) < pivot
        low = low + 1
    Loop
    Do While arr(high) > pivot
        high = high - 1
    Loop
    If low <= high Then
        Temp = arr(low)
        arr(low) = arr(high)
        arr(high) = Temp
        low = low + 1
        high = high - 1
    End If
Loop

If first < high Then QuickSort arr, first, high
If low < last Then QuickSort arr, low, last

End Sub

After that, call it with the array and its lower and upper bounds:

Sub vba_sort_array_quicksort()

Dim myArray As Variant
Dim i As Long

myArray = Array(45, 7, 120, 3, 18)

QuickSort myArray, LBound(myArray), UBound(myArray)

For i = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(i)
Next i

End Sub

QuickSort sorts the array in place, from A to Z (or smallest to largest). It compares text in a case-sensitive way unless the module has “Option Compare Text” at the top.

Sort an Array with the SORT Function (Microsoft 365)

If you use Excel for Microsoft 365, Excel 2024 or Excel 2021, you can let Excel’s SORT function do the work with WorksheetFunction.Sort. A one-dimensional array is treated as a single row, so set the last argument (by_col) to True to sort across that row.

Sub vba_sort_array_sort_function()

Dim myArray As Variant
Dim result As Variant
Dim v As Variant

myArray = Array("E", "D", "C", "B", "A")

'1 = A to Z, -1 = Z to A
result = WorksheetFunction.Sort(myArray, 1, 1, True)

For Each v In result
    Debug.Print v
Next v

End Sub

Change the third argument to -1 to sort from Z to A. In older versions of Excel, this line gives an error because the SORT function isn’t there, so use one of the loop methods above.

Sort an Array with ArrayList

On Windows, you can also use the ArrayList object from the .NET Framework, which has its own Sort and Reverse methods:

Sub vba_sort_array_arraylist()

Dim myList As Object
Dim v As Variant

Set myList = CreateObject("System.Collections.ArrayList")

myList.Add "E"
myList.Add "D"
myList.Add "C"
myList.Add "B"
myList.Add "A"

myList.Sort
'use myList.Reverse after Sort for Z to A

For Each v In myList
    Debug.Print v
Next v

End Sub

This needs .NET Framework 3.5 installed on the computer. Without it, the CreateObject line gives an automation error. It doesn’t work on a Mac either, so don’t use it in a file that other people will run.

Which Method to Use

• Nested loop: works in every version of Excel, on Windows and Mac. Best for small arrays.
• QuickSort: works everywhere too, and is much faster for large arrays.
• WorksheetFunction.Sort: the shortest code, but only in Excel for Microsoft 365, Excel 2024 and Excel 2021.
• ArrayList: short code, but Windows only and needs .NET Framework 3.5.

Frequently Asked Questions

Sub vba_sort_range_array()

Dim myArray As Variant
Dim i As Long
Dim j As Long
Dim Temp As Variant

myArray = Range("A1:A10").Value

For i = LBound(myArray, 1) To UBound(myArray, 1) - 1
    For j = i + 1 To UBound(myArray, 1)
        If myArray(i, 1) > myArray(j, 1) Then
            Temp = myArray(j, 1)
            myArray(j, 1) = myArray(i, 1)
            myArray(i, 1) = Temp
        End If
    Next j
Next i

Range("B1:B10").Value = myArray

End Sub