VBA ArrayList

In vba we have built in collections of data types, functions variables and other important statements but similar to this we have array list in VBA in which a user can modify and put own collections of variables and user defined functions in an array, there are certain keywords for the array list to design it.

Excel VBA ArrayList

VBA ArrayList is a kind of data structure we use in VBA to store the data. ArrayList in Excel VBA is a class used to create an array of values. This unlike traditional arrays where those arrays have fixed length but Array List doesn’t any fixed length.

VAB 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 of an integral part of any coding language. By 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 we need to decide well in advance at the time of declaring the variable in case of Static Arrays and in case of Dynamic Arrays we need to decide the length of the array after declaring the array by using “ReDim” statement in VBA.

However, we have one more option where we can store the “N” number of values without declaring the lower limit and upper limit. In this article, we will show you about that option i.e. “VBA ArrayList”

To set the reference to VBA ArrayList object to follow the below steps.

Step 1: Go to Tools > References

Step 2: Object library reference window will appear in front of you. Select the option “mscorlib.dll”

Step 3: Click on OK too. Now we can access the VBA ArrayList.

Examples of VBA ArrayList in Excel

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 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 Array List 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: Now we can keep storing values to the array variable by using “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 one and how can we show up the values or use them to our needs.

If you remember traditional array type we refer 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 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 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 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 below VBA code.

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

With two array lists, I have stored Names of the Mobile and Prices of the Mobile. Now we need to insert these values to 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 to 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 then, we will get the below result.

Recommended Articles

This has been a guide to VBA ArrayList. Here we learn how to create ArrayList in VBA which is used to store data along with simple to advanced examples. Below are some useful excel articles related to VBA –

• Excel VBA Debug Print
• VBA UCase
• Text Box in VBA
• Excel VBA Declare Array