Excel VBA Collection: How It Works and When to Use It

An Excel VBA Collection is an in-memory container that holds related items and lets you retrieve them by their position or, optionally, a unique string key. Create one with Set items = New Collection, add values with items.Add, and read them with items(index) or items(key).

What a VBA Collection is

A Collection is a built-in VBA object for keeping a group of values or object references together while a macro runs. It is useful when the items to process are determined at runtime, such as a changing list of worksheet names, selected records, or workbook objects. Unlike a worksheet range, it lives in memory rather than in cells; its contents do not automatically persist when the macro ends or the workbook closes.

Collections preserve an order. The first item has position 1, the next has position 2, and so on. This is different from many programming-language lists that start counting at zero, so using index 0 with a VBA Collection is a common source of errors. A collection’s Count property reports how many items it currently contains.

The word Collection also appears in Excel’s object model. For example, ThisWorkbook.Worksheets is Excel’s collection of worksheets. That is an Excel-provided object with its own behavior; it is related in concept to the generic VBA Collection object, but it is not the same object.

How a Collection works

The main operations are adding an item, reading an item, checking the count, and removing an item. Items can be values such as text, numbers, or dates, as well as object references such as a Worksheet or a custom object. The Collection does not require you to declare a fixed capacity before adding items.

Add appends an item by default, or places it before or after a specified existing position or key.
Item retrieves an item by its one-based position or by its key. It is the default property, so parentheses can be used without writing the word Item.
Count returns the current number of items.
Remove deletes an item by position or key.

For a simple example, suppose a macro gathers a variable number of department names. A Collection lets the code add each name as it is found instead of reserving a fixed-size array in advance. The code can then loop over the names or retrieve a particular one later.

Creating, adding, and retrieving items

Declare a variable as Collection and use New to create the object. The following complete macro shows ordinary values, a key, the count, and retrieval by both position and key. The Debug.Print statements send output to the Immediate window in the Visual Basic Editor.

Sub DemoCollection()
    ' Create a collection and add two department names.
    Dim items As Collection
    Set items = New Collection

    items.Add Item:="North", Key:="N"
    items.Add Item:="South", Key:="S"

    Debug.Print items.Count
    Debug.Print items(1)
    Debug.Print items("S")
End Sub

In this example, the count is 2, items(1) returns North, and items("S") returns South. Because the key is optional, the code could add items without keys and access them only by position. Named arguments make the intent clear, especially because Add also accepts optional placement arguments.

To insert an item at a particular point, use Before or After and identify an existing item by its position or key. For example, items.Add Item:="Central", Key:="C", Before:=2 places Central before the current second item. The new item shifts the later positions. Do not specify both Before and After in the same call.

Using keys and understanding their limits

A key gives an item a label that can be used for lookup instead of relying on its changing position. In the example, N and S are keys. Keys are strings, and a key must be unique within that Collection. Adding an item with a key that is already in use raises an error rather than replacing the earlier item.

Keys are particularly helpful when order might change or when code needs to retrieve an item by a known identifier. For instance, a macro could store a worksheet object under a short label and retrieve that worksheet by label later. A key is not a separate value stored alongside the item in a way that the Collection can list for you: the built-in Collection has no Keys method and no Exists method. If you need to enumerate all keys or test key membership directly, a Dictionary is often a better fit.

Collection keys are not general-purpose database IDs with rich search features. They are simple lookup labels. If duplicate labels are legitimate, consider using positional access, storing a nested collection, or choosing a data structure that supports the lookup rules your task requires. Do not assume that a key can be changed after adding an item; the Collection interface does not provide a key-editing operation.

Looping through, removing, and changing items

A For Each loop is a convenient way to process every item without calculating positions. Declare the loop variable as a Variant for a collection of mixed or ordinary values. This example prints each value to the Immediate window:

Sub PrintCollectionItems()
    ' Loop through a collection of text values.
    Dim items As Collection
    Dim entry As Variant

    Set items = New Collection
    items.Add Item:="North"
    items.Add Item:="South"

    For Each entry In items
        Debug.Print entry
    Next entry
End Sub

You can remove an item with its position or key, as in items.Remove 1 or items.Remove "S". Removing an item changes the positions of the items after it: if position 2 is removed, what was position 3 becomes position 2. Code that repeatedly removes items while counting upward can therefore skip an item or address the wrong position. When removing by position during a counted loop, work from the highest position down, or use a strategy that does not rely on positions staying fixed.

