VBA Dictionary: How to Create and Use One

Yes. In VBA, a dictionary is a key-value object that lets you store a value under a unique key and retrieve it quickly; in Excel for Microsoft 365 on Windows, you can create one with CreateObject("Scripting.Dictionary"). It is useful for tasks such as counting repeated values, checking whether an item has already appeared, and matching records without repeatedly scanning a worksheet.

What a VBA dictionary is

A dictionary keeps pairs of information: a key and its associated item, often called a value. For example, the key “North” might be associated with a sales total of 1250, or an employee ID might be associated with a name. Each key must be unique within that dictionary, while the values do not have to be unique.

This structure is part of the Microsoft Scripting Runtime on Windows. In VBA, the object is commonly referred to as a Scripting.Dictionary. Unlike a worksheet range, it lives in memory while the procedure runs, so it can be convenient for repeated lookups and intermediate calculations. It does not automatically read from or write to cells; your code has to transfer data between the worksheet and the dictionary.

A dictionary is not the same thing as a worksheet lookup formula. It is a programming tool for VBA procedures, not a function you enter in a cell. It also does not inherently sort its keys or keep worksheet rows in sync when the data changes.

Create a dictionary in VBA

The Windows example below uses late binding. That means the code creates the object by its programmatic name and does not require you to add a reference in the Visual Basic Editor. It reads names from column A of a worksheet named Data, starting at row 2, counts each nonblank name, and prints the counts in the Immediate window.

Option Explicit

Sub CountNamesInColumnA()
    Dim ws As Worksheet
    Dim counts As Object
    Dim lastRow As Long
    Dim r As Long
    Dim nameText As String
    Dim key As Variant

    ' Read names from the Data sheet; row 1 is a heading.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set counts = CreateObject("Scripting.Dictionary")

    ' Treat text keys such as "Alex" and "ALEX" as equivalent.
    counts.CompareMode = vbTextCompare

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        If Not IsError(ws.Cells(r, "A").Value) Then
            nameText = Trim$(CStr(ws.Cells(r, "A").Value))

            If Len(nameText) > 0 Then
                If counts.Exists(nameText) Then
                    counts(nameText) = CLng(counts(nameText)) + 1
                Else
                    counts.Add nameText, 1&
                End If
            End If
        End If
    Next r

    ' Display each key and its count in the Immediate window.
    For Each key In counts.Keys
        Debug.Print CStr(key) & ": " & CStr(counts(key))
    Next key
End Sub

Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. Change the sheet name or input column in the example if your data is arranged differently. Open the Visual Basic Editor and use View > Immediate Window to see the printed results. If the Data sheet has Alex, Sam, and alex in its rows, the case-insensitive setting groups Alex and alex together, producing a count of 2 for that key and 1 for Sam. The displayed spelling comes from the first matching key encountered.

Late binding and early binding

Late binding, shown above, declares the variable as Object and calls CreateObject. It avoids setting a project reference and is often convenient when sharing a Windows workbook among users with different Office installations. The trade-off is that the editor offers less help with dictionary-specific properties and methods while you write the code.

With early binding, you declare a variable as Scripting.Dictionary and add a reference in the Visual Basic Editor using Tools > References, then select Microsoft Scripting Runtime. Early binding can provide code completion and catch some typing mistakes earlier. The reference must be available to the workbook on the computer where the code runs; a missing reference can cause a “MISSING” entry in the References list and prevent code from compiling. For a workbook shared across different environments, late binding can reduce that particular dependency.

Both approaches use the same dictionary concepts. Do not mix an early-bound declaration with a project that lacks the required reference. On Excel for Mac, VBA is available, but the Windows COM-based Scripting.Dictionary object is not a dependable cross-platform choice. If the workbook must run on Mac, use a Mac-compatible alternative such as a Collection, an array-based approach, or a separately implemented dictionary class, and test the code in the target Excel version.

Core methods and properties

The most useful dictionary members cover adding, checking, retrieving, and removing entries. Knowing how they behave helps prevent subtle errors:

Add key, item inserts a new pair. It raises an error if that key already exists.
Exists(key) returns True if the key is present and False otherwise. Use it before retrieving a key when absence is possible.
Item(key), usually written as dict(key), gets or sets the value associated with a key.
Count returns the number of key-item pairs.
Keys and Items return arrays containing the keys and values, respectively.
Remove key removes one pair; RemoveAll clears the dictionary.

There is an important behavior to remember: reading the Item property for a key that does not exist can create that key with an empty value. Therefore, code that needs to distinguish “not found” from “found with an empty value” should call Exists first. The counting example does this, so it increments an existing count and adds a new key only when necessary.

