VBA Dictionary Reference: Setup, Binding, and Alternatives

VBA & macros · Updated November 18, 2026

In Excel VBA, a Dictionary is provided by the Microsoft Scripting Runtime. To use it with early binding, enable the Microsoft Scripting Runtime reference in the Visual Basic Editor; for late binding, declare the object as Object and create it with CreateObject. Early binding offers editor support, while late binding avoids a compile-time reference dependency.

What the VBA Dictionary reference means

A Dictionary stores pairs of keys and values. Each key identifies one item, much like a label used to retrieve a value. For example, a macro can use product codes as keys and quantities as values, or use customer names to track whether a record has already been processed. Unlike a worksheet range, the Dictionary is an in-memory VBA object; it is useful for collecting, checking, and looking up data while a macro runs.

The phrase “VBA Dictionary reference” usually means the project reference that makes the Dictionary type available to VBA. In the Visual Basic Editor, the relevant library is named Microsoft Scripting Runtime. This is a project-level setting: it is stored with the VBA project, not copied into a workbook as a separate library file. The dictionary object itself is created by your code.

A reference is not the same thing as a variable declaration. A declaration such as Dim dict As Scripting.Dictionary tells VBA the variable’s specific type, while the checked reference tells VBA where that type is defined. Without the reference, the editor may report “User-defined type not defined” when it compiles that declaration.

Early binding versus late binding

With early binding, you check the library reference and declare the variable as Scripting.Dictionary. The editor can then offer type-aware autocomplete, and VBA can check many object and member names while compiling. This is convenient during development and makes code easier to inspect.

Late binding uses a generic Object variable and asks Windows to create the Dictionary at runtime. It does not require the Microsoft Scripting Runtime reference to be checked in the VBA project. That can make a workbook easier to distribute among Windows computers with differing reference settings, but it removes much of the type information and autocomplete. A misspelled member may not be caught until the macro reaches that line.

  • Choose early binding when you control the Windows environment and want clearer declarations and editor assistance.
  • Choose late binding when you need to avoid a checked project reference, such as when distributing a workbook to other Windows users.
  • Do not assume late binding makes the code portable everywhere. It still depends on the scripting component being available at runtime, and it does not solve platform differences such as Excel for Mac.

How to enable Microsoft Scripting Runtime

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. In the editor, select Tools > References, find Microsoft Scripting Runtime in the list, check its box, and select OK. If you do not see it immediately, scroll through the alphabetized list. Then add a declaration such as Dim dict As Scripting.Dictionary in a procedure or module.

On a Mac keyboard, the shortcut for opening the Visual Basic Editor may require Option+F11 or Fn+Option+F11, depending on the keyboard and macOS settings. The Windows Tools > References procedure and the Windows scripting library should not be treated as a guaranteed Mac setup. See the Mac section below before making the reference a requirement for a shared workbook.

A checked reference is specific to the VBA project. If you work with several workbooks, enabling it in one project does not automatically enable it in another. When opening a workbook created elsewhere, inspect the References list if a compile error appears; a library marked MISSING is a sign that the project expects a reference unavailable at its current location.

Creating and using a Dictionary

Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, even if a particular example only prints results in the Immediate window. The following early-bound example assumes Microsoft Scripting Runtime is checked. It adds two items and prints the value for one key.

Option Explicit

Sub DictionaryExample()
    ' Requires the Microsoft Scripting Runtime reference.
    Dim dict As Scripting.Dictionary

    Set dict = New Scripting.Dictionary
    dict.Add "North", 12
    dict.Add "South", 8

    Debug.Print dict("North")
End Sub

To use late binding instead, remove the specific Scripting.Dictionary type and instantiate the object with CreateObject. This version also uses declared variables and writes the same result to the Immediate window, which you can show in the Visual Basic Editor with Ctrl+G on Windows.

Option Explicit

Sub LateBoundDictionaryExample()
    ' No Microsoft Scripting Runtime reference is required.
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 12
    dict.Add "South", 8

    Debug.Print dict("North")
End Sub

Both examples print 12. The key is the text North and its associated item is the number 12. The Dictionary is separate from the worksheet: these examples do not write values into cells. To transfer dictionary contents to a sheet, your code must explicitly place the keys or items into a range.

Keys, values, and comparison behavior

Dictionary keys must be unique. Calling Add with a key that already exists raises an error, so use Exists when duplicates are possible. A common pattern is to test the key first, then either update its stored value or add a new item. The Count property reports the number of entries, Remove deletes one entry, and RemoveAll clears the object.

