Microsoft Excel VBA Dictionary: How to Use It

A Microsoft Excel VBA Dictionary is a key-and-value collection that lets a macro look up, count, and update information by a unique key. In VBA, you can create one on Windows with CreateObject("Scripting.Dictionary"), add entries with .Add, check for a key with .Exists, and retrieve its value using the key.

What a VBA Dictionary Is

A dictionary stores pairs: a key that identifies an entry and an item that holds its associated value. For example, a product code can be the key and its description the item, or a department name can be the key and a sales total the item. Unlike a worksheet range, a dictionary is an in-memory VBA object, so a macro can use it to organize data while it runs without repeatedly searching cells.

Each key must be unique within a dictionary. Items do not have to be unique: several different keys can point to the same item value. A dictionary is useful when the key is meaningful, such as an order number or customer ID, and you need to find the related value quickly or determine whether that key has already been encountered.

The object is commonly called Scripting.Dictionary and is provided through the Microsoft Scripting Runtime on Windows. It is not an Excel worksheet function. You use it in VBA code, typically in a macro or a procedure called by another macro.

How to Create and Set Up a Dictionary

There are two common ways to make the object available to VBA. The choice affects portability and whether the VBA editor can recognize the object while you write code:

Late binding: declare the variable as Object and create it with CreateObject("Scripting.Dictionary"). This avoids setting a reference in the workbook and is often convenient when sharing a macro among Windows users.
Early binding: in the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. The editor can offer more information and autocomplete, but each computer opening the project needs access to the referenced library.

For a simple example, the following macro reads labels from column A, counts them, and writes each distinct label and its count to columns C and D. It treats capitalization as insignificant, so “North” and “NORTH” are counted together. It skips blank cells and uses the first spelling encountered as the displayed key.

Assumptions: labels are in A2:A through the last used cell in column A, and columns C:D are available for the output. This macro clears neither output nor source data, but it writes over existing cells in C:D, so check those columns first. Save a copy of the workbook before running any macro; VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Sub CountLabelsWithDictionary()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim label As String
    Dim dict As Object
    Dim key As Variant
    Dim outRow As Long

    ' Use the active sheet; change this if a specific sheet is required.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    ' Late binding avoids a Tools > References requirement on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    For r = 2 To lastRow
        label = Trim$(CStr(ws.Cells(r, "A").Value2))
        If Len(label) > 0 Then
            If dict.Exists(label) Then
                dict(label) = dict(label) + 1
            Else
                dict.Add label, 1
            End If
        End If
    Next r

    ws.Cells(1, "C").Value = "Label"
    ws.Cells(1, "D").Value = "Count"
    outRow = 2

    For Each key In dict.Keys
        ws.Cells(outRow, "C").Value = key
        ws.Cells(outRow, "D").Value = dict(key)
        outRow = outRow + 1
    Next key
End Sub

To run it in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, and run the procedure from the editor or Excel’s Macro dialog. On a Mac, the VBA editor shortcut differs by keyboard and Excel settings; use Tools > Macro > Visual Basic Editor if the shortcut is unavailable. The code above relies on the Windows Scripting Runtime and should not be assumed to work on Mac, as explained below.

Adding, Finding, and Updating Entries

The main methods are straightforward. .Add key, item creates a new pair, .Exists(key) returns True or False, and dict(key) returns or sets the item for that key. The .Keys and .Items properties provide arrays of the stored keys and values, while .Count reports the number of pairs.

Use .Exists before adding a key if duplicates are possible. Calling .Add with a key already present raises an error rather than silently replacing the old item. If the intention is to update an existing value or add it when absent, use a pattern like the counting macro: test with .Exists, then either change dict(key) or call .Add. This makes the desired duplicate behavior explicit.

A dictionary can hold different kinds of items, including numbers, text, dates, and object references. In practice, consistent item types make code easier to maintain. If an item is an object rather than a value, VBA generally requires Set when assigning or retrieving that object. Keys should also be chosen carefully: a customer’s display name may not be unique, whereas a customer ID usually is.

Case Sensitivity and CompareMode

By default, a dictionary compares text keys using binary comparison, which distinguishes uppercase and lowercase letters. Thus “ABC” and “abc” can be separate keys. To make text comparisons case-insensitive, set CompareMode to vbTextCompare, as the example does.

Set CompareMode before adding the first key. Changing it after the dictionary contains data can raise an error. The comparison mode governs how keys are compared; it does not automatically remove spaces, standardize punctuation, or make different spellings equivalent. If “North ” and “North” should count as the same label, trim the value before using it as a key. If IDs must preserve case, leave the default comparison mode in place.

When a Dictionary Is the Right Tool

