VBA ArrayList – Using the VBA ArrayList in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

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.

VBA ArrayList example

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

Removing items

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

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

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:

Clear items

To clear a VBA ArrayList simply use Clear:

On a regular basis use rather the VBA Collection instead of the VBA ArrayList