VBA Coding Dictionary: How the Dictionary Object Works

VBA & macros · Updated November 18, 2026

A VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look up a value by a unique key or quickly check whether that key exists. In Excel for Microsoft 365 on Windows, you can create one with CreateObject("Scripting.Dictionary") and use methods such as Add, Exists, and Remove.

What “VBA coding dictionary” means

The phrase can refer either to a dictionary object used in VBA code or to a reference that explains VBA terms and syntax. Most Excel questions about a “VBA dictionary” mean the object: a convenient in-memory structure for connecting a key—such as a product code—to a value—such as a description or count. This article focuses on that object and also points to places to look up VBA language details.

Think of a dictionary as a set of labeled drawers. A key identifies a drawer, and the item stored in it is the value. For example, the key "P-104" could refer to the value "Desk lamp". Unlike a numbered list, you request an item using its key rather than its position. Each key must be unique within a dictionary, although values can repeat.

This makes dictionaries useful when a macro needs to match records, count repeated entries, remove duplicates, or build a fast lookup table. They are especially handy when the same lookup would otherwise require repeatedly scanning a large range of worksheet cells.

How a Dictionary works

A Dictionary holds data in memory while the VBA procedure runs. It does not automatically create a worksheet table or save its contents into the workbook. Your code must read data into it and, if you want a lasting result, write the values back to a worksheet or another storage location.

Keys are unique. Adding a key that already exists with Add raises an error, so check with Exists first when duplicates are possible. An item can be a number, a string, or another VBA value. A dictionary can also hold object references, but those require object assignment and are a more advanced use.

By default, text keys are compared using binary comparison. In practical terms, "North" and "north" can be treated as different keys. If the lookup should ignore capitalization, set CompareMode to vbTextCompare before adding any keys. Changing the comparison mode after entries have been added can cause an error.

Creating one: early and late binding

There are two common ways to declare a Dictionary in VBA. Late binding uses a general Object variable and creates the object by its registered programmatic identifier. It avoids requiring each workbook user to set a VBA reference, so it is often convenient for distributing a macro on Windows.

Early binding uses a specific Dictionary type and can provide code completion in the Visual Basic Editor. To use it, open the editor, choose Tools > References, and select Microsoft Scripting Runtime if it is available. The exact reference list can vary by Windows installation. A workbook that relies on this reference may need it set on another computer too; a missing reference can produce a “User-defined type not defined” compile error.

Late binding avoids that particular reference dependency, but it does not make the Windows Scripting.Dictionary component available on every operating system. The choice is about how VBA connects to the object, not about making a Windows COM component universally portable.

Core properties and methods

These are the Dictionary members most Excel macros use:

  • Add key, item inserts a new key and its value. It errors if that key is already present.
  • Exists(key) returns True if the key is present, making it useful before reading or adding an entry.
  • Item(key) retrieves or sets the value associated with a key. In VBA, assigning to an item for a key that is not present can add that key, but using Add or an explicit existence check can make the intended behavior clearer.
  • Count gives the number of keys currently stored.
  • Keys returns the keys as an array, and Items returns the corresponding values as an array.
  • Remove(key) deletes one entry; RemoveAll clears the dictionary.

You can loop through the keys with a For Each loop. The key is the useful thing to iterate over when you need to retrieve its associated value. If you need to preserve a particular order, do not assume a Dictionary will provide the sorted order you want; sort the data separately or use a structure designed for ordered data.

Example: count repeated values in a column

This example reads names from column A on a worksheet named Data, starting in row 2. It counts each nonblank name, ignoring differences in capitalization, and prints the results in the Visual Basic Editor’s Immediate window. The Immediate window is available in the editor through View > Immediate Window, or with Ctrl+G on Windows. The code assumes the worksheet exists and that column A contains ordinary text or numbers rather than values requiring special error handling.

Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z. This example only reads worksheet data and prints results in the editor, but the reminder matters particularly for macros that write to or clear cells.

Option Explicit

Sub CountNamesWithDictionary()
    Dim ws As Worksheet
    Dim dict As Object
    Dim lastRow As Long
    Dim r As Long
    Dim i As Long
    Dim key As String
    Dim keys As Variant
    Dim counts As Variant

    ' Read the source list from the Data worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "There are no names to count."
        Exit Sub
    End If

    ' Create a late-bound Dictionary and ignore letter case.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

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

            If Len(key) > 0 Then
                If dict.Exists(key) Then
                    dict(key) = CLng(dict(key)) + 1
                Else
                    dict.Add key, 1
                End If
            End If
        End If
    Next r

    ' Print each unique name and its count in the Immediate window.
    keys = dict.Keys
    counts = dict.Items

    For i = 0 To dict.Count - 1
        Debug.Print CStr(keys(i)) & vbTab & CStr(counts(i))
    Next i
