Array List in VBA

We will introduce the lists in VBA and how to use them in our programs with examples.

Array List in VBA

There are many situations where we must store data in a data structure to display in messages or output them on an excel sheet. For this purpose, VBA provides ArrayList, a data structure we can use to store data.

As we all know, arrays have a fixed length, but when we use ArrayList to create a variety of values, it is not limited by fixed size.

To use ArrayList, we must set the reference because it is an external library or an object. There are two array types in VBA.

The first one is the static array which needs to know the lower and upper limit at the time declaration. On the other hand, the second one is a dynamic array that doesn’t require a lower or upper limit at the time of declaration.

Still, we need to provide the length after declaring the dynamic arrays using the redim statement in VBA. But when using ArrayList, we don’t have to declare the upper or lower limits.

Let’s first set the reference before using the ArrayList in our examples. To set the reference, we need to follow the following steps.

1. First, we have to go to the Developers tab and click on Visual Basic, as shown in the image below.

   

2. We will go to the Tools menu and click on Reference, as shown below in the image.

   

3. Now, we will look for the mscorlib.dll reference, select it, and click OK, as shown in the image below.

   

Once we have set the reference of the ArrayList, we will go through an example in which we will create an ArrayList of fruits. First, we will create our sub, as shown below.

# VBA
Sub testExample()

End Sub

Now, inside our function, we will define fruits as an ArrayList and set the variable to a new ArrayList, as shown below.

# VBA
Sub testExample()

  Dim fruits As ArrayList
  Set fruits = New ArrayList

End Sub

Now, we will add items to the ArrayList one by one by using the method Add and directly assign it a string of fruit names as we want.

# VBA
Sub testExample()

  Dim fruits As ArrayList
  Set fruits = New ArrayList

  fruits.Add "Mango"
  fruits.Add "Apple"
  fruits.Add "Banana"

End Sub

Now, if we want to call any of these fruits, we can quickly call them by using the reference order in which they are added. For example, if we want to get the first fruit from the ArrayList, we can quickly call it the same way we call any object from an array using the index of that array.

Let’s try to display fruits on a message box in the same order as shown below.

# VBA
Sub testExample()
  Dim fruits As ArrayList
  Set fruits = New ArrayList

  fruits.Add "Mango"
  fruits.Add "Apple"
  fruits.Add "Banana"
  MsgBox ("First Fruit is: " & fruits(0) & vbNewLine & "Second Fruit is: " & fruits(1) & vbNewLine & "Third Fruit is: " & fruits(2))

End Sub

Now, let’s run it and check how it works:

As we can see from the above example, it is very easy to use and output the values from the ArrayList, and we can easily store as many values into the ArrayList using a simple method of Add.