VBA ArrayList

BY PAUL KELLY·3 COMMENTS

The VBA ArrayList is a much better alternative to the built-in VBA Collection. It contains much
richer functionality such as sorting, converting to an array, removing all items etc.
Check out the quick guide for an overview of what the ArrayList does. The rest of this post
provides examples of how to use the ArrayList.

Contents [hide]
 1 Quick Guide to the VBA ArrayList
 2 Description
 3 Declare and Create the VBA ArrayList
o 3.1 Late Binding
o 3.2 Early Binding
o 3.3 VBA ArrayList Automation Error
 4 Adding Items to the VBA ArrayList
 5 Reading through an ArrayList
 6 Sorting
 7 Cloning the VBA ArrayList
 8 Copying from an VBA ArrayList to an Array
 9 Copying to Arrays(2D)
 10 Array to a VBA ArrayList(1D)
 11 Array to VBA ArrayList(2D)
 12 Remove All Items from the ArrayList
 13 What’s Next?
 14 Get the Free eBook

Quick Guide to the VBA ArrayList

Task Examples

Access all items(For Each) Dim item As Variant

For Each item In coll
Debug.Print item
Next item

Access all items(For) Dim i As Long

For i = 0 To coll.Count - 1
Debug.Print coll(i)
Next i

Access item coll(1) or coll(2)

Access item added first coll(0)

Access item added last coll(coll.Count - 1)

Add item coll.Add "Apple"

coll.Add "Pear"

Check if items exists coll.contains("Apple")

Copy ArrayList Dim coll2 As Object

Set coll2 = coll.Clone

Copy to Array Dim arr As Variant

arr = coll.ToArray

Create Set coll = CreateObject("System.Collections.ArrayList")

Declare Dim coll As Object

Get number of items coll.Count

Insert Item coll.Insert 0, "Peach" ' First

coll.Insert 1, "Banana" ' Second
coll.Insert coll.Count, "Orange" ' Last

Remove all Items coll.Clear

Remove item at position coll.RemoveAt 0

Remove item by name coll.Remove "Apple"

Reverse the list coll.Reverse

Sort in ascending coll.Sort

Description
The ArrayList is similar to the VBA built-in Collection. It is not part of VBA, but it is in an
external library which we can access easily. The ArrayList is the same one that is used in the
language C#. As you would expect, the ArrayList has a built-in sort, array conversion and other
functionality that you would expect in a modern programming language. For the purpose of this
article, I will refer to it as the VBA ArrayList.

Declare and Create the VBA ArrayList

Like all external libraries we can create the ArrayList using early and late binding.

Late Binding
We use CreateObject to create the ArrayList using late binding:

Sub UsingArrayList()

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

End Sub

The disadvantage of late binding is that we don’t have access to the Intellisense. The advantage
is that it is better to use when distributing a VBA application to a user.

Early Binding
Early binding allows use to use the Intellisense to see what is available to use. We must first add
the type library as a reference and then select it from the reference list. We can use the following
steps to do this:
 1. Select Tools and then References from the menu.
2. Click on the Browse.
3. Find the file mscorlib.tlb and click Open. It should be in a folder like
this C:\Windows\Microsoft.NET\Framework\v4.0.30319.
4. Scroll down the list and check mscorlib.dll.
5. Click Ok.

You can now use the following code to declare the ArrayList using early binding:

Dim coll As New ArrayList

VBA ArrayList Automation Error

You may encounter the VB Run-time Error ‘-2146232576 Automation Error’ when trying to get
the ArrayList to work. Or sometimes your code has been working for a long time and then
suddenly this error appears.
This is caused by not having the correct .Net Framework version installed. The correct version is
3.5. It doesn’t matter if you have a later version like 4.7, you must have 3.5 installed.

Adding Items to the VBA ArrayList

Adding items to the ArrayList is very similar to how we add them to the Collection. We use
the Addmethod:

Sub AddingToList()

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items

coll.Add "Apple"

coll.Add "Watermelon"

coll.Add "Pear"
 coll.Add "Banana"

' Insert to first position

coll.Insert 0, "Plum"

End Sub

Reading through an ArrayList

We read through the ArrayList similar to the VBA Collection except that we read from zero
to Count-1 rather than from one to Count.
Note: We will use this PrintToImmediateWindow sub in the follow examples to show the
contents of the array after the various operations.

' Print all items to the Immediate Window(Ctrl + G)

' Items must be basic data type e.g. Long, String, Double

Sub PrintToImmediateWindow(coll As Object)

Dim i As Long

For i = 0 To coll.Count - 1

Debug.Print coll(i)

Next i

End Sub

We can use the For Each loop with the VBA ArrayList just like we use it with a Collection:

' Print all items to the Immediate Window(Ctrl + G)

' Items much be basic data type e.g. Long, String, Double

Sub PrintToImmediateWindowEach(coll As Object)

Dim item As Variant

 For Each item In coll

Debug.Print item

Next item

End Sub

Sorting
Sort will sort the VBA ArrayList in ascending order.
To sort in descending order simply use Reverse after Sort.
The following code shows an example of sorting in both ascending and descending order:

Sub Sorting()

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items

coll.Add "Apple"

coll.Add "Watermelon"

coll.Add "Pear"

coll.Add "Banana"

coll.Add "Plum"

' Sort

coll.Sort

Debug.Print vbCrLf & "Sorted Ascending"

' Add this sub from "Reading through the items" section

PrintToImmediateWindow coll
 ' Reverse sort

coll.Reverse

Debug.Print vbCrLf & "Sorted Descending"

PrintToImmediateWindow coll

End Sub

Sub PrintToImmediateWindow(coll As Object)

Dim i As Long

For i = 0 To coll.Count - 1

Debug.Print coll(i)

Next i

End Sub

