JIYIK CN >

In VBA, or any programming language, the computer needs to store values ​​that are critical to the execution of the code. A great way to do this is with arrays.

Arrays are objects that have the ability to store information. They are essential in computer programming as they give programmers the option to save data that is required at a later stage of the code execution.

In VBA, we can use Array()and Arraylist()to meet our array needs. The former has a faster execution time, while the latter ensures flexibility. The former is fixed length, while the latter is variable length.

The following code blocks demonstrate how to create and use Array()and Arraylist().

Creating and using 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

StatArrayDemoOutput:

Katrina

Creating and using dynamic Array():

Sub DynaArrayDemo()

Dim namesArr as Variant

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

Debug.print namesArr(0)
End Sub

DynaArrayDemoOutput:

Glen

Create and useArraylist()

To use it in VBA Arraylist(), we need to enable Arraylist()the library where it is located.

To do this:

Now everything is ready.

In the following example, an object named is declared and initialized ArrayValues, Arraylistand then values ​​are 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

ArrayListDemoOutput:

Jose

Array()Sorting in VBA

The following code block will sort the values ​​entered in the subroutine either numerically or alphabetically.

ArraySortThe subroutine accepts three arguments. vArrayAmong them is the unsorted value, while inLowwill be vArraythe lower bound of , and inHiis vArraythe upper bound of . is tested ArraySortby two test subroutines NumberTestand 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

NumberTestOutput:

1 
3 
3 
4 
5 
5 
7 
8 

LetterTestOutput:

A
B
D
L
M
O
Q
T

Arraylist()Sorting in VBA

For Arraylist, it's easier because Arraylistthe object comes with Sorta sort method. So if you need to sort something in an array, it's better to use Arraylist.

The following code block will demonstrate how to Arraylistsort the values ​​in . The function Arraylistof Sortcan sort both 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

ArraylistSortLettersOutput:

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

ArraylistSortNumbersOutput:

1 
2 
4 
5 
7 
7 
8 
8 

ArraylistSortLettersThe two code blocks above ArraylistSortNumberssort the elements from low to high.

If we want to sort the array from high to low, we can sort the array from low to high and then use Reversethe method to switch.

The following code block will demonstrate the use of Reversemethod to sort from high to low.

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

SortInHighestToLowestOutput:

8 
8 
7 
7 
5 
4 
2 
1