VBA ArrayList: How to Create and Use It in Excel VBA

In VBA, an ArrayList is a resizable, zero-based list that you can create on Windows with CreateObject("System.Collections.ArrayList"). It is not a built-in VBA data type: it comes from the .NET Framework, so a VBA Collection or dynamic array may be a better choice when portability or native VBA behavior matters.

What a VBA ArrayList Means

An ArrayList is an ordered container: it holds items in sequence, lets code add or remove items, and adjusts its size as the contents change. Unlike a fixed-size array, you do not normally need to decide how many entries it will hold before adding the first one. Unlike a worksheet range, it exists in memory while the macro runs; it does not automatically read from or write to cells.

The name can cause confusion because VBA itself has arrays, and VBA also has a built-in Collection object. ArrayList refers specifically to the .NET class System.Collections.ArrayList, accessed from VBA through COM automation. In typical Excel VBA code, it is used late-bound: CreateObject creates the object at run time, and the variable is declared As Object.

An ArrayList can hold values of different kinds, such as text and numbers, as well as object references. That flexibility is useful for short-lived lists assembled while a procedure runs, but it also means VBA does not check the type of each item at compile time. If a list should contain only dates, for example, the macro must enforce that rule itself.

Create an ArrayList in Excel VBA

In Excel for Microsoft 365 on Windows, put this code in a standard VBA module and run the procedure. It creates a list, adds three strings, and prints its count and first item in the Immediate window. Open that window in the Visual Basic Editor with Ctrl+G. On Mac, the equivalent shortcut is Control+G in the Visual Basic Editor, though this particular .NET-based approach is not supported there.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even when a particular example only prints values.

Sub CreateArrayListExample()
    Dim items As Object

    ' Create a .NET ArrayList through COM automation.
    Set items = CreateObject("System.Collections.ArrayList")
    items.Add "pear"
    items.Add "plum"
    items.Add "apple"

    Debug.Print items.Count
    Debug.Print items.Item(0)
End Sub

The Immediate window shows 3 and then pear. The item at index zero is the first item, because ArrayList indexing starts at zero. The Add method appends an item to the end. Its returned value is the position where that item was inserted, but the example does not need to store that return value.

Late binding keeps setup simple: you do not need to select a .NET library in Tools > References. The trade-off is that VBA cannot validate ArrayList method names and argument types at compile time. A misspelled member name or an unavailable COM class will fail when the code runs.

Adding, Reading, Removing, and Checking Items

ArrayList offers methods and properties for common list operations. Its Count property reports how many items it currently contains, while Item(index) gets or sets the item at a zero-based position. Common operations include:

Add(value) appends one item.
Insert(index, value) puts an item at a chosen position and shifts later items along.
Remove(value) removes a matching item; when duplicates exist, it removes the first match.
RemoveAt(index) removes the item at a specified zero-based position.
Contains(value) checks whether a matching item is present.
Clear removes every item, and Reverse reverses the current order.

For example, after adding “pear,” “plum,” and “apple,” items.RemoveAt(1) removes “plum,” leaving “pear” and “apple.” The new second item is at index 1. This is different from VBA’s built-in Collection, whose first item is addressed as item 1.

You can loop through the list with For Each. The loop variable should be declared, commonly as a Variant when the list may contain different value types. Use Item(index) when you need a particular position, and For Each when the position itself is not important. Avoid changing the list while iterating it unless the loop logic accounts for items being shifted or removed.

Because these members are late-bound, VBA’s editor may not offer the usual member list after you type items.. Check spelling and index bounds carefully: with three items, valid indexes are 0, 1, and 2, not 1 through 3.

Sorting and Working with Different Data Types

Call Sort to put compatible items in order, or Reverse to flip their current order. Sorting works best when the list contains values that can be compared consistently, such as all strings or all numbers. A mixed list containing unrelated types may not have a meaningful common ordering and can produce a run-time error. If sorting matters, validate or convert the values before calling the method.

For a practical example, begin with a list containing the strings “pear,” “plum,” and “apple,” in that order. After items.Sort, the strings are in ascending comparison order: “apple,” “pear,” “plum.” The exact behavior for text comparisons can depend on the underlying .NET comparison rules and culture; do not assume it provides a custom business-specific order. For rules such as “ignore punctuation” or “sort by a separate date,” build that logic explicitly or use a purpose-built VBA routine.

ArrayList equality and comparison are also not a substitute for defining your data rules. For example, if a list contains a number and a numeric-looking string, they are not necessarily interchangeable for membership checks or sorting. Convert values to a consistent type when the distinction matters, and test boundary cases such as empty values, duplicate entries, and text with different capitalization.

ArrayList Indexing and VBA Arrays

