VBA ArrayLists

An ArrayList is a data structure which uses an underlying array with extra capacity and a size variable which keeps track of the number of items in the array. The purpose of the ArrayList is to make adding elements to an array more efficient. Whenever the size of the ArrayList reaches to capacity of the underlying array, the array is reallocated with double its capacity to allow space for additional elements to be added. There is no built-in ArrayList class in VBA. The .NET ArrayList class can be used by installing .NET version 3.5 and setting a reference to mscorlib.tlb. Alternatively, an ArrayList class can be implemented using VBA.

.NET ArrayList

To access the .NET ArrayList class, Microsoft .NET Framework 3.5 must be installed on the user's computer. A reference can then be set to the mscorlib library. No intellisense is available for the mscorlib ArrayList and the Object Browser has limited information about the classes in mscorlib. If run-time error -2146232576 (80131700) occurs when trying to instantiate an ArrayList it is because the correct version of the .NET framework is not installed.

Public Sub Example()

    'Set reference to mscorlib.tlb
    Dim A As ArrayList
    Set A = New ArrayList

End Sub

Public Sub Example()

    'Late-binding
    Dim A As Object
    Set A = CreateObject("System.Collections.ArrayList")

End Sub

VBA Implementation

An ArrayList can be implemented using VBA in a variety of ways. Specific implementations can be used to handle specific data types and provide different functionality. ArrayList implementations include:

• clsArrayListVariant (Value types only)
• clsArrayListString
• clsArrayListObject
• clsArrayListUDT

Download an implementation from GitHub and import the module into a VBA project.

Public Sub Example()

    Dim AL As clsArrayListString
    Set AL = New clsArrayListString
    
    AL.Append "A"
    AL.Append "B"
    AL.Append "C"
    AL.Append "D"
    AL.Append "E"
    AL.Append "F"
    AL.Append "G"

    AL.Reverse
    Debug.Print AL.JoinString(",")

    AL.Sort
    Debug.Print AL.JoinString(",")
    
End Sub