How to Sort the Elements of an Array and Arraylist in VBA

1. Sort Array() in VBA
2. Sort Arraylist() in VBA

In VBA or in any programming language, the need of the computer to store values vital with the code execution. A great way to do this is to utilize arrays.

Arrays are objects that have the ability to store information. They are essential in computer programming as they give the programmer the option to save data that was needed on the latter part of the code execution.

In VBA, we can utilize Array() and Arraylist() for our array needs. The former is faster in execution time and the latter promises flexibility. The former is fixed length while the latter length is variable.

The code block below will demonstrate how to create and how to use an Array() and Arraylist().

Create and use static Array():

Sub StatArrayDemo()

Dim namesArr (1 to 4) as String

namesArr(1) = "Glen"
namesArr(2) = "Jose"
namesArr(3) = "Katrina"
namesArr(4) = "Myla"

Debug.print namesArr(3)
End Sub

StatArrayDemo Output:

Katrina

Create and use Dynamic Array():

Sub DynaArrayDemo()

Dim namesArr as Variant

namesArr = Array("Glen", "Jose", "Katrina", "Myla")

Debug.print namesArr(0)
End Sub

DynaArrayDemo Output:

Glen

Create and use Arraylist()

To use Arraylist() in VBA, we need to enable the library by which Arraylist() are into.

To do this:

You are now all set.

On the example below, Arraylist object named ArrayValues was declared and was initialized then values were added.

Sub ArrayListDemo()

Dim ArrayValues As ArrayList
'Create a new Arraylist Object

'Adding Values to ArrayValues Arraylist
ArrayValues. Add("Glen")
ArrayValues. Add("Jose")
ArrayValues. Add("Kartina")
ArrayValues. Add("Myla")

Debug.Print (ArrayValues(1))

End Sub

ArrayListDemo Ouput:

Jose

Sort Array() in VBA

The code block below will sort the values inputted on the subroutine whether in numerical order or in alphabetical order.

The ArraySort subroutine accepts three parameters. vArray where the unsorted values while inLow will be the lowerbound of vArray and inHi is the upperbound of vArray. The ArraySort was tested by two test subroutines NumberTest and LetterTest.

Public Sub ArraySort(vArray As Variant, inLow As Long, inHi As Long)

Dim arr1   As Variant
Dim tempO As Variant
Dim tempL  As Long
Dim tempH   As Long

tempL = inLow
tempH = inHi

arr1 = vArray((inLow + inHi) \ 2)

While (tempL <= tempH)
    While (vArray(tempL) < arr1 And tempL < inHi)
        tempL = tempL + 1
    Wend

    While  (arr1 < vArray(tempH) And tempH > inLow)
        tempH = tempH - 1
    Wend

    If (tempL <= tempH) Then
        tempO = vArray(tempL)
        vArray(tempL) = vArray(tempH)
        vArray(tempH) = tempO
        tempL = tempL + 1
        tempH = tempH - 1
    End If
Wend

If (inLow < tempH) Then ArraySort vArray, inLow, tempH
If (tempL < inHi) Then ArraySort vArray, tempL, inHi

End Sub

Sub NumberTest()

Dim myArr As Variant

myArr = Array(5, 7, 3, 8, 5, 3, 4, 1)

Call ArraySort(myArr, 0, UBound(myArr))

Dim i As Integer

For i = LBound(myArr) To UBound(myArr)

Debug.Print (myArr(i))
Next i

End Sub

Sub LetterTest()

Dim myArr As Variant


myArr = Array("A", "T", "O", "D", "B", "Q", "M", "L")
Call ArraySort(myArr, 0, UBound(myArr))

Dim i As Integer

For i = LBound(myArr) To UBound(myArr)

Debug.Print (myArr(i))
Next i

End Sub

NumberTest Output:

1 
3 
3 
4 
5 
5 
7 
8 

LetterTest Output:

A
B
D
L
M
O
Q
T

Sort Arraylist() in VBA

For Arraylist, it is easier since the Arraylist object comes with the Sort method. Thus, if you need to sort things in array, it is much better to use Arraylist.

The code block below will demonstrate how to sort values in Arraylist. The Sort Function of Arraylist can sort values and letters.

Public Sub ArraylistSortLetters()

Dim myArr As Arraylist
Set myArr = New Arraylist

myArr.Add ("A")
myArr.Add ("T")
myArr.Add ("O")
myArr.Add ("D")
myArr.Add ("B")
myArr.Add ("Q")
myArr.Add ("M")
myArr.Add ("L")

myArr.Sort

Dim i As Integer

For i = 0 To myArr.Count - 1
   Debug.Print (myArr(i))
Next i

End Sub

ArraylistSortLetters Output:

A
B
D
L
M
O
Q
T

Public Sub ArraylistSortNumbers()

Dim myArr As Arraylist
Set myArr = New Arraylist

myArr.Add (5)
myArr.Add (8)
myArr.Add (2)
myArr.Add (8)
myArr.Add (4)
myArr.Add (7)
myArr.Add (1)
myArr.Add (7)

myArr.Sort

Dim i As Integer

For i = 0 To myArr.Count - 1
    Debug.Print (myArr(i))
Next i

End Sub

ArraylistSortNumbers Output:

1 
2 
4 
5 
7 
7 
8 
8 

The two code blocks above ArraylistSortLetters and ArraylistSortNumbers sorts the elements from lowest to highest.

If we want to sort the array from highest to lowest, we can sort the array from lowest to highest then use the Reverse method to switch.

The code block below will demonstrate sorting highest to lowest using the Reverse method.

Public Sub SortInHighestToLowest()

Dim myArr As Arraylist
Set myArr = New Arraylist

myArr.Add (5)
myArr.Add (8)
myArr.Add (2)
myArr.Add (8)
myArr.Add (4)
myArr.Add (7)
myArr.Add (1)
myArr.Add (7)

myArr.Sort
myArr.Reverse

Dim i As Integer

For i = 0 To myArr.Count - 1
   Debug.Print (myArr(i))
Next i

End Sub

SortInHighestToLowest Output:

8 
8 
7 
7 
5 
4 
2 
1