Create a Dictionary in VBA: Keys, Values, and Code Examples

To create a dictionary in VBA for Excel on Windows, declare an object and use CreateObject("Scripting.Dictionary"). Add entries as key–value pairs, then use methods such as Exists to check for a key and Item to retrieve its value. A dictionary is useful when you need fast, direct lookups or a collection of unique keys.

What a VBA dictionary does

A dictionary stores pairs: each key identifies an associated item, or value. For example, a product code can be the key and a product name the value. Unlike a worksheet table, the dictionary exists in memory while the macro runs; it does not automatically save its contents into cells or preserve them after the procedure ends.

Keys must be unique. If the same key is added twice using Add, VBA raises an error. Values do not have to be unique, so two different keys can point to the same value. You can also use a dictionary as a set by storing a placeholder value and caring mainly about whether each key exists.

Dictionaries are handy for tasks such as counting occurrences, removing duplicates, matching identifiers to descriptions, and checking whether a value has already been processed. They are particularly useful when repeatedly searching a list: instead of scanning the list again for every lookup, the code can check whether a key is present.

Create a dictionary on Windows

For a straightforward Excel for Microsoft 365 setup on Windows, use late binding. It creates the dictionary at runtime without requiring a reference to be selected in the VBA editor. Open the editor with Alt+F11, insert a standard module, and add a procedure like this. On a Mac, the editor shortcut is commonly Option+F11; some keyboards require holding Fn as well.

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

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

    ' Compare string keys without distinguishing uppercase and lowercase.
    dict.CompareMode = vbTextCompare

    ' Add key-value pairs. Keys must be unique.
    dict.Add "US", "United States"
    dict.Add "CA", "Canada"

    ' Look up a key safely before reading its value.
    If dict.Exists("ca") Then
        Debug.Print dict.Item("ca")
    End If

    ' Print every key and its associated value in the Immediate window.
    For Each key In dict.Keys
        Debug.Print key, dict.Item(key)
    Next key
End Sub

The lookup prints Canada because the example sets text comparison before adding entries. To see the output, open the Immediate window in the VBA editor with Ctrl+G on Windows. On Mac, use the editor’s View menu to show the Immediate window if that shortcut is unavailable.

Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm if you need to retain the VBA code; saving as .xlsx removes macros.

Late binding and early binding

Late binding, shown above, declares the variable as Object and creates it with CreateObject. It is convenient for sharing a Windows workbook among users because they do not have to set a project reference manually. The trade-off is that the editor cannot provide the same type-specific autocomplete and compile-time checking for dictionary members.

Early binding uses the dictionary’s declared type. In the Visual Basic Editor on Windows, choose Tools > References and select Microsoft Scripting Runtime. Then declare and create the object like this:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Early binding can make code easier to explore and maintain because the editor recognizes the object’s members. However, a workbook may fail to compile on another computer if the reference is missing or cannot be resolved. For a workbook distributed across Windows machines, late binding often reduces reference-related setup issues; choose based on how the file will be maintained and shared.

Adding, finding, changing, and removing entries

The main methods and properties cover most everyday dictionary work:

Add key, item inserts a new pair. It raises an error if that key already exists.
Exists(key) returns True or False, so you can check before retrieving a value.
Item(key) gets or sets the value associated with a key. Assigning an item for a key that is not present creates that key, so use Add when you specifically want duplicate keys to be rejected.
Count returns the number of entries.
Remove key removes one entry, while RemoveAll clears the dictionary.
Keys and Items return arrays of the current keys and values, respectively.

For example, a macro can test dict.Exists(productCode) before reading dict.Item(productCode). That avoids an error from trying to retrieve a missing key. If you intend to update an existing entry or insert a new one, assigning dict.Item(productCode) = productName is concise, but it does not flag a spelling mistake in the key as a duplicate-key error would.

Choosing key comparison and key design

String comparison is controlled by the dictionary’s CompareMode. The default binary comparison distinguishes uppercase and lowercase text, so keys such as "North" and "north" can be different. Setting CompareMode to vbTextCompare makes text-key comparisons case-insensitive. Set it before adding entries; changing it after the dictionary contains data can raise an error.