Cloning the VBA ArrayList

We can create a copy of the ArrayList by using the Clone method. This creates a brand new
copy of the ArrayList.
It’s not the same as assigning the variable which means both variables point to the same
ArrayList e.g.

' Both variables point to the same ArrayList

Set coll2 = coll

We use Clone like this:

Sub Cloning()
 ' Create the ArrayList

Dim coll1 As Object

Set coll1 = CreateObject("System.Collections.ArrayList")

' Add items

coll1.Add "Apple"

coll1.Add "Watermelon"

coll1.Add "Pear"

coll1.Add "Banana"

coll1.Add "Plum"

' Creates a copy of the original ArrayList

Dim coll2 As Object

Set coll2 = coll1.Clone

' Remove all items from coll1

coll1.Clear

' Add PrintToImmediateWindow sub from "Reading through the items" section

Debug.Print vbCrLf & "coll1 Contents are:"

PrintToImmediateWindow coll1

Debug.Print vbCrLf & "coll2 Contents are:"

PrintToImmediateWindow coll2

End Sub

Sub PrintToImmediateWindow(coll As Object)

 Dim i As Long

For i = 0 To coll.Count - 1

Debug.Print coll(i)

Next i

End Sub

Copying from an VBA ArrayList to an Array

We can copy from the ArrayList to an array in one line using the ToArray method:

Sub CopyToArray()

' Declare and Create ArrayList

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items

coll.Add "Apple"

coll.Add "Watermelon"

coll.Add "Pear"

coll.Add "Banana"

coll.Add "Plum"

' Copy to array

Dim arr As Variant

arr = coll.ToArray

' Print the array

Debug.Print vbCrLf & "Printing the array contents:"

PrintArrayToImmediate arr
End Sub

' Prints the contents of a one dimensional array

' to the Immediate Window(Ctrl + G)

Sub PrintArrayToImmediate(arr As Variant)

Dim i As Long

For i = LBound(arr) To UBound(arr)

Debug.Print arr(i)

Next i

End Sub

Copying to Arrays(2D)
Copying to Two-Dimensional arrays is very useful in VBA because we can then write them
directly to the Range of a worksheet.
There ArrayList doesn’t have a function that does this. However all is not lost, I have created a
function to convert the ArrayList to a two-dimensional array.

' Convert to 2D Array

Function ToArray2D(coll As Object) As Variant

Dim arr As Variant

ReDim arr(1 To coll.Count, 1 To 1)

Dim i As Long

For i = 0 To coll.Count - 1

arr(i + 1, 1) = coll(i)
 Next i

ToArray2D = arr

End Function

We can use it like this:

Sub ClearArrayList()

' Declare and Create ArrayList

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items

coll.Add "Apple"

coll.Add "Watermelon"

coll.Add "Pear"

coll.Add "Banana"

coll.Add "Plum"

' Convert to a 2D array

Dim arr As Variant

arr = ToArray2D(coll)

Sheet1.Range("A1:A5") = arr

' We can also write directly to the range from the function

Sheet1.Range("C1:C5") = ToArray2D(coll)

End Sub
Array to a VBA ArrayList(1D)
As we have seen, there is an in-built function ToArray which will copy from an ArrayList to an
Array.
If we want to copy from an Array to an ArrayList we need to create our own function which I
have done below. Because we read through the items one at a time, it may be a bit slow if we
have a lot of data:

Function Arr1DToArrayList(arr As Variant) As Object

' Check that array is One Dimensional

On Error Resume Next

Dim ret As Long

ret = -1

ret = UBound(arr, 2)

On Error Goto 0

If ret <> -1 Then

Err.Raise vbObjectError + 513, "Arr1DToArrayList" _

, "The array can only have one 1 dimension"

End If

' Create the ArrayList

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items to the ArrayList

Dim i As Long

For i = LBound(arr, 1) To UBound(arr, 1)

coll.Add arr(i)

Next i

' Return the new ArrayList

Set Arr1DToArrayList = coll

End Function

You can use it like this:

Sub ReadFromArray1D()

Dim arr(1 To 3) As Variant

arr(1) = "PeterJ"

arr(2) = "Jack"

arr(3) = "Jill"

' Create the ArrayList

Dim coll As Object

Set coll = Arr1DToArrayList(arr)

PrintToImmediateWindow coll

End Sub

Array to VBA ArrayList(2D)

We can convert from a two-dimensional Array/Range to an ArrayList using the following
function:

Function Arr2DToArrayList(arr As Variant) As Object

' Check for 2D array

If UBound(arr, 2) > 1 Then

Err.Raise vbObjectError + 513, "RangeToArrayList" _

 , "The range/array can only have one column"

End If

' Create the array list

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items from array to ArrayList

Dim i As Long

For i = LBound(arr, 1) To UBound(arr, 1)

coll.Add arr(i, 1)

Next i

' Return new ArrayList

Set Arr2DToArrayList = coll

End Function

We can use it like this:

Sub ReadFromArray()

' Create the ArrayList

Dim coll As Object

Set coll = Arr2DToArrayList(Sheet1.Range("A1:A4").Value)

PrintToImmediateWindow coll

End Sub
Remove All Items from the ArrayList
We can remove all the items from an ArrayList by using the Clear function:

Sub ClearArrayList()

' Declare and Create ArrayList

Dim coll As Object

Set coll = CreateObject("System.Collections.ArrayList")

' Add items

coll.Add "Apple"

coll.Add "Watermelon"

coll.Add "Pear"

coll.Add "Banana"

coll.Add "Plum"

Debug.Print vbCrLf & "The number of items is: " & coll.Count

' Remove all item

coll.Clear

Debug.Print "The number of items is: " & coll.Count

End Sub

What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills
then why not try out the The Ultimate VBA Tutorial.