A dictionary is especially helpful when a macro needs a fast membership check, a distinct list, a count for each category, or a value associated with a unique identifier. It can replace repeated searches through a growing list: as a macro reads each record, it checks whether the key is already in the dictionary and then acts accordingly. This is useful for reconciling two lists, flagging repeated invoice numbers, or accumulating totals by product.

Use a dictionary to map a unique code to a description or value.
Use it to count occurrences or maintain running totals by category.
Use .Exists to test whether an ID has appeared before.
Consider a worksheet formula, PivotTable, or Power Query instead when the result should be visible, refreshable, or maintained without VBA.

A dictionary is not automatically better just because it can be fast. For a small list, a worksheet lookup or a simple loop may be clearer. A dictionary also keeps its contents in memory; it does not create a permanent data table. If you need to keep results after the macro finishes, write them to cells, a file, or another persistent data store.

Dictionary Compared with Collection and Worksheet Tools

A VBA Collection can also hold items and retrieve them using keys, but it does not provide the same built-in .Exists method. That can make duplicate checks more cumbersome. A dictionary is generally a natural fit when the code needs frequent key lookups or needs to know whether a key is present. A Collection can be suitable for an ordered group of items when its simpler behavior fits the task.

Worksheet tools are often a better choice when people need to inspect or edit the data. XLOOKUP, COUNTIF, PivotTables, and Power Query solve related problems in different ways: formulas recalculate in the sheet, PivotTables summarize data interactively, and Power Query transforms and combines data through a refreshable process. A VBA dictionary is better understood as a programming structure for a macro, not a replacement for every lookup or summary feature in Excel.

Iteration order should not be treated as a sorting feature. Do not rely on a dictionary returning entries in alphabetical, numeric, or otherwise meaningful order. If output order matters, sort the keys or sort the worksheet results after writing them.

Windows, Mac, and Compatibility

The Microsoft Scripting Runtime is a Windows COM component. Late binding with CreateObject("Scripting.Dictionary") removes the need to select a reference in the VBA project, but it does not make that Windows component available on every operating system. Excel for the web does not run VBA macros, either; users can view a workbook there, but need desktop Excel to execute VBA.

On Mac, Excel supports VBA, but Windows COM automation objects such as Scripting.Dictionary are not generally available in the same way. A macro built around this object may fail when it reaches CreateObject. If a workbook must run on both Windows and Mac, test the exact macro on both platforms and consider an alternative that does not depend on that component. Options include a VBA Collection with suitable error handling, arrays and loops, worksheet-based processing, or a cross-platform dictionary class designed for VBA. Such alternatives have different behaviors and may require adding or maintaining code.

Early binding has an additional sharing consideration: if a required reference is missing on another computer, VBA can mark it as unavailable and code using that library may fail to compile. Late binding can avoid that particular reference problem on Windows, although it cannot solve the Mac COM limitation.

Common Problems and Safer Practices

Several predictable issues account for many dictionary errors. Check the key, comparison rules, and object setup before assuming the dictionary has lost data:

Duplicate-key error: check .Exists before calling .Add, or deliberately update the existing item.
Wrong capitalization behavior: set CompareMode before adding keys, and decide whether text should be case-sensitive.
Unexpected near-duplicates: normalize input consistently, for example by trimming spaces or standardizing case when that matches the data rules.
Object not available: confirm the Windows environment and, for early binding, check Tools > References for a missing library.
Wrong output order: sort the results separately rather than relying on the order of dictionary keys.
Data overwritten by a macro: inspect the worksheet destinations and save a backup before running code.

Also consider how empty cells, errors, and mixed data types should be handled. Converting a cell to text with CStr can be suitable for labels, but a cell containing an Excel error value may need an IsError check before conversion. Avoid using a display label as a key if different underlying values can display identically. Clear rules for cleaning and identifying data are as important as the dictionary itself.

Frequently Asked Questions

How do I add a key to a VBA Dictionary?
Call dict.Add key, item after creating the dictionary. If the key may already exist, first use dict.Exists(key) and choose whether to update the item or leave it unchanged.

How do I check whether a dictionary contains a key?
Use dict.Exists(key). It returns True when the key is present and False when it is not, which is useful before adding a possibly repeated key.

Is a VBA Dictionary case-sensitive?
By default, text keys use binary comparison, so uppercase and lowercase can be treated as different. Set dict.CompareMode = vbTextCompare before adding entries if text keys should be compared without regard to case.

Why does Scripting.Dictionary fail on Mac?
Scripting.Dictionary depends on a Windows COM component that is not generally available to Mac Excel VBA. Use and test a cross-platform alternative, such as a Collection or an appropriate VBA dictionary class, if the workbook must run on both systems.

Can I use a VBA Dictionary in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel to run its VBA code; for browser-based automation, consider Office Scripts where available.

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.