VBA Dictionary: How It Works, Examples, and Common Uses

A VBA Dictionary is a key–value collection: you store a value under a unique key, then retrieve it quickly by that key. It is not a built-in VBA data type; in Excel for Microsoft 365 on Windows, it is usually created as a Scripting.Dictionary object from the Microsoft Scripting Runtime library.

What a VBA Dictionary Is

Think of a dictionary as a lookup table held in memory. A key identifies an entry, and its associated item holds the information you want to retrieve. For example, a product code can be a key and its description, price, or stock count can be the item. A dictionary is useful when you need to look up values repeatedly, count occurrences, or ensure that a list contains each key only once.

Unlike a worksheet range, a dictionary is not a visible grid and does not save itself in the workbook. It exists while the macro is running unless your code writes its contents somewhere, such as a worksheet or file. Its keys must be unique according to the dictionary’s comparison rules; its items do not have to be unique.

How to Create One in Excel VBA

There are two common ways to create a dictionary. Late binding uses CreateObject and does not require setting a reference in the VBA editor. Early binding uses the library’s specific type and offers more helpful editor support, but requires the reference to be available and enabled.

Late binding: declare the variable as Object and create it with CreateObject("Scripting.Dictionary"). This is often convenient when sharing a workbook between Windows computers with different reference settings.
Early binding: in the VBA editor, choose Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. The editor can then show the object’s members as you type.

Here is a small late-bound example. It counts words without treating uppercase and lowercase spellings as different keys:

Sub CountWords()
    Dim counts As Object
    Dim words As Variant
    Dim word As Variant

    ' Save a copy of the workbook before running a macro.
    Set counts = CreateObject("Scripting.Dictionary")
    counts.CompareMode = vbTextCompare
    words = Array("Excel", "VBA", "excel")

    For Each word In words
        If counts.Exists(CStr(word)) Then
            counts(CStr(word)) = CLng(counts(CStr(word))) + 1
        Else
            counts.Add CStr(word), 1
        End If
    Next word

    Debug.Print counts("EXCEL")  ' Prints 2
End Sub

The example writes its result to the Immediate window, which you can display in the VBA editor with Ctrl+G on Windows. On a Mac, use the VBA editor’s View > Immediate Window command if available in your Office version. The macro does not alter worksheet cells, but saving a copy before running macros is a sound precaution: changes made by VBA generally cannot be reversed with Ctrl+Z.

Keys, Items, and Everyday Operations

The central operations are adding an entry, checking for a key, retrieving or changing its item, and removing entries. With late binding, the variable is declared as Object; with early binding, use the Scripting.Dictionary type after enabling its reference.

Add key, item creates an entry. Adding a key that already exists raises an error.
Exists(key) returns True if that key is present. Check this before reading a key that might not exist.
Item(key), commonly written as dict(key), gets or sets the item associated with a key.
Remove key deletes one entry; RemoveAll clears the dictionary.
Count reports the number of entries. Keys and Items return arrays containing the keys and items.

For example, after creating an empty dictionary called prices, prices.Add "P100", 12.5 stores a price under the key P100. You can test prices.Exists("P100") before retrieving it. Assigning a new value to an existing key, such as prices("P100") = 13, updates the item rather than adding a second entry.

One important detail is that reading or assigning through the Item property is not the same as using Add. In the Scripting.Dictionary object, referencing an absent key through its Item property can create that key with an empty item. That can hide a spelling mistake or an unexpected lookup. Use Exists when absence is meaningful, and use Add when a duplicate should be treated as a problem.

Choosing Case-Sensitive or Case-Insensitive Keys

The CompareMode property controls how string keys are compared. Set it before adding entries. The common choices are vbBinaryCompare, which compares text using binary rules, and vbTextCompare, which compares text without distinguishing letter case. For example, with text comparison enabled, “Excel” and “EXCEL” refer to the same key.

Changing CompareMode after entries have been added is not supported and can cause a run-time error. Set the property immediately after creating the dictionary, before calling Add. If you need case-sensitive identifiers, retain the default binary comparison or set it explicitly. If your data has special requirements for accents, locale, or punctuation, test those cases rather than assuming that text comparison follows every rule your users expect.

Looping Through a Dictionary