For existing keys, assigning a new item through dict(key) = value replaces the prior value. If the key is absent, that assignment can create it. In contrast, Add is specifically for adding a new pair and will report a duplicate-key error. Choose the behavior intentionally: use Add when duplicates indicate a problem, or assignment when updating or creating is acceptable.

Choosing key comparison rules

By default, a dictionary uses binary comparison for string keys, so capitalization can matter: “Blue” and “blue” can be distinct keys. Set the CompareMode property to vbTextCompare if you want text keys such as those to be treated as equivalent. Set it before adding the first key. Changing the comparison mode after entries have been added can raise an error.

Text comparison is useful for names, categories, and codes that should not differ solely because of letter case. It can also cause separate source values to be combined, so decide whether that matches the meaning of your data. If letter case is meaningful—for example, in a case-sensitive identifier—keep binary comparison. Trimming spaces, as the example does, is a separate choice: comparison mode does not automatically remove leading or trailing spaces. Also, VBA’s Trim$ removes ordinary spaces; imported data may contain nonbreaking spaces or other characters that need separate cleaning.

Common dictionary tasks and pitfalls

Dictionaries are especially helpful when a procedure must test membership or maintain a running result. Typical uses include counting occurrences, removing duplicate records from a list, mapping IDs to descriptions, and building a lookup table before processing another range. A dictionary is most useful when the code repeatedly needs to ask whether a key exists or retrieve its associated value.

Keep these practical details in mind when adapting the sample:

Check the input range. The example assumes row 1 is a header and column A contains names. A missing Data worksheet causes an error, and a different layout requires changing the references.
Handle errors and blanks. The sample skips cell errors and blank strings. Decide what to do with numeric values, formulas returning an empty string, and cells containing only unusual whitespace.
Expect duplicate keys. Calling Add twice for the same key raises an error. Use Exists or intentional assignment to handle repeats.
Do not assume a sorted result. The order from Keys is not a sorting guarantee. If the output needs alphabetical or numeric order, sort the keys separately.
Use the right data type for objects. When storing object references as items, VBA generally requires Set when assigning or retrieving the object. Ordinary numbers and text are assigned without Set.

The example prints results rather than writing them into the workbook, which avoids overwriting worksheet cells. To create a report, you can write the keys and values to a dedicated output range, but choose that range deliberately and decide whether old results should be cleared first. For large ranges, reading the worksheet data into a VBA array and processing the array can reduce worksheet access overhead; a dictionary can still hold the counts or lookup results.

Dictionary compared with Collection and arrays

A VBA Collection is a built-in container that can hold items and optionally associate them with string keys. It can be a suitable alternative when you want simple keyed storage and want to avoid a Windows-specific scripting component. However, it does not provide the same convenient Exists method, so checking for a key often requires error handling or a separate lookup strategy. Updating keyed items is also less direct than assigning a dictionary item’s value.

Arrays are often a better fit when data is a fixed sequence of values that will be processed in order. They can be efficient for bulk work, but they do not provide the same built-in key-to-value lookup behavior. A worksheet range is preferable when the data should remain visible, editable, and available to formulas. The right option depends on whether the main need is ordered data, visible tabular data, or fast key-based lookup.

Platform and macro safety

The code using CreateObject("Scripting.Dictionary") is intended for Excel for Microsoft 365 on Windows. Excel for the web cannot run VBA macros. Excel for Mac can run many VBA procedures, but Windows COM automation and some references do not transfer directly; test cross-platform workbooks on each platform and avoid assuming this object will be available there.

Only run macros from sources you trust. A macro can change or delete workbook data, and enabling macros for an untrusted file can expose you to harmful code. Keep a separate copy before running a procedure that writes to cells or changes workbook structure. Macro security settings are controlled by Excel and organizational policy; do not weaken them just to run an unfamiliar file.

Frequently Asked Questions

Do I need to add a reference to use a VBA dictionary?
Not for the late-binding Windows example: it uses As Object and CreateObject. Early binding requires selecting Microsoft Scripting Runtime in Tools > References in the Visual Basic Editor.

How do I check whether a dictionary contains a key?
Call dict.Exists(key). It returns True when the key is present, and lets you avoid accidentally retrieving a missing Item that may create an empty entry.

How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare before adding any entries. This makes text comparison case-insensitive; it does not remove spaces or otherwise normalize the key.

Can a dictionary contain duplicate keys?
No. Each key must be unique. Adding an existing key with Add raises an error, while assigning a value to an existing key replaces its item.

Will Scripting.Dictionary work in Excel for Mac?
It is a Windows COM-based object and is not a reliable cross-platform choice for Mac VBA. For a workbook that must run on Mac, consider a Collection, arrays, or a compatible custom implementation and test it on the intended platform.

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.