VBA Dictionary vs Collection: Which Should You Use?

Use a VBA Dictionary when you need fast lookups by unique key, an Exists check, or easy access to all keys and values. Use a Collection when you mainly need an ordered, one-based list of items and want a built-in VBA type. The right choice depends on how you access and update your data, and whether your macro must run on a Mac.

The Core Difference

Both objects hold groups of values, and both can hold objects as well as ordinary values. Their main difference is how you identify and retrieve an entry. A Dictionary pairs each unique key with an item; a Collection is primarily an ordered sequence, with optional keys for named lookups.

Dictionary: retrieve an item by its key, check whether a key exists, and inspect the keys or items.
Collection: retrieve an item by its one-based position or by an optional key, and loop through items in sequence.

For example, if a macro needs to find the sales total for the customer ID C104, a Dictionary is a natural fit. If it needs to walk through a list of worksheet names in order, a Collection is often simpler. Neither is automatically better: the access pattern matters more than the label.

Availability and Background

Collection is a built-in VBA object. You can create one with New Collection without adding a reference. A Dictionary usually means the Scripting.Dictionary object, which is supplied through Windows scripting components rather than being a core VBA collection type.

In Excel for Microsoft 365 on Windows, a Dictionary can be created with late binding using CreateObject("Scripting.Dictionary"), or with early binding by enabling the Microsoft Scripting Runtime reference in the VBA editor. Late binding avoids a project reference and can make a workbook easier to move between Windows computers. Early binding provides type information and editor-assisted completion, but the reference must be available on the computer running the code.

Mac support is a significant consideration. The Windows COM-based Scripting.Dictionary is not a dependable cross-platform choice, and attempts to create it with CreateObject can fail on Mac. If the same workbook must run on both Windows and Mac, a Collection or a separately designed cross-platform data structure is generally safer. Test deployment on the actual target versions rather than assuming that a Windows macro will behave identically on Mac.

How a Dictionary Works

A Dictionary stores key-item pairs. Each key must be unique, and a lookup uses the key rather than a position. Its useful members include Add, Exists, Keys, Items, Remove, RemoveAll, and Count. The Keys and Items properties return arrays, which can be convenient when you need to transfer or inspect the contents.

Keys are compared according to CompareMode. The default is binary comparison, so differently cased text can be treated as distinct keys. Set the mode before adding entries; changing it after adding keys can raise an error. For case-insensitive text keys, set it to vbTextCompare first. This setting is useful when user-entered names may vary in capitalization.

One easily missed behavior is that reading an absent Dictionary key through its Item property can create that key with an empty item. Therefore, check Exists before retrieving a key when absence matters. This is different from the usual expectation that a failed lookup simply returns a harmless blank value.

How a Collection Works

A Collection keeps items in a one-based sequence. You can add an item at the end, or use the optional Before or After argument to insert it relative to another position or key. Items can be retrieved by position, such as items(1), or by an optional string key supplied when adding them.

Collection keys are optional, but any key you do supply must be unique. A duplicate key raises an error. Collections do not provide an Exists method, a Keys property, or a configurable comparison mode. Attempting to retrieve a missing key raises an error, so code that performs uncertain lookups needs an error-handling strategy or a different structure.

Collections are useful for straightforward ordered lists, but they are less convenient when the program needs to ask questions such as “Is this customer already present?” or “What are all the keys?” You can search a Collection manually, but that means writing and maintaining the search loop yourself.

Small VBA Examples

This Windows example uses late binding, sets case-insensitive comparison before adding a key, and checks for a key before reading it. The key variable is declared as a Variant because Dictionary keys can be enumerated as Variants.

Sub ExampleDictionary()
    Dim dict As Object
    Dim key As Variant

    ' Create a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    dict.Add "North", 12
    If dict.Exists("north") Then
        Debug.Print dict("north")
    End If

    For Each key In dict.Keys
        Debug.Print CStr(key), dict(key)
    Next key
End Sub

The result printed for the lookup is 12, because vbTextCompare makes the lookup for north match the key North. If the key were not present, the If block would not read it and accidentally add it.

This Collection example stores two values and gives each an optional key. The loop follows the sequence of items.

Sub ExampleCollection()
    Dim items As Collection
    Dim item As Variant

    ' Create a built-in VBA Collection.
    Set items = New Collection
    items.Add "red", "color"
    items.Add "blue", "sky"

    Debug.Print items("color")
    For Each item In items
        Debug.Print item
    Next item
