VBA Dictionary Library: Setup, Uses, and Alternatives

VBA & macros · Updated November 18, 2026

A VBA dictionary library is a way to store and retrieve data as key–value pairs, with each key identifying one item. On Windows, the usual choice is the Scripting.Dictionary object: it is available through the Microsoft Scripting Runtime, or it can often be created without adding a project reference by using late binding.

What “VBA dictionary library” means

VBA does not have a built-in Dictionary keyword or a dedicated dictionary type in the same way that some programming languages do. When people ask for a “VBA dictionary library,” they usually mean either Microsoft’s Scripting.Dictionary object or a separate VBA class module that provides similar behavior.

A dictionary associates a key with a value. For example, a key such as "Invoice-104" could point to a customer name, a row number, or a running total. Unlike a worksheet lookup formula that searches a range each time, a dictionary lets a macro test whether a key exists and retrieve its associated value directly. Keys are unique; values do not have to be.

Dictionaries are useful for removing duplicates, counting occurrences, matching records between sheets, and building quick lookup tables while a macro runs. They hold data in memory, not in a worksheet by themselves, so a macro must still write results back to cells or another destination when the work is done.

What Scripting.Dictionary provides

The Windows Scripting.Dictionary object has a compact set of operations that covers many common VBA tasks:

  • Add key, value adds a new key and its associated value.
  • Exists(key) checks whether a key is present.
  • Item(key), often written as dict(key), gets or sets the value for a key.
  • Keys and Items return arrays containing the keys and values.
  • Count reports the number of entries.
  • Remove(key) deletes one entry, while RemoveAll clears the dictionary.

For example, a macro could store a product code as the key and its worksheet row as the value. Later, it can check whether another product code is in the dictionary and retrieve the corresponding row without repeatedly searching the original list. A value can also be a count, a string, or another object, depending on the task.

One detail matters when reading items: test with Exists first if a missing key should not be created or treated as a valid result. The dictionary’s item property can create a new entry when a previously absent key is referenced. Also, adding a key that already exists with Add causes an error; assigning with dict(key) = value is the usual way to set or replace an item.

Early binding and late binding

There are two common ways to create a Scripting.Dictionary on Windows. With early binding, the project refers to the type library. In the Visual Basic Editor, open Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. The editor can then offer its members through IntelliSense, and VBA can check the object type while compiling.

Early binding is convenient for development, but the reference must be available on each computer running the workbook. A broken or missing reference can produce a “MISSING” entry in the References dialog and prevent code from compiling. This can be a concern when a workbook is distributed across different Windows configurations.

Late binding avoids a compile-time reference. Declare the variable as Object and create it with CreateObject("Scripting.Dictionary"). This is often simpler for a workbook shared between Windows computers, although it gives up IntelliSense and compile-time type checking. Late binding does not make the COM object portable to a platform where that object is not available.

A practical unique-count example

This macro counts distinct, nonblank labels in column A below a header. It treats labels with different capitalization as the same and trims leading or trailing spaces. It ignores Excel error cells. The result is displayed in a message box rather than written to the sheet.

Sub CountUniqueLabels()
    ' Count nonblank, distinct labels in column A below the header.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim cellValue As Variant
    Dim key As String
    Dim dict As Object

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

    If lastRow < 2 Then
        MsgBox "There are no data rows in column A."
        Exit Sub
    End If

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    For r = 2 To lastRow
        cellValue = ws.Cells(r, "A").Value
        If Not IsError(cellValue) Then
            key = Trim$(CStr(cellValue))
            If Len(key) > 0 Then
                If Not dict.Exists(key) Then dict.Add key, True
            End If
        End If
    Next r

    MsgBox "Unique labels: " & dict.Count
End Sub

For example, if the data rows contain North, South, North, and north, the macro reports two unique labels. The comparison setting is chosen before any keys are added, as required. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the usual shortcut is Option+F11, though keyboard and function-key settings can affect it.

Choosing comparison behavior and key design

By default, a Scripting.Dictionary compares string keys using binary comparison, so capitalization can distinguish keys. Set CompareMode to vbTextCompare before adding entries if North and north should match. Changing the comparison mode after adding data can raise an error, so decide on the rule when creating the dictionary.

Key design is just as important as the comparison setting. If a key is made from multiple fields, simple concatenation can create collisions: combining customer AB with region C produces the same text as customer A with region BC. Use a delimiter that cannot occur in the fields, or encode the parts unambiguously. Decide how to handle blank cells, extra spaces, numbers stored as text, and dates; otherwise values that look similar to a person may become different keys to VBA.