Think carefully about what makes a key unique in the data. If a customer number is unique, it may be a suitable key. A customer’s name may not be: two customers can share a name, or a name can change. If you need a combined key, construct it consistently and use a separator that cannot be confused with the data, or use a stable identifier instead. Also normalize data where appropriate—extra spaces or inconsistent punctuation can make values that look similar behave as different keys.

Using a dictionary with worksheet data

A common workflow is to read a range into an array, build a dictionary from the array, and then use the dictionary to check or retrieve results. This keeps repeated lookups in memory rather than repeatedly searching worksheet cells. For instance, a macro might use employee IDs as keys and department names as values, then look up each ID in a second list.

Decide how the macro should handle duplicate source keys before loading the data. You can test Exists and skip duplicates, update the existing value, or report the duplicate for review. Which behavior is correct depends on the meaning of the data; silently keeping the first or last value can conceal an input problem. For a duplicate count, store a number as the item, increment it when the key already exists, and initialize it when the key is new.

When transferring results back to a worksheet, remember that dictionary enumeration order should not be treated as a sorting rule. If output must be alphabetized or follow the order of the source data, sort the keys or maintain a separate ordered list. A dictionary is a lookup structure, not a formatted report or a replacement for a well-designed worksheet table.

Common errors and practical limits

A “key is already associated with an element” error commonly occurs when Add is called twice with the same key. Use Exists to decide whether to add, update, skip, or report the duplicate. A missing-key error usually means code tried to read an item without first confirming that the key exists. Check the key’s spelling, data type, spaces, and comparison mode as well.

Another frequent issue is setting CompareMode too late. Set it immediately after creating the dictionary and before its first entry. If a macro depends on a particular behavior, document that choice so a later edit does not accidentally change how keys match. Avoid assuming that numeric-looking text and numeric values are interchangeable keys; use a consistent type and conversion rule.

A dictionary is held in memory, so its contents disappear when the object is discarded or the macro ends. To preserve results, write them to a worksheet, a file, or another storage system. For modest tasks, a dictionary is often convenient; if the goal is simply to identify duplicates in a worksheet, built-in Excel features such as Data > Remove Duplicates may be more appropriate.

Windows, Mac, and alternatives

Scripting.Dictionary is a Windows scripting component and is not a dependable cross-platform choice for Excel VBA. On Excel for Mac, the Microsoft Scripting Runtime reference and Windows COM-based CreateObject approach may be unavailable. If a workbook must run on both Windows and Mac, test the actual target environments and consider a VBA Collection, a custom class or lookup routine, or a worksheet-based approach. Collections can store keyed items but do not offer the same built-in Exists method, so handling missing keys requires a different pattern.

For Microsoft 365 on Windows, both early- and late-bound dictionaries are reasonable. For Excel for the web, VBA macros do not run; use an alternative such as Office Scripts where its capabilities fit the task. Do not enable all macros just to run a dictionary example. Follow your organization’s macro policy and enable only files you trust.

Frequently Asked Questions

What is the VBA code to create a dictionary?

On Windows, declare an object with Dim dict As Object, then create it using Set dict = CreateObject("Scripting.Dictionary"). Add entries with dict.Add key, item. This is late binding and does not require selecting the Microsoft Scripting Runtime reference.

How do I check whether a key exists?

Call dict.Exists(key). It returns True if the key is present and False if it is not. Checking first is a safe way to decide whether to retrieve, add, or update an item.

How do I make dictionary keys case-insensitive?

Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any keys. With text comparison enabled, uppercase and lowercase versions of a text key match.

Why does VBA say the key already exists?

Add cannot insert a key that is already in the dictionary. Test with Exists first, or assign to Item(key) if replacing or creating the value is the intended behavior.

Can I use Scripting.Dictionary in Excel for Mac?

Do not assume it will work on Mac: it relies on a Windows scripting component and is not a portable VBA solution. For a workbook that must run on Mac, choose and test a cross-platform alternative such as a Collection or custom lookup code.

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.