VBA ArrayList

Excel VBA ArrayList

Data structures are used to store a series of data in programming languages. It binds to the memory rather than address. An ArrayList is one of the data structures in excel. Comparing to normal arrays in excel ArrayList is dynamic. Therefore, no initial declaration of size is needed. ArrayList is not a part of VBA it is associated with an external library which can be used with VBA.

ArrayList can be defined as a list of a nearby memory location. Where the values are retrieved using the index numbers. The list starts from an index number ‘0’, the first element will be inserted into the ‘0’ index and rest is followed by 1, 2, 3, etc. ArrayList offers plenty of built-in operations, sorting, adding, removing, reversing, etc. are some among them.

Adding the Library

To use the ArrayList into the VBA it needs to include the library ‘mscorlib.dll’ which comes with .NET framework.

• Press F11 or right-click the sheet name to get the code window. Go to the VBA code window, from the main menu select Tools.

• The tools menu contains ‘references’ option and it consists of a list of libraries which supports VBA for including different objects. Click on the Reference option.

• It will lead you to a window with a list of different libraries which supports in VBA and Excel. Scroll down to find the ‘dll’. Tick mark to confirm the selection then press ‘OK’ button.

Now the library is included in your VBA code and it will support different methods associated with an ArrayList.

How to Create VBA ArrayList in Excel?

Below are the different examples to create VBA ArrayList in Excel.

Excel VBA ArrayList – Example #1

How to Add Values to the ArrayList using VBA?

ArrayList act as a list where we can add values. This will automatically store in the different portions starting from 0,1, 2, etc. The values can add or insert to the ArrayList using the add method.

In this example, you will learn how to add a list of values into an ArrayList. Follow the below steps to add ArrayList using VBA Code in excel.

Step 1: To add a list of values to an ArrayList create a function arraylist1.

Code:

Private Sub arraylist1()

End Sub

Step 2: Now we want to include the ArrayList into the function as an object where a list is declared as an ArrayList.

Code:

Private Sub arraylist1()

Dim alist As ArrayList

End Sub

Step 3: Since this is an object to use it, you have to create an instance of the ArrayList. Set a new instance for this object.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList

End Sub

Step 4: Now using the ‘Add’ property of an ArrayList adds the values to the ArrayList. Where the list is added into the index values in an order 0,1,2,3 etc.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList
alist.Add "192" 'index(0)
alist.Add "168" 'index(1)
alist.Add "1" 'index(2)
alist.Add "240" 'index(3)

End Sub

Step 5: To check whether the values got added into the list, let’s print the array values using a message box. To print the values each index is printed since the values are stored in these partitions.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList
alist.Add "192" 'index(0)
alist.Add "168" 'index(1)
alist.Add "1" 'index(2)
alist.Add "240" 'index(3)
MsgBox ("\\" & alist(0) & "." & alist(1) & "." & alist(2) & "." & alist(3))

End Sub

Step 6: Press F5 or run button to run the program and the values will be printed as below. Here an IP address is stored in the ArrayList and while printing the values extra notations are concatenated to form the IP address in a proper format.

Automation error in VBA

It is a common error happens while running an ArrayList. An automation error may encounter ‘Run-time Error ‘-2146232576 (80131700) Automation Error’

This is because of not the correct version of the .NET framework installed. To work with ArrayList you must have minimum .NET 3.5 or the higher versions of .NET framework.

Excel VBA ArrayList – Example #2

Sorting ArrayList Using VBA Code 

ArrayList supports different functions like sorting, reversing, etc. this help to sort the values inserted into an ArrayList. Once you add a list into the ArrayList it is possible to reverse the inserted list.

Follow the below steps to sort the ArrayList using VBA Code:

Step 1: Create a function called arraysort1 to perform the sorting within the inserted values into an ArrayList.

Code:

Sub arraysort1()

End Sub

Step 2: Declare an object ‘arraysort’ of the ArrayList. Use this object to add and sort the values within the ArrayList.

Code:

Sub arraysort1()

Dim arraysort As ArrayList

End Sub

Step 3: Similar to the first example need to create a new instance of the declared object. Set this instance as a new ArrayList.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList

End Sub

Step 4: Now using the ‘Add’ method insert the elements to the ArrayList. Which is not possessing any order on values. Randomly inserted some values into the list.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

End Sub

Step 5: To note the difference in the ArrayList, let’s print the ArrayList after inserting the values and before sorting it.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

Step 6: Press F5 on the keyboard or run button on the code window to run the program to print the ArrayList. The ArrayList is printed in the same order as it is inserted since we use the index numbers in its correct order.

Step 7: Now to this list apply the sort property of the ArrayList. Use the sort method to sort the inserted list. The sort property will sort the list of values in ascending order by default.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

arraysort.Sort

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

Step 8: Hit F5 or Run button under VBE to run this code, Where the values are sorted and printed in order from smallest value to largest.

Excel VBA ArrayList – Example #3

Reversing the ArrayList using VBA Code

When you want to reverse the order of inserted values in an ArrayList reverse method is available. This will reverse the order of the list from its current order. Now we have already sorted the ArrayList in the previous example, which is in ascending order.

Let’s try to reverse the sorted array to make it descending order. Use the reverse method of ArrayList to do this.

Code:

Sub arraysort2()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"
arraysort.Sort

arraysort.Reverse

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

After applying the reverse method, the ArrayList will become in descending order and use the message box to print the reversed array. The sorted list is changed from large value to small value.

Things to Remember

• ArrayList is dynamic in nature; it does not require re-initialization.
• Different built-in methods are associated with ArrayList.
• Compared to the array, ArrayList is easy to use in Excel VBA.
• The supporting .NET libraries should be included in the VBA to work with ArrayList.
• ArrayList is a continuing memory location which identified using index values.

Recommended Articles

This is a guide to VBA ArrayList. Here we discuss how to create ArrayList in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Guide to Using VBA Arrays
2. Excel Sort By Number
3. How to Sort in Excel Using VBA?
4. Tutorials on Sorting in Excel