VBA ArrayList

VBA ArrayList

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.

 

Quick Guide to the VBA ArrayList

TaskMethodParametersExamples
Access item Itemindex - long integervalue = list.Item(0)
value = list.Item(3)
Access item added lastItemindex - long integervalue = list.Item(coll.Count - 1)
Access item added first Itemindex - long integervalue = list.Item(0)
Access all items(For Each)N/AN/ADim element As Variant
For Each element In fruit
Debug.Print element
Next element
Access all items(For)Itemindex - long integerDim i As Long
For i = 0 To list.Count - 1
Debug.Print list.item(i)
Next i
Add itemAddobject or valuelist.Add "Apple"
list.Add "Pear"
Copy ArrayList to another ArrayListClone NoneDim list2 As Object
Set list2 = list.Clone
Copy to ArrayToArrayNoneDim arr As Variant
arr = list.ToArray
Copy to a range(row)ToArrayNoneSheet1.Range("A1").Resize(1, list.Count).Value = list.ToArray
Copy to a range(column)ToArrayNoneSheet1.Range("A3").Resize(list.Count, 1).Value = WorksheetFunction.Transpose(list.ToArray)
Create CreateObject"System.Collections.ArrayList"Dim list As Object
Set list = CreateObject("System.Collections.ArrayList")
DeclareN/AN/ADim list As Object
Find - check if item existsContainsitem to findlist.Contains("Apple")
Find the position of an item in the ArrayListIndexOf1. Item to find.
2. Position to start searching from.
Dim index As Long
' Search from 0 position
index = fruit.IndexOf("Pear", 0)
Get number of itemsCountNonetotalElements = list.Count
Insert ItemInsert1. Index - position to insert at.
2 Value - object or value to insert.
list.Insert 0, "Peach" ' First
list.Insert 1, "Banana" ' Second
list.Insert list.Count, "Orange" ' Last
Remove all Items
ClearNonelist.Clear
Remove item at positionRemoveAtIndex - position where the item islist.RemoveAt 0
Remove item by nameRemoveItem - the item to remove from the ArrayListlist.Remove "Apple"
Remove a range of ItemsRemoveRange1. Index - starting postion.
2. Count - the number of items to remove.
list.RemoveRange 1,3
Reverse the listReverseNonelist.Reverse
Sort in ascendingSortNonelist.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:

' https://excelmacromastery.com/
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

Update 12-Nov-2019: Intellisense doesn’t currently work for the ArrayList.
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 Add method:

' https://excelmacromastery.com/
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
' https://excelmacromastery.com/
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
' https://excelmacromastery.com/
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:

' https://excelmacromastery.com/
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

 

' https://excelmacromastery.com/
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:

' https://excelmacromastery.com/
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

 

' https://excelmacromastery.com/
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:

' https://excelmacromastery.com/
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)
' https://excelmacromastery.com/
Sub PrintArrayToImmediate(arr As Variant)
    
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i
       
End Sub

 

Writing Directly to a Range

One of the biggest advantages of the ArrayList is that we can write the contents directly to a range.

The code below writes the contents to both a row and a column:

' Convert to 2D Array
' https://excelmacromastery.com/
Sub ClearArrayList()

    ' Declare and Create ArrayList
    Dim fruit As Object
    Set fruit = CreateObject("System.Collections.ArrayList")
    
    ' Add items
    fruit.Add "Apple"
    fruit.Add "Watermelon"
    fruit.Add "Pear"
    fruit.Add "Banana"
    fruit.Add "Plum"
    fruit.Add "Peach"
    
       
    ' Write to the range
    Sheet1.Cells.ClearContents
    
    ' Write to a row
    Sheet1.Range("C1").Resize(1, fruit.Count).Value = fruit.toArray
    
    ' Write to a row
    Sheet1.Range("A1").Resize(fruit.Count, 1).Value = WorksheetFunction.Transpose(fruit.toArray)
    
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:

' https://excelmacromastery.com/
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:

' https://excelmacromastery.com/
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

 

Remove All Items from the ArrayList

We can remove all the items from an ArrayList by using the Clear function:

' https://excelmacromastery.com/
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.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 


 
 

14 comments

  1. Is the ArrayList Mac compatible? I was looking for an alternative to the Collection as discovered Collections are extremely slow at cleaning up objects placed in the Collection Item and exploring if using an Array would help. Note Will be adding, deleting, iterating on large datasets with Key, Item pairs. A dictionary would be great but isn’t Mac compatible.

  2. I’ve scrapped using the ArrayList idea as realized in VBA, after testing various data structures, as too all were slow at cleaning up large datasets that contain objects. You start to notice the effects once got 100,000 custom objects and appears to exponentially take longer to dereference all the objects. It must be something to do with VBA and it’s garbage collection.

    1. Hi Mark, if the memory capacity is not the concern, why not to create a new collection instead of cleaning it up item by item and let garbage collector take care about the rest?

      Instead of:
      Do Until Col.Count = 0
      Col.Remove(1)
      Loop

      Do This:
      Set Col = New Collection

      it is the fastest way how to “Clean Up” then collection.

  3. Paul,
    You mention that Early Binding allows Intellisense, but I have selected mscorlib.dll from my ms Access vba references and –no intellisense. I’m trying to get some vba based demos of the various data structures in Systems.Collections ( Stack, Queue, ArrayList…).

    I enjoy your site videos and your more recent youtube (class module, collections, dictionary)
    Thanks in advance.
    jack

    1. Hi Jack,

      The Intellisense doesn’t appear to work with this library anymore. I have tried with all the different .Net libraries and the result is the same.

      It may have been caused by a Windows update. I will post more information when I know more about the cause.

      -Paul

  4. Hello Paul

    I am currently using an array of structures with each array member representing an object. I would like to use something more object oriented. I have need to sort, determine existence of a object, create references to another similar array of structures, etc.
    Will an array list support an array of structures? And will you cover that in Wednesday night’s tutorial?

    Thanks in advance
    Mike
    My objects have multiple

Leave a Reply

Your email address will not be published. Required fields are marked *