Dictionary iteration should not be used as a promise of a particular sort order. If the output must be alphabetized or follow the source order, sort the keys or retain ordering information separately. A dictionary is primarily for membership and lookup, not a replacement for a sorted table.

Windows, Mac, and Excel for the web

Scripting.Dictionary is a Windows COM automation object. It is commonly available in desktop Excel for Windows, but a workbook’s exact environment can vary, especially on managed computers. Early binding depends on the Microsoft Scripting Runtime reference; late binding depends on the COM object being creatable. Changing from early to late binding does not remove that platform dependency.

Excel for Mac supports VBA, but it does not provide Windows COM automation in the same way. Consequently, code based on CreateObject("Scripting.Dictionary") is not a dependable cross-platform solution for Mac users. A portable alternative is to use VBA’s built-in Collection when its simpler features are enough, or import a VBA dictionary class module designed for Mac compatibility, such as the community VBA-Dictionary project. Check the project’s current compatibility notes and license before adopting it, and test the workbook on the Excel versions you need to support.

Excel for the web does not run VBA macros. A dictionary-based macro needs to run in desktop Excel, whether on Windows or Mac with an appropriate implementation. If browser-based automation is a requirement, consider a different technology supported in that environment rather than expecting a VBA library to execute there.

Common problems and how to avoid them

  • “User-defined type not defined”: With early binding, check that Microsoft Scripting Runtime is selected in Tools > References. For broader Windows compatibility, late binding may avoid a missing reference, but it cannot solve a missing COM object or Mac incompatibility.
  • Duplicate-key errors: Use Exists before Add, or assign the item when replacing a value is intended.
  • Unexpected duplicates: Review CompareMode, whitespace trimming, and how numbers or dates are converted to strings. Normalize keys consistently when adding and looking them up.
  • Wrong values from an empty lookup: Check Exists before reading an item if absence has a distinct meaning. Do not assume that requesting an unknown key is a harmless read.
  • Results appear in a different order: Do not rely on dictionary enumeration order. Apply an explicit sort or track order independently.

When troubleshooting a workbook shared with colleagues, confirm which Excel platform runs the macro and whether the project uses early or late binding. A workbook that compiles on one Windows computer may still fail on a Mac or on a machine with a missing reference. Testing with representative blank cells, repeated keys, capitalization differences, and error values can expose assumptions before the macro is used on important data.

Alternatives, cost, and choosing a library

For Windows-only workbooks, Scripting.Dictionary is often the most straightforward option: it is a standard Windows component rather than a separate paid product, though its availability and reference state should still be checked in the target environment. A separately imported VBA class may provide better cross-platform support, but introduces code to review, maintain, and distribute. Use a reputable source, review its documentation and license, and keep a copy of the version included with the workbook.

VBA’s built-in Collection is another option. It can hold items and allows keyed access, but it does not provide the same direct Exists method or convenient arrays of all keys and items. For a small fixed set of values, arrays may be simpler. For data already stored in cells, worksheet operations or lookup formulas may be easier for other users to inspect. Choose based on platform needs, data size, maintainability, and whether the code needs to test key membership frequently.

Frequently Asked Questions

Is Dictionary built into VBA?
Not as a native VBA data type. On Windows, Scripting.Dictionary is supplied by the Microsoft Scripting Runtime and can be used through a reference or late binding. Other platforms may require a different implementation.

Do I need to enable Microsoft Scripting Runtime?
Only for early binding. Select it under Tools > References in the Visual Basic Editor. Late binding uses an Object variable and CreateObject, so the reference is unnecessary, but the Windows COM object is still required.

Can I use Scripting.Dictionary on Mac?
Do not assume so. Mac Excel supports VBA but not the Windows COM dependency used by Scripting.Dictionary. Consider a compatible VBA class or Collection and test on the target Mac version.

How do I make dictionary keys ignore capitalization?
Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any keys. Apply consistent cleanup, such as trimming spaces, if that is part of the matching rule.

Is a dictionary faster than searching worksheet cells?
For repeated lookups in a macro, keeping keys in memory can avoid repeatedly scanning worksheet ranges and is often more efficient. The best choice depends on the data and code; for a single small lookup, a worksheet function or a simple loop may be clearer.

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.