Excel VBA Collection vs Dictionary: Which Should You Use?

Use a VBA Collection when you need an ordered list of items, optionally addressed by unique string keys; use a Scripting.Dictionary when you need unique keys, fast key-based lookup, and methods such as Exists. For most Windows Excel macros that repeatedly look up records by ID, a dictionary is the more convenient choice; for a simple ordered group or better portability, a collection may fit better.

What the two VBA objects are

Both objects hold multiple values in memory while a macro runs, but they organize those values differently. A Collection is a built-in VBA object. It can hold items in sequence and lets you refer to an item by its one-based position or, if you supply one, a string key. A Scripting.Dictionary is a key-and-item object supplied by the Microsoft Scripting Runtime on Windows. Each dictionary entry has a key, and the key must be unique.

Neither object is an Excel worksheet table or a permanent data store. The values remain in memory unless your code writes them to a worksheet, file, or another storage system. Both can hold different kinds of values, including strings, numbers, and object references. This makes them useful for temporary indexes, grouped results, de-duplication, and other tasks where repeatedly scanning worksheet cells would be awkward.

How a Collection works

A collection preserves the sequence in which items are added. You can loop through it with For Each, or retrieve an item by its one-based position. The optional key is a string, and it must be unique within that collection. Omitting the key is allowed, so a collection can be used as a straightforward list.

For example, this macro creates two keyed entries and retrieves one by its key:

Sub ShowCollectionExample()
    Dim records As Collection
    Dim itemName As Variant

    Set records = New Collection
    records.Add Item:="North", Key:="A104"
    records.Add Item:="South", Key:="B208"

    itemName = records("A104")
    Debug.Print itemName
End Sub

The result printed is North. A collection does not offer a built-in Exists method. Trying to retrieve a missing key raises a runtime error, so code that probes for a key needs error handling or another way to track membership. Adding an item with a duplicate key also raises an error. These behaviors are manageable for small, predictable lists, but they can make a collection less convenient as a lookup table.

How a Dictionary works

A dictionary pairs each key with an item. The Exists method checks whether a key is present before your code reads or changes its value. You can also request arrays of keys or items, remove an entry, clear all entries, and check the number of entries with Count. These methods make dictionaries particularly useful when worksheet rows have IDs that may be encountered more than once.

This late-bound example works in Windows Excel without selecting a reference in the VBA editor:

Sub ShowDictionaryExample()
    Dim records As Object
    Dim itemName As Variant

    Set records = CreateObject("Scripting.Dictionary")
    records.Add "A104", "North"
    records.Add "B208", "South"

    If records.Exists("A104") Then
        itemName = records("A104")
        Debug.Print itemName
    End If
End Sub

It prints North. Late binding declares the variable as Object and creates the dictionary at run time. Early binding instead declares a Scripting.Dictionary variable and requires the Microsoft Scripting Runtime reference in Tools > References in the VBA editor. Early binding provides clearer type information and editor assistance; late binding avoids requiring that reference to be set in each development environment. The object still needs to be available on the computer where the macro runs.

Key differences at a glance

Order: A collection is designed as a sequence and supports retrieval by position. Do not make a dictionary’s enumeration order part of your program’s logic; use a separate ordered list if order matters.
Keys: A collection’s key is optional and must be a unique string when supplied. A dictionary requires a key for each entry, and its keys must be unique.
Membership checks: Dictionaries have Exists. Collections do not, so a missing-key check requires extra code.
Duplicate keys: Both reject adding an already-used key. A dictionary can instead update the value associated with an existing key by assigning to its item.
Changing values: A dictionary is designed for reading and replacing items by key. A collection has no direct general-purpose method for replacing a stored scalar item; code often removes and re-adds an entry or stores a mutable object.
Platform: The VBA Collection is built into VBA. Scripting.Dictionary is commonly available in Windows desktop Excel, but its Windows scripting dependency makes it a poor assumption for Mac or web-based use.

Dictionary keys are unique, but dictionary items do not have to be. For example, two different product codes can both map to the same category. A common misconception is that a dictionary automatically removes duplicate values; it only enforces uniqueness of keys.

Choosing based on the job

Choose a dictionary when your code needs to answer questions such as “Have I already seen this invoice number?” or “What value belongs to this customer ID?” The key should represent a stable identifier. A dictionary can make that logic concise and avoids writing a repeated loop to search all previously collected rows.

Choose a collection when you primarily need to retain an ordered sequence, when numeric positions are useful, or when your code should avoid relying on the Scripting Runtime. Collections are also convenient for accumulating items where no lookup by key is needed. If you need both ordered output and rapid keyed lookup, use both structures: keep the sequence in a collection or array and use a dictionary as an index.

