Excel VBA ArrayList: How to Use It and When to Choose It

VBA & macros · Updated November 21, 2026

An Excel VBA ArrayList is a resizable, zero-based list that can hold items and provides methods such as Add, Remove, Sort, and Reverse. In Excel for Microsoft 365 on Windows, create one with Set items = CreateObject("System.Collections.ArrayList"); it is useful when you need a list that grows or shrinks more easily than a fixed-size VBA array.

What an ArrayList is—and where it comes from

ArrayList is not a native VBA data type or a worksheet feature. It is a .NET Framework collection exposed to VBA through COM automation. That distinction matters: VBA can use its methods through an object variable, but the list’s behavior and availability depend on Windows and the installed .NET Framework environment.

Unlike a VBA array, an ArrayList does not require you to declare its final size before adding values. It keeps track of its item count and expands its internal storage as needed. It also has built-in methods for common list operations, so you do not have to write your own loops for every insertion, removal, or sort.

ArrayList stores values as objects and can hold different kinds of values. That flexibility is convenient, but it also means your code should check or control the types it adds. Mixed values can create problems when you try to sort or compare them.

Create an ArrayList in VBA

The simplest approach is late binding: declare the variable as Object and create the .NET collection at run time. This avoids adding a project reference in the VBA editor. The following example reads nonblank names from column A, sorts them, and writes them to column B.

Option Explicit

Sub SortNamesWithArrayList()
    Dim names As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set names = CreateObject("System.Collections.ArrayList")

    ' Add nonblank names from column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        If Len(ws.Cells(i, "A").Value2) > 0 Then
            names.Add CStr(ws.Cells(i, "A").Value2)
        End If
    Next i

    ' Sort the list, then write its zero-based items to column B.
    names.Sort
    For i = 0 To names.Count - 1
        ws.Cells(i + 2, "B").Value = names(i)
    Next i
End Sub

Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. This sample assumes the headers are in row 1 and the source names are in column A of a worksheet named Sheet1. It writes results beginning in B2. If a previous run produced more results than the current run, old values below the new output may remain, so clear the intended output area first if that matters.

To run it, open the Visual Basic Editor with Alt+F11 on Windows, insert a standard module through Insert > Module, paste the procedure, and run it from the editor or Excel’s Macro dialog. On Mac, Option+F11 or Fn+Option+F11 may open the editor depending on the keyboard and system settings, but this .NET-based ArrayList approach is not supported there.

How its indexes and methods work

ArrayList indexes start at zero, not one. If items.Count is 3, the valid indexes are 0, 1, and 2. This differs from the common VBA habit of using arrays that start at 1, though VBA arrays can also be zero-based depending on how they are declared. When looping through an ArrayList by index, use a bound based on Count and account for the empty-list case.

Common operations include:

  • Add value appends a value to the end of the list.
  • Insert index, value puts a value at a chosen position and shifts later items.
  • Remove value removes a matching value; RemoveAt index removes the item at a position.
  • Contains value checks whether a matching item exists, and IndexOf value returns its position or -1 if it is absent.
  • Sort sorts the list in place; Reverse reverses the current order.
  • Clear removes all items, while ToArray returns the list contents as an array.

The list’s Count property reports how many items it contains. Capacity refers to the amount of internal storage reserved, which can be larger than Count; most macros do not need to manage Capacity directly. A simple loop that processes each item can use For Each with a Variant variable, while index-based loops are useful when the position itself matters.

Sorting, duplicates, and data types

ArrayList allows duplicate values. Adding the same name or number twice creates two entries; it does not automatically enforce uniqueness. If your task is to build a unique list, check with Contains before adding each value, or choose a collection designed around keys, such as a Scripting.Dictionary.

Sort is most predictable when every item is the same kind of comparable value. For example, a list of strings can be sorted as strings, and a list of numeric values can be sorted as numbers. If a list mixes incompatible types, the sort can fail because the items may not be comparable to one another. Convert incoming worksheet values deliberately—for instance, to strings for names or to an appropriate numeric type for measurements—rather than relying on implicit conversions.

Text sorting is not the same as Excel’s full worksheet sort. ArrayList does not know about header rows, multiple sort keys, cell formatting, or an Excel table’s data range. If you need those worksheet-specific behaviors, use Excel’s Sort feature or sort a range through the Excel object model instead. Also decide how blanks, errors, and numeric-looking text should be handled before loading cells into a list.

When to choose an ArrayList