The default key comparison is binary, which generally treats differently capitalized text as different keys. If you want North and NORTH to count as the same key, set CompareMode to vbTextCompare immediately after creating the object and before adding any entries. Changing CompareMode after entries have been added can raise an error. For instance, a text comparison is useful when imported data has inconsistent capitalization but represents the same category.

The Exists method is a clear way to test for a key before reading or adding it. Do not assume that asking for an item that is absent behaves like a harmless lookup: the Scripting.Dictionary Item property can create a missing key with an empty item when it is accessed. Testing first avoids accidental entries and makes the macro’s intent easier to understand. The Keys and Items properties return arrays of the dictionary’s keys and values, respectively, which can be useful when processing all entries.

Common reference and runtime problems

If VBA says “User-defined type not defined” on a Scripting.Dictionary declaration, the usual cause is that Microsoft Scripting Runtime is not checked in that project. Either enable it through Tools > References or switch to a late-bound Object declaration. If a reference is labeled MISSING, identify the unavailable library and correct or remove the dependency; changing the Dictionary declaration alone will not fix a different missing library used elsewhere in the project.

A runtime error such as ActiveX component can’t create object when using CreateObject means the requested scripting object could not be created in that environment. It is not the same as a compile-time missing-reference error. Check that the workbook is running in a supported Windows desktop environment and that the object is available there. Late binding postpones object creation until runtime; it does not guarantee success on every computer.

An error when calling Add often means the key already exists. Test it with Exists before adding, or deliberately update an existing item. If a project compiles on one machine but not another, inspect references for missing entries and confirm that the receiving computer has the needed components. For shared workbooks, test the macro on the actual target versions and operating systems rather than relying only on the author’s setup.

Dictionary compared with Collection and worksheet lookups

A VBA Collection is built into VBA and can store items under keys, so it does not require the Scripting Runtime reference. It is a practical alternative for simple collections, but its interface differs: it does not provide the same convenient Exists method, and key handling and updates may require extra error-handling code. A Dictionary is often simpler when code frequently asks whether a key exists or needs to update values by key.

Worksheet functions and lookup formulas are a better fit when the result needs to remain visible, recalculate with changing cell data, or be maintained by spreadsheet users. A Dictionary is more appropriate for temporary in-memory work inside a macro, such as counting repeated values before writing a summary. Another option is a VBA array when the data is ordered and accessed by position rather than by a unique label. The right choice depends on whether you need a key-based object, a visible worksheet result, or a compact sequence of values.

Excel for Mac and portability

Excel for Mac supports VBA, but Windows COM and ActiveX components are not generally interchangeable with Mac automation. The Microsoft Scripting Runtime reference and CreateObject approach should therefore not be treated as portable merely because both platforms run VBA. Availability can depend on the Excel environment and component support, so a workbook that relies on Scripting.Dictionary should be tested on the exact Mac version and setup where it will run.

If reliable Windows-and-Mac support is essential, consider a VBA Collection, arrays, or a custom class-based solution, and test the chosen approach on both platforms. Keep platform-dependent code isolated when possible, and document the requirement for users. Late binding can help avoid a broken checked reference on Windows, but it does not itself provide a cross-platform Dictionary implementation.

Frequently Asked Questions

Do I need to add a reference to use a VBA Dictionary?
Only for early binding. Check Microsoft Scripting Runtime to declare a variable as Scripting.Dictionary. Late binding with an Object variable and CreateObject does not need that project reference, though the runtime object must still be available.

Where is Microsoft Scripting Runtime in the References list?
Open the Visual Basic Editor, choose Tools > References, and look for Microsoft Scripting Runtime in the list. The setting applies to the current VBA project, not every workbook you open.

What is the difference between Dictionary and Scripting.Dictionary?
Scripting.Dictionary is the specific object type supplied by the Microsoft Scripting Runtime. “Dictionary” is often used as a shorthand name for that object, but the precise early-bound VBA type is Scripting.Dictionary.

Can I use a Dictionary without a reference?
On supported Windows installations, late binding avoids checking the reference: declare the variable As Object and use CreateObject(“Scripting.Dictionary”). This removes the compile-time type dependency, not the runtime dependency on the scripting object.

Why does my Dictionary macro work on Windows but fail on Mac?
The code may rely on the Windows scripting component or COM behavior that is not available in the same way on Mac. Test on the target Mac setup and consider a Collection, array, or another platform-appropriate design if cross-platform operation is required.

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.