ArrayList always uses zero-based indexes. VBA’s Option Base 1 setting does not change that. Nor does it affect an array declared with explicit bounds, such as Dim scores(1 To 5) As Long. Keep the two indexing systems separate when copying data between a list and an array: an ArrayList with five items has indexes 0 through 4, while that explicitly declared VBA array has indexes 1 through 5.

Dynamic VBA arrays are often preferable when the data has a known type or needs to be processed in bulk. A dynamic array can be resized with ReDim; using ReDim Preserve retains existing values, but in a multidimensional array VBA can preserve only the last dimension while resizing. ArrayList is convenient when items are added and removed individually, but every item is exposed through a general object interface rather than a strongly typed VBA array.

Neither structure is automatically connected to worksheet cells. A macro that reads thousands of cells should generally transfer a range into a Variant array, process the data in memory, and write results back in a block. ArrayList can still be useful for a changing collection of selected values, but repeatedly exchanging individual cell values with Excel can dominate the work and make code slower.

ArrayList Compared with Collection and Dictionary

Choose the container based on what the code needs, not simply because one has “array” in its name. These are the main differences:

ArrayList: A resizable, ordered list with zero-based indexing, sorting, reversing, and convenient removal by position. It depends on Windows COM/.NET availability when used this way.
Collection: A built-in VBA object available without creating a .NET object. It is one-based, supports optional string keys, and is useful for storing items in order. It does not provide ArrayList’s built-in sorting method.
Dynamic array: A native VBA structure that can use a specific data type, such as Long or String. It suits indexed data and bulk processing, but resizing and removal require more deliberate code.
Scripting.Dictionary: A key-value structure suited to looking up a value by a unique key. It is not an ordered-list replacement in the general sense, and the commonly used COM-based Scripting Runtime approach has Windows compatibility limits.

For example, use a Collection if a macro needs a straightforward ordered sequence and named keys, or a dynamic array if it needs typed numeric data and predictable indexing. Choose a Dictionary when the key, such as an employee ID, is how the macro finds an entry. Choose ArrayList when an ordered, changeable list and built-in sorting are useful and the Windows environment is controlled.

Compatibility and Common Errors

This CreateObject approach targets desktop Excel for Windows. It relies on Windows COM automation and the registered .NET Framework class. A machine’s configuration can affect whether that class can be created, so do not assume every Windows installation has identical .NET components or registration. Excel for Mac does not provide this Windows COM/.NET route, and Excel for the web does not run VBA macros. A workbook intended for Mac users should use native VBA arrays or Collections instead, or provide a separate implementation.

A “Can’t create object” or automation error when the line with CreateObject runs usually means the requested class is unavailable to that environment. It is not fixed by changing Dim items As Object to another variable declaration. If the macro must run across different computers, use a VBA-native container rather than depending on local COM registration.

Another frequent problem is an “index out of range” error caused by treating the list as one-based or requesting an index after an item has been removed. Check Count before accessing an index, remembering that an empty list has no valid item index. Sorting errors commonly point to incompatible values; normalize the contents before sorting. Finally, late binding means errors appear at run time, so test the macro with representative data and handle errors where the workbook’s workflow requires a clear recovery path.

When to Choose an ArrayList

ArrayList is a reasonable choice for a Windows-only macro that builds an ordered list of an unknown or changing length, removes entries by index, or needs a quick built-in sort. It can make code shorter than repeatedly resizing an array for individual insertions and deletions. That convenience does not make it universally faster or safer: performance depends on the workload, and late-bound calls and generic values have their own costs.

Before adopting it, ask whether the workbook will be used on Mac, whether its data has a single known type, whether keys are more important than order, and whether the macro needs to exchange data with worksheet ranges. A Collection, dynamic array, or Dictionary may be a clearer and more portable fit. For a shared workbook, compatibility often matters more than a small reduction in code.

Frequently Asked Questions

Is ArrayList built into VBA?
No. The ArrayList used from VBA is a .NET Framework class created through COM automation. VBA’s built-in alternatives include Collection objects and arrays.

Does ArrayList start at 0 or 1?
It starts at 0. The first item is Item(0), regardless of the VBA module’s Option Base setting.

Can I use System.Collections.ArrayList on Excel for Mac?
Not through the Windows COM/.NET technique shown here. Use a native VBA array or Collection for code that must run on Mac.

Can an ArrayList contain both text and numbers?
Yes, it can hold values of different types. However, mixed types can complicate comparisons and sorting, so use consistent types when operations depend on their values.

Should I use ArrayList or Collection?
Use ArrayList when its resizable ordered-list behavior and methods such as Sort suit a Windows-only macro. Use Collection when you want a built-in VBA object, one-based access, or optional keys and need broad platform compatibility.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.