VBA Dictionary Declaration: Setup, Methods, and Examples

VBA & macros · Updated November 18, 2026

To declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed by Set dict = CreateObject("Scripting.Dictionary"); this late-bound form does not require adding a reference. A Dictionary stores unique keys with associated values, making it useful for fast lookups, counting, and removing duplicate entries.

What a VBA Dictionary Is

A Dictionary is a key-and-value collection provided by the Microsoft Scripting Runtime on Windows. Each entry pairs a key—such as a product code, customer name, or worksheet row identifier—with a value, such as a description, total, or object. You retrieve a value by its key rather than by its position in a numbered list.

For example, a dictionary could map each department name to its sales total. When a department appears again, code can find the existing key and update its total instead of searching through a separate list. Dictionary keys are unique: adding the same key a second time raises an error unless the code checks for it or updates the existing item.

VBA does not have a dedicated Dictionary keyword or built-in declaration type in the way it has types such as Collection. On Windows, the commonly used Dictionary comes from the Scripting Runtime. The declaration choice determines whether VBA checks that library at compile time or finds it at run time.

Choose Late Binding or Early Binding

There are two common ways to declare and create a Dictionary in Windows VBA:

  • Late binding: Declare the variable as Object and create it with CreateObject. This avoids setting a project reference and is usually easier to share between Windows computers.
  • Early binding: Declare it as Scripting.Dictionary and create it with New. This gives the VBA editor access to the Dictionary type and its member names while writing code, but requires a reference to the library.

For a workbook that will run only in a managed Windows environment, either approach can work. Late binding is a practical default for portable Windows workbooks because a missing reference is less likely to cause a compile error on another computer. Early binding is convenient during development, especially when you want editor assistance and compile-time checks. Neither approach makes the Windows Scripting Runtime available to Excel for Mac.

Late-Bound Declaration and Creation

This complete example declares a late-bound Dictionary, adds two entries, and reads one value. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

Sub CreateLateBoundDictionary()
    Dim dict As Object
    Dim city As String

    ' Create a Windows Scripting.Dictionary without a project reference.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "Oslo", 710000
    dict.Add "Lima", 9750000

    city = "Oslo"
    If dict.Exists(city) Then
        MsgBox dict(city)
    End If
End Sub

Set is required when assigning an object to an object variable. The variable itself is declared with Dim; CreateObject creates the Dictionary at run time. In this example, the key is a city name and the value is a number. The displayed value for Oslo is 710000.

Early-Bound Declaration and Reference Setup

For early binding, first enable the library in the VBA editor on Windows. Press Alt+F11 to open the editor, then choose Tools > References and select Microsoft Scripting Runtime. The exact wording can vary slightly by installed Office or Windows components. After selecting it, declare and create the Dictionary like this:

Sub CreateEarlyBoundDictionary()
    Dim dict As Scripting.Dictionary

    ' Requires the Microsoft Scripting Runtime reference.
    Set dict = New Scripting.Dictionary

    dict.Add "A100", "Keyboard"
    MsgBox dict("A100")
End Sub

Here the message displays Keyboard. If the reference is not available or is marked as missing on another computer, the project can fail to compile. To change to late binding, declare the variable as Object and use CreateObject("Scripting.Dictionary").

Add, Find, Update, and Remove Entries

Use Add to create a new key-value pair and Exists to check whether a key is already present. The Item property—often written with parentheses after the dictionary variable—returns or sets the value for a key. You can also use dict(key) as shorthand for dict.Item(key).

This pattern safely counts repeated labels. The example uses text keys and stores a numeric count. Save a copy of the workbook before running the macro because VBA changes cannot be reversed with Ctrl+Z.

Sub CountLabels()
    Dim dict As Object
    Dim labels As Variant
    Dim label As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    labels = Array("Red", "Blue", "Red", "Green", "Blue", "Red")

    For Each label In labels
        If dict.Exists(CStr(label)) Then
            dict(CStr(label)) = dict(CStr(label)) + 1
        Else
            dict.Add CStr(label), 1
        End If
    Next label

    MsgBox dict("Red")
End Sub

The result is 3, because Red occurs three times. Calling Add for a key that already exists would instead raise an error, which is why the code checks Exists first. To change an existing value, assign to that key as shown. Use dict.Remove key to delete one entry, or dict.RemoveAll to clear every entry while keeping the Dictionary object.

Keys, Comparison Rules, and Case Sensitivity

By default, text keys are compared using binary comparison, so capitalization matters: "North" and "north" can be distinct keys. To make text-key matching case-insensitive, set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any entries. Changing the comparison mode after entries have been added can raise an error.