ArrayList is a reasonable fit for temporary, in-memory work when the number of items changes and you want convenient insertion, removal, or sorting methods. It can be helpful when a macro builds a working set incrementally, removes entries as conditions change, or needs to reorder values before writing them back to a sheet.

It is not automatically faster than every alternative. Each call from VBA to a COM object has overhead, and repeated worksheet-cell reads and writes are often a larger performance cost than the choice of collection. For large datasets, read a range into a VBA array in one operation, process values in memory, and write the result back in bulk where practical. Choose based on the operations and platform your workbook requires, not just the shorter syntax.

ArrayList compared with VBA arrays, Collection, and Dictionary

A dynamic VBA array is a built-in choice and works across Excel platforms that support VBA. You can resize it with ReDim, and ReDim Preserve retains existing values when resizing the final dimension. Arrays are often a good fit for processing a known block of worksheet data efficiently, but inserting or deleting an item in the middle generally requires manual shifting or rebuilding.

A VBA Collection is also built in and can grow as items are added. It supports keys and iteration, but it does not provide ArrayList’s built-in sorting method or indexed insertion in the same way. A Scripting.Dictionary is useful for key-value lookups and uniqueness checks, but it is a different structure and does not behave as a sorted list by default. These alternatives avoid depending on this particular .NET collection, though some COM-based options have their own platform constraints.

For data that belongs on a worksheet and needs sorting, filtering, formulas, or visible user interaction, a range or Excel Table may be the clearest representation. For a small fixed group of values, a simple VBA array can be easier to understand. Use an ArrayList when its dynamic list operations are genuinely useful and the Windows-only dependency is acceptable.

Availability, references, and common errors

Late binding with CreateObject does not require selecting a reference in Tools > References. It also means VBA does not provide compile-time knowledge of the object’s methods, so a misspelled method name may only be caught when the code runs. Early binding can provide editor assistance, but setting up a .NET reference through the VBA project is less straightforward and may reduce portability. For many small macros, late binding is simpler.

If CreateObject("System.Collections.ArrayList") raises an error such as “ActiveX component can’t create object,” the collection could not be activated in that environment. Check that the macro is running in desktop Excel on Windows and that the required .NET Framework components and COM registration are available. The exact cause depends on the machine’s configuration; changing a VBA reference alone will not make a Mac or Excel for the web support .NET COM objects.

Other frequent mistakes include using a one-based loop, assuming a list with zero items has an item at index 0, sorting mixed data types, or expecting Remove to delete every duplicate. To avoid an invalid-index error, only enter an index loop when Count is greater than zero, and use RemoveAt only with an index from 0 through Count minus 1. When removing several items by index, work backward so that shifting positions do not make later indexes point to the wrong entries.

Platform and workbook considerations

This guidance targets desktop Excel for Microsoft 365 on Windows. The same VBA editor and basic language exist in Excel for Mac, but Mac Excel does not provide the Windows .NET COM automation environment needed for this ArrayList creation method. A workbook containing this macro may open on a Mac, but the procedure will not become cross-platform merely because it is saved as an .xlsm file. Excel for the web does not run VBA macros at all.

For a workbook shared with Mac users, replace the ArrayList dependency with a VBA array or Collection where possible, or provide a separate platform-appropriate implementation. If a macro uses other Windows-only COM objects as well, changing only the list will not make the whole macro portable. Test the complete workflow on each platform you intend to support.

Frequently Asked Questions

Is ArrayList built into VBA?
No. It is a .NET Framework class that VBA can access through COM on supported Windows systems. VBA arrays and Collections are built-in alternatives.

How do I create an ArrayList in Excel VBA?
Declare an object variable and assign CreateObject("System.Collections.ArrayList") to it with the Set keyword. Late binding does not require adding a reference in Tools > References.

Does ArrayList start at 0 or 1?
Its indexes start at 0. If Count is 4, the items are at indexes 0, 1, 2, and 3.

Can an ArrayList contain duplicate values?
Yes. Add does not check for duplicates. Use Contains before adding when duplicates should be excluded, or use a keyed structure if your task is primarily lookup by unique key.

Can I use this ArrayList code on Mac or in Excel for the web?
No. This .NET COM-based method is for desktop Excel on Windows; Excel for Mac does not support the same COM activation, and Excel for the web does not run VBA. Use a VBA array or Collection for a more portable desktop macro, while remembering that web Excel cannot run that macro.

Is ArrayList always better than a VBA array?
No. ArrayList offers convenient dynamic list methods, while arrays are built in, broadly portable, and often suitable for bulk data processing. The better option depends on whether you need list operations, performance, or cross-platform compatibility.

Related

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.