End Sub

For example, if A2:A5 contains Alex, Sam, Alex, and Jo, the Immediate window shows three entries: Alex with 2, Sam with 1, and Jo with 1. Because the comparison mode is text-based, entries such as Alex and ALEX are counted together. Blank cells are skipped, and cells containing Excel errors are skipped rather than converted to text.

The key and count arrays returned by Keys and Items line up by position, which is why the loop can display each key with its matching count. If you want a reusable worksheet report instead, write those array values to a clearly chosen output range. Check that the destination is empty first, so the macro does not overwrite information you need.

Dictionary versus Collection and worksheet lookups

VBA also has a built-in Collection. A Collection can store items and can use keys, but it does not offer the same direct Exists method. Checking whether a keyed item exists in a Collection often requires error handling or a separate tracking structure. A Dictionary is usually simpler when the task depends on frequent membership checks or updating counts by key.

A worksheet lookup formula or Excel table may be a better fit when the result should remain visible, recalculate as data changes, or be understood by people who do not use VBA. A Dictionary is most useful for work performed inside a macro, particularly when processing many rows without repeatedly searching the sheet. It is not a replacement for a database or a sorted table when you need persistent records, a specified order, or shared access.

Common errors and how to avoid them

  • Duplicate key error: Add cannot add a key that is already present. Test with Exists, then update the existing item or choose another key.
  • Wrong capitalization behavior: Set CompareMode before adding entries if letter case should not distinguish keys. Also consider trimming leading and trailing spaces; otherwise "Sam" and " Sam " are different keys.
  • Missing reference: Early-bound code can fail to compile if Microsoft Scripting Runtime is not selected on that computer. Late binding can avoid a reference setting, but still requires the component to be available.
  • Type mismatch in a key: Normalize values before using them as keys. For example, decide whether numeric identifiers should be treated as numbers or text, and keep that choice consistent. A cell containing an Excel error also needs to be handled before converting its value with CStr.
  • Unexpectedly empty results: Check the worksheet name, header row, and last-row calculation. In the example, data begins in row 2 and the last used row is detected using column A.
  • Assuming the data is saved: A Dictionary is temporary in-memory data. It disappears when the procedure ends unless the macro writes its contents somewhere persistent.

Windows, Mac, and macro safety

The Scripting.Dictionary object is a Windows scripting component created through COM. Late binding does not remove that platform dependency, and Microsoft Scripting Runtime is not generally available as the same component in Excel for Mac. A workbook that uses it may therefore fail on a Mac even if the code compiles and runs on Windows. Excel for the web cannot run VBA macros.

If the workbook must work on both Windows and Mac, consider a VBA Collection for simpler keyed storage, a worksheet-based lookup table, or a custom class or routine that implements the needed behavior without relying on a Windows-only component. Test the chosen approach in the actual Excel versions and operating systems your users have. Also treat macros from untrusted sources cautiously: enable or run code only when you understand its source and purpose, and keep a backup before macros that edit workbook contents.

For a language reference rather than a data structure, Microsoft’s VBA documentation explains syntax and object-library members. In the Visual Basic Editor, the Object Browser can also help inspect available objects and members; select an item and use F2 on Windows to open it. The Object Browser describes what a member does, while a Dictionary itself is a runtime object used by your program.

Frequently Asked Questions

Is Dictionary built into VBA?
VBA code can use the Dictionary object through the Microsoft Scripting Runtime on Windows. It is not the same as a core VBA language type such as String, and its availability depends on the scripting component and how the project references or creates it.

Do Dictionary keys have to be unique?
Yes. A key identifies one entry, so adding the same key again with Add causes an error. Use Exists to check first, then update the existing item if that is the intended behavior.

How do I make Dictionary lookups case-insensitive?
Set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any keys. You can also normalize text yourself, but comparison mode is usually more direct when only capitalization should be ignored.

Can I use a VBA Dictionary on Mac?
Do not assume so. Scripting.Dictionary relies on a Windows COM component that is not generally available in Excel for Mac. For a workbook that must run on both platforms, use a cross-platform design such as a Collection or worksheet lookup and test it on Mac.

How do I get all the keys or values?
Use the Dictionary’s Keys or Items property, which returns an array. The two arrays correspond by position, so an item at a given index belongs to the key at that same index.

Related

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.