Sub CaseInsensitiveKeys()
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    ' Set comparison behavior before adding keys.
    dict.CompareMode = vbTextCompare

    dict.Add "North", "Region 1"
    MsgBox dict.Exists("north")
End Sub

The message returns True. This setting changes how text keys are compared; it does not change the spelling or capitalization stored in the original key. Choose the comparison mode deliberately when keys come from user input or worksheet cells, since unexpected spaces are still different characters. For example, "North" and "North " are not the same key unless the input is cleaned first.

Loop Through a Dictionary

The Keys and Items properties return arrays of keys and values. A straightforward way to visit every entry is to loop through the keys and look up each corresponding item. Count reports the number of entries. Do not rely on a Dictionary to represent a sorted list; sort the keys separately if order matters.

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A100", "Keyboard"
    dict.Add "B205", "Mouse"

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

Open the Immediate window in the VBA editor with Ctrl+G to see the printed entries. On a Mac keyboard, the shortcut and editor availability can depend on the keyboard and Excel version; use the editor’s View menu to open the Immediate window if needed. A key variable declared as Variant is useful for this loop because the key array is returned as a Variant array.

Common Errors and Practical Safeguards

  • Duplicate key error: Check Exists before calling Add, or assign a new value to an existing key when updating is intended.
  • Missing reference: An early-bound declaration can fail if Microsoft Scripting Runtime is not selected or is missing. Use late binding for Windows workbooks that need to avoid that project reference.
  • Object variable not set: Create the Dictionary with Set before using it. A declared object variable is initially Nothing.
  • Wrong comparison behavior: Set CompareMode before adding keys, and decide whether capitalization should distinguish keys.
  • Unexpected key mismatch: Check for leading or trailing spaces and inconsistent data types. Converting worksheet values to a consistent string format can make matching more predictable.

A Dictionary can store objects as values as well as ordinary values, but object assignments need object-reference handling with Set. For basic lookups and counters, storing strings, numbers, dates, or other simple values is easier to maintain. Also, a Dictionary is held in memory: changing it does not write anything back to worksheet cells unless the macro explicitly outputs the data.

Dictionary Compared With Collection and Arrays

A Dictionary is a good fit when code needs to look up an item by a unique key or test whether a key exists. A VBA Collection can also hold items and can use keys, but it does not provide the same built-in Exists method or the same convenient key-and-item arrays. Arrays are useful for ordered data and can be efficient for processing a fixed block of worksheet values, but finding a particular value may require searching through the array.

Choose based on the job rather than assuming one container is always fastest or best. Use a Dictionary for unique-key lookups and counts; use an array for ordered batches of data; and consider a Collection for a simple group of items where its behavior is sufficient. A Dictionary does not automatically sort entries or remove duplicates from a worksheet range; your code must decide how to handle order and output.

Excel for Mac and Other Alternatives

The Scripting Dictionary examples above target Excel for Microsoft 365 on Windows. Excel for Mac does not provide the Windows COM-based Scripting Runtime in the same way, so CreateObject("Scripting.Dictionary") and a reference to Microsoft Scripting Runtime are not reliable cross-platform choices. A macro that depends on them should not be assumed to run unchanged on Mac.

For code that must run on both Windows and Mac, consider a VBA Collection, arrays, or a custom class and lookup routine, depending on the required behavior. These options may need more code to check for existing keys or manage updates. A third-party VBA Dictionary implementation is another possibility, but it adds a dependency that must be included and maintained. Test cross-platform macros in the actual Excel versions and operating systems where the workbook will be used.

Frequently Asked Questions

How do I declare a Dictionary in VBA?
For late binding on Windows, write Dim dict As Object, then initialize it with Set dict = CreateObject("Scripting.Dictionary"). For early binding, use Dim dict As Scripting.Dictionary and enable the Microsoft Scripting Runtime reference.

Do I need to add a reference for Scripting.Dictionary?
Only for early binding. Late binding uses Object and CreateObject, so you do not select the reference in Tools > References, although the Windows Scripting Runtime still needs to be available at run time.

How do I check whether a Dictionary contains a key?
Call dict.Exists(key). It returns True when the key exists and False when it does not. This is the normal check to make before adding a key that may already be present.

Can a Dictionary contain duplicate keys?
No. Keys must be unique under the Dictionary’s comparison rules. Use a different key, update the existing item’s value, or store multiple values inside a collection or array associated with that key.

Does Scripting.Dictionary work in Excel for Mac?
Do not rely on the Windows Scripting Runtime Dictionary for Mac VBA. For a workbook that must run on Mac, use a platform-compatible design such as a Collection, arrays, or custom VBA code, and verify it in the target Excel environment.

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.