Unique list of IDs: Use a dictionary and add a key only when Exists returns False.
Ordered steps or objects: Use a collection if the order of addition is meaningful and you mainly iterate through the items.
Grouping worksheet rows: Use a dictionary keyed by category, with each item holding a collection or other container of rows in that group.
Small fixed lookup: Either can work; choose the one that makes missing keys, duplicates, and ordering easiest to understand.

A dictionary is often described as faster for lookup than searching a collection one item at a time. That is a useful general expectation for large sets of keyed data, not a guarantee for every macro. Performance depends on data size, how the code accesses the items, and the surrounding worksheet operations. For a small list, readable code is usually more important than a speed difference. For a large macro, measure the complete task rather than assuming the container is the bottleneck.

Comparison settings and duplicate handling

String-key comparisons need deliberate attention. A newly created Scripting.Dictionary uses binary comparison by default, which treats differences in letter case as significant. Set CompareMode to vbTextCompare if keys such as "abc" and "ABC" should match. Set it before adding any entries; changing it after the dictionary contains data can raise an error. A collection’s string-key lookup is case-insensitive, so moving code between the two structures can change how keys are treated.

When repeated input keys are possible, choose a policy instead of letting an accidental duplicate crash the macro. You might keep the first value, replace it with the latest value, or record that the input contains a conflict. For a dictionary, check Exists before adding. For a collection, you need error handling or a separate index to test whether a key has already been used. Be especially careful when a key comes from worksheet text: leading or trailing spaces, inconsistent capitalization, and blank cells can create unexpected distinctions or collisions.

Dictionary item assignment can add a key that was not present, as well as replace an existing item. That convenience can also hide a typo in a key if you intended only to update existing data. Use Exists when the distinction between “update” and “create” matters. Adding an existing key with the dictionary’s Add method is different: it raises an error because that method requires a new key.

Windows, Mac, and Excel for the web

For Microsoft 365 Excel on Windows, Scripting.Dictionary is a common VBA choice. Late binding with CreateObject("Scripting.Dictionary") avoids a manual reference setting, while early binding can make code easier to inspect and maintain. If a macro is shared, document which approach it uses and test it on the target computers.

Mac compatibility deserves a separate check. The VBA Collection is a built-in language object, while Scripting.Dictionary relies on a Windows scripting component and should not be assumed to work in Excel for Mac. A macro intended for both platforms can use a collection, arrays, or a custom VBA class for a portable design, depending on its lookup needs. Excel for the web does not run VBA macros, so neither object makes a VBA solution usable in the browser; use a different automation approach if web execution is required.

Common errors and practical safeguards

A missing collection key is a frequent source of runtime errors because there is no native membership test. Avoid using broad error suppression around an entire procedure: it can conceal unrelated problems. If you choose a collection for keyed lookups, keep membership tracking explicit or handle only the narrow operation that may fail. With dictionaries, check Exists before reading a key that may not be present.

Remember that VBA macros cannot be undone with Ctrl+Z. Save a copy of the workbook before running a macro that changes worksheet data, even if the collection or dictionary itself is only temporary. Also consider these safeguards:

Decide whether keys are case-sensitive and normalize input consistently.
Check for empty or malformed IDs before adding them.
Use clear names for keys and items; a dictionary’s key and stored value serve different purposes.
When reading a worksheet into memory, validate the source range and write results back only after the transformation is complete where practical.
Test duplicate and missing-key cases, not just the expected clean data.

Frequently Asked Questions

Is a VBA Dictionary faster than a Collection?
A dictionary is generally a better fit for repeated lookup by key, particularly as the number of entries grows. A collection is useful for ordered iteration and position-based access. Actual speed depends on the task and data size, so benchmark a representative macro if performance is important.

Can a VBA Collection have keys?
Yes. The Collection.Add method accepts an optional string key, and you can retrieve an item using that key. Keys must be unique, and the collection has no built-in Exists method.

Does a Dictionary allow duplicate values?
Yes. It requires unique keys, not unique items. Multiple keys may point to the same value; adding a second entry with a key that is already present is what causes a duplicate-key error.

Should I use early binding or late binding?
Early binding uses a specific Scripting.Dictionary type and a reference to the Microsoft Scripting Runtime, providing editor assistance and clearer declarations. Late binding uses Object and CreateObject, so the reference need not be selected in the project. Both still depend on the scripting component being available.

Can I use Scripting.Dictionary in Excel for Mac or the web?
Do not assume it is available on Mac because it depends on a Windows scripting component; test on the target Mac version or choose a portable alternative. Excel for the web does not execute VBA, so VBA collections and dictionaries cannot run there.

Bottom line

For a Windows VBA macro that maps unique IDs to values and needs reliable membership checks, choose Scripting.Dictionary. For an ordered list, simple iteration, or a design that should avoid that Windows-specific dependency, choose Collection. If your code needs both order and keyed lookup, combining a sequence with a dictionary index is often clearer than forcing one object to do both jobs.

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.