VBA ArrayList – Using the VBA ArrayList in Excel

The VBA ArrayList is also a very useful data structure if you want to work with dynamic VBA arrays but don’t want the hassle of having to constantly redefine (Redim) the size of the array. ArrayLists don’t have a fixed size so you can keep adding items to it. However, in VBA in can be better superseded by the Native VBA Collection.

Other VBA Data Structures

VBA ArrayList example

Below is an example of creating a VBA ArrayList and adding an item:

Dim arrList as Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

arrList.Add "Hello" 'Adding items to an ArrayList
arrList.Add "You"
arrList.Add "There"
arrList.Add "Man"

'Get number of items
Debug.Print arrList.Count 'Result: 3 

For Each item In arrList
  Debug.Print item
Next Item
'Result: Hello, You, There, Man

Removing items

You can remove all items of a particular value from a VBA ArrayList by using Remove:

Dim arrList as Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

arrList.Add "Hello"  'Add "Hello"
arrList.Add "You"    'Add "You"
arrList.Remove "You" 'Remove "You"

For Each item In arrList
  Debug.Print item
Next Item
'Result: Hello

To remove items at a particular index use RemoveAt instead. Remember that indexing starts at 0.

Dim arrList as Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

arrList.Add "Hello"  'Add "Hello"
arrList.Add "You"    'Add "You"
arrList.Add "There"    'Add "There"
arrList.RemoveAt (0)

For Each item In arrList
  Debug.Print item
Next Item
'Result: You, There

Get and Count items

To get an item at a particular index using the Item property. Remember that indexing in the VBA ArrayList starts at 0.

To count the number of items in the VBA ArrayList simply use the Count property:

Dim arrList as Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

arrList.Add "Hello"  'Add "Hello"
arrList.Add "You"    'Add "You"

Debug.Print arrList.Count 'Result: 2

Debug.Print arrList.Item(1) 'Result: You

Clear items

To clear a VBA ArrayList simply use Clear:

Dim arrList as Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

arrList.Add "Hello"  'Add "Hello"
arrList.Add "You"    'Add "You"

arrList.Clear

Debug.Print arrList.Count 'Result: 0
On a regular basis use rather the VBA Collection instead of the VBA ArrayList