End Sub

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. On Mac, the equivalent undo shortcut is Command+Z, but it does not undo a macro either.

How to Choose

Choose based on the operations your code needs, not on which object seems more sophisticated. These questions usually settle the decision:

Do you need lookup by a unique identifier? Prefer a Dictionary, especially when you will perform repeated lookups.
Do you need to test for membership? Dictionary’s Exists method makes this direct and readable.
Is sequence or position central? A Collection offers simple one-based indexing and ordered traversal.
Do you need to insert relative to an existing position? Collection supports Before and After when adding an item.
Must the workbook run on Windows and Mac? Avoid relying on the Windows scripting object unless you have confirmed its availability in the target environment.
Do duplicate values matter? Both structures can contain repeated item values. Dictionary keys must be unique; Collection keys, when supplied, must also be unique, but items can repeat.

A frequent pattern is to use a Dictionary for aggregation. As a macro reads rows, it can use a product code as the key and update that product’s running total. For a small fixed sequence of steps or labels where position is the useful identifier, a Collection is often clearer. If you need both key-based lookup and a guaranteed output order, keep an explicit ordered list as well as the Dictionary rather than relying on Dictionary enumeration order.

Common Problems and Misconceptions

Assuming a Dictionary lookup is read-only: Accessing a missing key with the Item property can add it. Use Exists first when a missing entry should remain missing.

Setting CompareMode too late: Assign CompareMode immediately after creating the Dictionary and before its first key is added. If the code needs a different case rule, decide it at initialization.

Expecting a Collection to have an Exists method: It does not. You can loop through the items or handle lookup errors, but repeated membership checks are usually more straightforward with a Dictionary.

Assuming Dictionary order is a contract: A Dictionary is designed around keys, not a promised display order. Even if enumeration appears consistent in a particular setup, sort the keys or maintain a separate sequence when order is part of the requirement.

Confusing a key with an item: In a Dictionary, the key identifies the entry and the item is its associated value. In a Collection, the item is central and a key is optional. Plan what each variable represents before writing lookup code.

Using object values like simple values: Both containers can hold object references, but retrieving or assigning an object may require VBA’s Set statement. Keep the object variable declared with an appropriate object type, and distinguish an object reference from a string or number stored as a value.

Performance and Practical Limits

Dictionary lookups are generally a better fit for repeated key searches in larger groups than scanning a Collection from beginning to end. A Dictionary is built for key-based access; a manual Collection search must inspect items until it finds a match or reaches the end. The exact speed difference depends on the data, surrounding code, and how often the lookup occurs, so avoid treating a particular timing as universal.

For a few dozen values or a one-time loop, the difference may be irrelevant. Readability, portability, and correct behavior are usually more important than optimizing a tiny data structure. If performance is genuinely important, measure the real macro with representative data and avoid repeatedly reading cells one at a time; worksheet interaction can dominate the cost of the lookup structure.

Alternatives to Consider

Arrays are a good option when data is naturally positional and you want compact storage or predictable traversal. They do not provide Dictionary-style keyed lookup by themselves, so code must find an index or maintain a mapping. A worksheet range or Excel table can be more appropriate when the data needs to remain visible and editable to workbook users.

For a cross-platform macro that requires keyed access, possible approaches include implementing a small lookup routine over an array, using a custom class, or using a worksheet table as the data store. Each adds trade-offs in complexity, speed, or workbook design. A Collection is often the simplest cross-platform built-in choice when its ordered-list behavior is sufficient, but it does not become a Dictionary merely because you add keys to some entries.

Frequently Asked Questions

Is a VBA Dictionary faster than a Collection?
A Dictionary is generally preferable for repeated lookups by key, while a Collection search by value or key may require scanning entries. For small lists, the practical difference can be negligible; measure if speed is a real concern.

Can a VBA Collection have duplicate keys?
No. Collection keys must be unique when provided, and adding a duplicate key raises an error. You may omit keys, and repeated item values are allowed.

Does a Dictionary keep items in insertion order?
Do not rely on that as a guaranteed ordering feature. If output order matters, sort the keys or store the required order separately.

Can I use Scripting.Dictionary on Excel for Mac?
Do not assume it will work. It relies on a Windows scripting component, and its availability through CreateObject is not dependable across Mac installations. Use a cross-platform approach or test on the exact target environment.

When should I use a Collection instead of an array?
Use a Collection when you want a resizable sequence that is easy to add items to and iterate through. Use an array when direct indexed storage, compact handling, or efficient bulk operations better fit the task.

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.