A common pattern is to loop over the keys and retrieve each item. A For Each control variable should be declared as a Variant (or an appropriate object variable). For instance, if dict already contains entries, this pattern lets you inspect them:

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

    ' Save a copy before running a macro that changes workbook data.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 24
    dict.Add "South", 18

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

This example creates its own entries and prints them in the Immediate window. Do not rely on a particular ordering of dictionary entries when order matters to your task. If you need a defined order, sort the keys or write the results to a range and sort that range. The arrays returned by Keys and Items are also handy when transferring results to other VBA procedures.

Common Errors and How to Avoid Them

Many dictionary problems come from a mismatch between the data and the lookup rules, rather than from complicated VBA. Check these points when a macro fails or returns unexpected results:

Duplicate key error: Add cannot add a key that already exists. Check with Exists, or update the existing item instead.
Missing key: test with Exists before relying on a lookup. Consider whether blank cells, extra spaces, or inconsistent number formats produced a different key.
Wrong case behavior: set CompareMode before inserting keys and confirm whether case should matter.
Object creation failure: CreateObject("Scripting.Dictionary") depends on the Scripting Runtime COM component being available. A missing component or an unsupported platform can prevent creation.
Reference problems: early-bound code can show “Missing” in Tools > References on another computer. Late binding avoids that compile-time reference, but it does not remove the need for the underlying component.

When keys come from cells, decide whether they should be text or numbers and normalize them consistently. For example, the text key “0012” and the numeric value 12 are not necessarily equivalent identifiers. Trimming accidental spaces can also prevent two visually similar keys from being treated as different entries.

Dictionary vs. Collection, Arrays, and Worksheet Lookups

VBA’s built-in Collection is another way to group values, but it does not provide the same convenient Exists method or the same straightforward key-management pattern. A collection can be a good choice when you mainly need an ordered group of items, while a dictionary is often clearer for key-based retrieval and uniqueness checks.

Arrays suit data with a known layout, especially when you need to process many worksheet values in memory. They can be efficient, but looking up an item by a separate key generally requires additional code or a search. A dictionary gives that key-based relationship directly. For a simple lookup that should remain visible and editable in a workbook, a worksheet table and an Excel lookup formula may be a better fit than VBA.

Choose based on the job, not on the assumption that one structure is always faster or better. A dictionary is particularly useful when a macro needs repeated lookups, counts, or unique values during a run. A table is easier for workbook users to inspect and maintain; an array is a natural choice for indexed data; and a collection can be suitable for a straightforward group of objects.

Platform and Macro-Safety Considerations

Scripting.Dictionary is a Windows COM component commonly used by Excel VBA on Windows. It is not a dependable cross-platform choice for Excel for Mac, where Windows COM automation is unavailable. If a workbook must run on both Windows and Mac, check compatibility in the target Office versions and consider a built-in VBA Collection or another implementation that does not depend on this COM component. Excel for the web does not run VBA macros.

Only run macros from sources you trust, and save a separate copy before executing code that changes workbook contents. A dictionary itself does not modify cells, but the surrounding macro may write results, delete data, or perform other actions. VBA changes cannot normally be undone with Ctrl+Z on Windows; Mac has the same practical limitation for macro-driven changes.

Frequently Asked Questions

Is Dictionary a built-in VBA data type?
No. It is an object supplied by the Microsoft Scripting Runtime, rather than a native VBA type like String or Long. You can use late binding with an Object variable or early binding with the Scripting Runtime reference.

How do I check whether a key exists?
Call the dictionary’s Exists method, as in dict.Exists("P100"). It returns True or False and is safer than trying to read an item that may not be present.

Can a dictionary have duplicate keys?
No. Each key must be unique under the current comparison mode. You can update the item for an existing key, or use Exists to decide whether to add a new entry or handle a duplicate.

Does a VBA Dictionary work on Mac?
Do not assume it does. The Scripting Runtime is a Windows COM component, and that dependency makes Scripting.Dictionary unsuitable as a guaranteed cross-platform solution. Consider a VBA Collection or another Mac-compatible approach when portability is required.

What is the difference between early and late binding?
Early binding uses a declared Scripting.Dictionary type and a reference set in Tools > References. Late binding uses an Object variable and CreateObject, avoiding that reference setting but still requiring the component at run time.

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.