VBA ArrayList

VBA ArrayList is a kind of data structure we use in VBA to store the data. For example, ArrayList in Excel VBA is a class that creates an array of values. However, unlike traditional arrays, where those arrays have a fixed length, Array List does not have any fixed length.

VBA ArrayList is not part of the VBA list. Rather, it is an external library or object which we need to set the reference before we start accessing it.

Arrays in VBA are an integral part of any coding language. For example, using arrays in excel, we can store data with a single variable name by declaring the "lower limit & upper limit."

With regular arrays, we need to decide the lower limit and upper limit of the array. Therefore, we need to decide well in advance when declaring the variable in the case of static arrays. In the case of dynamic arrays, we need to decide the array's length after declaring the array by using the “ReDim” statement in VBA.

However, we have one more option: store the "N" number of values without declaring the lower and upper limits. This article will show you that option, i.e., VBA ArrayList."

Below are the examples of Excel VBA ArrayList.

Example #1 - Create Instance of VBA ArrayList

Since Excel VBA ArrayList is an external object, we need to create an instance to start using this. To create an instance, follow the below steps.

Step 1: Declare the variable as “ArrayList.”

Code:

Sub ArrayList_Example1()

   Dim ArrayValues As ArrayList

End Sub

Step 2: Since the ArrayList is an object, we need to create a new instance.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

End Sub

Step 3: We can keep storing values to the array variable using the "Add" method. In the below image, I have added three values.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

  ArrayValues.Add "Hello" 'First Value
  ArrayValues.Add "Good" 'Second Value
  ArrayValues.Add "Morning" 'Three Value

End Sub

Now, we have assigned three values. How do we identify which is the first, and how can we show the values or use them for our needs?

If you remember the traditional array type, we refer to the first array value like this “ArrayName(0).”

Similarly, we can use the same technique here, as well.

ArrayValue(0) = “Hello”

ArrayValue(1) = “Good”

ArrayValue(2) = “Morning”

Let's show this in the message box.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

  ArrayValues.Add "Hello" 'First Value
  ArrayValues.Add "Good" 'Second Value
  ArrayValues.Add "Morning" 'Three Value

  MsgBox ArrayValues(0) & vbNewLine & ArrayValues(1) & vbNewLine & ArrayValues(2)

End Sub

Now, run the code using the F5 key or manually. Then, we will see "Hello," "Good," and "Morning" in the VBA message box.

Like this, we can store any number of values with an Array List Object.

Example #2 - Store Values to Cells Using VBA ArrayList

Let's see the example of storing the assigned values to the cells in the worksheet. Now, look at the VBA code below.

Code:

Sub ArrayList_Example2()

  Dim MobileNames As ArrayList, MobilePrice As ArrayList
  Dim i As Integer
  Dim k As Integer

  Set MobileNames = New ArrayList

  'Names of the mobile
   MobileNames.Add "Redmi"
   MobileNames.Add "Samsung"
   MobileNames.Add "Oppo"
   MobileNames.Add "VIVO"
   MobileNames.Add "LG"

   Set MobilePrice = New ArrayList

   MobilePrice.Add 14500
   MobilePrice.Add 25000
   MobilePrice.Add 18500
   MobilePrice.Add 17500
   MobilePrice.Add 17800

End Sub

We have stored the names of the mobile and prices of the mobile with two array lists. Now, we need to insert these values into the worksheet for this. We need to use loops. The below loop will do the job for me.

Below is the overall code to store values on the worksheet.

Code:

Sub ArrayList_Example2()

  Dim MobileNames As ArrayList, MobilePrice As ArrayList
  Dim i As Integer
  Dim k As Integer

  Set MobileNames = New ArrayList

 'Names of the mobile
  MobileNames.Add "Redmi"
  MobileNames.Add "Samsung"
  MobileNames.Add "Oppo"
  MobileNames.Add "VIVO"
  MobileNames.Add "LG"
 
  Set MobilePrice = New ArrayList

  MobilePrice.Add 14500
  MobilePrice.Add 25000
  MobilePrice.Add 18500
  MobilePrice.Add 17500
  MobilePrice.Add 17800

  k = 0

  For i = 1 To 5
  Cells(i, 1).Value = MobileNames(k)
  Cells(i, 2).Value = MobilePrice(k)
  k = k + 1
  Next i

End Sub

When we run the code manually or using the F5 key, we will get the result below.