A Collection does not provide a direct operation for replacing a stored value at a chosen position. One option for a simple value is to remove the old item and add the replacement, keeping in mind that this can affect order and keys. If the item is an object, the Collection stores a reference to it; code can change the object’s properties without replacing the reference. When retrieving an object, use Set, as in Set selectedSheet = items("Data").

Collection compared with arrays and Dictionaries

Choose a Collection when a macro needs an ordered group that can grow as items are discovered, and when optional key-based retrieval is useful. It is built into VBA, so it does not require adding a reference to an external library. Its simple interface is often enough for small or moderate tasks, especially when code processes every item or looks items up by a known key.

An array is a better choice when the data has a predictable size, when code needs direct control over dimensions, or when calculations benefit from working with a block of values. Arrays can be resized in some circumstances, but they require more attention to bounds and resizing. Collections are convenient for incremental additions, while arrays are often natural for data read from or written to worksheet ranges in bulk.

A Dictionary is preferable when code needs explicit key-value storage, a way to test whether a key exists, or access to the full set of keys. The late-bound form commonly used in Windows VBA is created with CreateObject("Scripting.Dictionary"), but that Windows COM component is not a universally portable choice, particularly for Mac workbooks. The built-in Collection is generally simpler to carry between VBA environments, although Excel for the web does not run VBA macros at all. A Collection also does not offer the Dictionary’s key-management features, so the choice depends on the operations required rather than on one structure being universally better.

Common errors and practical safeguards

Several predictable mistakes account for many Collection errors. A careful check of the object’s state and the lookup method usually makes the cause clear:

Using position 0: Collection positions start at 1. A lookup outside the valid range raises an error.
Using a missing key: Requesting an item by a key that was never added raises an error. Since there is no built-in Exists check, code that cannot guarantee the key should handle the lookup failure deliberately.
Adding a duplicate key: Keys must be unique. Check how the macro creates keys before adding, or use a Dictionary if duplicate detection and key membership checks are central requirements.
Confusing values and objects: Use Set when assigning an object retrieved from a Collection. Ordinary values such as strings do not use Set.
Assuming positions remain fixed: Inserting or removing items changes the positions of other items. Prefer keys when an item needs a stable lookup label.

For expected missing-key situations, use a narrowly scoped error handler rather than suppressing every error across a large procedure. Broad use of On Error Resume Next can conceal unrelated problems. If an error handler is used, check that it responds only to the expected lookup failure and restore normal error handling afterward.

Using Collections in Excel safely

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, use Insert > Module to add a standard module, then place a procedure there. Excel for Mac also supports VBA, though some editor details and operating-system integrations differ; Excel for the web cannot create or run VBA macros.

A Collection exists only while the VBA code retains it in memory. If the results must remain available after the macro finishes, write them to worksheet cells, a file, or another persistent store. A Collection variable declared inside a procedure normally goes out of scope when that procedure ends. Passing it to another procedure or storing it in a longer-lived module-level variable can keep it available during the relevant macro work, but does not make it permanent after Excel closes.

Save the workbook as a macro-enabled workbook, typically .xlsm, if you need to keep VBA code with it. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z, even if a macro changes worksheet data. On Mac, the corresponding undo limitation applies as well. Enable macros only in files from sources you trust, since macros can perform actions beyond manipulating a Collection.

Frequently Asked Questions

Is a VBA Collection the same as an Excel worksheet collection?
No. New Collection creates the general-purpose VBA object described here. Excel also exposes object-model collections such as Worksheets and Workbooks, which represent specific Excel objects and have their own properties and methods.

Does a VBA Collection start at 0 or 1?
It starts at 1. The first item is items(1), and the last is items(items.Count), provided the collection contains at least one item.

Can a Collection contain worksheets or other objects?
Yes. A Collection can hold object references as well as values. Retrieve an object into a variable of the appropriate type with Set, and remember that the Collection holds a reference rather than a copy of the worksheet.

Can I check whether a Collection key exists?
There is no built-in Exists method or key list for the VBA Collection. You can handle a lookup error carefully, keep track of keys separately, or use a Dictionary when membership checks and key enumeration are required.

Can Excel for the web run a VBA Collection macro?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run VBA, and account for platform-specific differences if the macro uses operating-system features beyond the built-in Collection.

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.