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 a 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 VBAArrays In VBAArray Variable is a variable which stores multiple values in a single variable. To use an array variable in VBA, you must first define it. You can define the array variable with or without its length. are an integral part of any coding language. By using arrays in excelArrays In ExcelArray formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results., 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 the case of Static Arrays, and in the case of Dynamic Arrays, we need to decide the length of the array after declaring the array by using the “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.
- Go to Tools > References.
- Object library reference window will appear in front of you. Select the option “mscorlib.dll.”
- 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 an instance, follow the below steps.
Step 1: Declare the variable as “ArrayList.”
Sub ArrayList_Example1() Dim ArrayValues As ArrayList End Sub
Step 2: Since the Array List is an object, we need to create a new instance.
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 the “Add” method. In the below image, I have added three values.
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.
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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided..
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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
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.
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.
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 –