VBA Scripting.Dictionary: How It Works and How to Use It

VBA & macros · Updated November 18, 2026

A VBA Scripting.Dictionary stores data as unique keys paired with values, so code can look up an item by name or ID without scanning a worksheet row by row. In Excel for Microsoft 365 on Windows, create one with CreateObject("Scripting.Dictionary"), add entries with Add, and test for a key with Exists.

What Is a VBA Scripting.Dictionary?

Scripting.Dictionary is an object in the Microsoft Scripting Runtime. It is often called a dictionary, map, or key-value store: each entry has a key that identifies it and an associated item that holds its value. For example, a customer ID can be the key and that customer’s order count can be the item.

Keys must be unique within one dictionary. Values do not: several different keys can refer to the same value. A dictionary is useful when a macro needs to test membership, retrieve a value by key, or build a set of distinct values. It can also accumulate totals, counts, or other results while processing data.

Unlike a worksheet range, a dictionary is held in memory while the code runs. It is not a cell range, does not automatically update when worksheet data changes, and is not saved as part of the workbook after the macro ends unless the code writes its contents somewhere.

How the Dictionary Works

A dictionary keeps a collection of key-item pairs. The key answers “which entry?” and the item answers “what value belongs to it?” In VBA, Exists(key) checks whether a key is present, while the Item(key) property retrieves or assigns its value. You will commonly see the shorter bracket form, such as dict(customer), used for that property.

For example, a dictionary might contain the pair "C104" and 7, meaning customer C104 appears seven times in a data set. Calling dict("C104") returns 7. Calling dict.Exists("C104") returns True. This direct key-based lookup is the central reason to use a dictionary rather than repeatedly searching a large range.

A dictionary does not sort its keys. Even if a particular run appears to return entries in a consistent order, do not make your logic depend on that order. If sorted output is needed, copy the keys to an array and sort them, or write them to a worksheet and sort the range.

Creating and Configuring a Dictionary

There are two common ways to create the object. Late binding uses CreateObject and does not require selecting a reference in the VBA editor. It is convenient when sharing a workbook among Windows users with different Office setups. Early binding declares a variable as Scripting.Dictionary and requires the Microsoft Scripting Runtime reference, which provides IntelliSense and compile-time type checking.

To set up early binding in the Windows VBA editor, choose Tools > References and select Microsoft Scripting Runtime. The library’s availability is a deployment consideration: a workbook that compiles with a reference on one computer may not find that reference on another. Late binding avoids that particular compile-time reference, but it does not make the Windows COM component available on platforms that lack it.

The dictionary’s CompareMode controls how text keys are compared. The default is binary comparison, which treats uppercase and lowercase letters as different. Set CompareMode to vbTextCompare before adding keys if you want text keys such as “North” and “north” to count as the same key. Changing the mode after entries have been added raises an error.

Adding, Finding, and Updating Entries

The Add method creates an entry and raises an error if the key already exists. That behavior is helpful when a duplicate indicates bad input, but many data-processing tasks are meant to combine repeats. In those cases, check Exists first and then either update the existing item or add a new pair.

  • dict.Add key, item adds a new key-item pair.
  • dict.Exists(key) returns True or False depending on whether the key exists.
  • dict(key) reads or assigns the item for a key. Assigning through this property creates the key if it is not already present.
  • dict.Count gives the number of entries.
  • dict.Keys and dict.Items return arrays of keys and items, respectively.
  • dict.Remove key deletes one entry, while dict.RemoveAll clears the dictionary.

For clarity, use Exists when the difference between adding and updating matters. If code uses Add without checking and encounters a repeated key, VBA normally reports an error rather than silently replacing the old item. Conversely, reading a missing key through the item property can create an entry with an empty value, so check existence before reading when missing keys are possible.

Example: Count Orders by Customer

This macro reads customer names from column A of a worksheet named Orders, starting at row 2, and counts how often each nonblank name appears. The dictionary uses case-insensitive text comparison, so “A. Lee” and “a. lee” are counted together. It displays the number of distinct nonblank customer names; each dictionary item holds that customer’s order count.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even if the macro only appears to make a small change. This example only reads data and displays a message, but the backup habit matters especially for macros that edit or delete cells.

Sub CountOrdersByCustomer()
    ' Count nonblank customer names in Orders column A.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim data As Variant
    Dim counts As Object
    Dim i As Long
    Dim customer As String

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    data = ws.Range("A2:A" & lastRow).Value2
    Set counts = CreateObject("Scripting.Dictionary")
    counts.CompareMode = vbTextCompare

    For i = 1 To UBound(data, 1)
        customer = Trim$(CStr(data(i, 1)))
        If Len(customer) > 0 Then
            If counts.Exists(customer) Then
                counts(customer) = counts(customer) + 1
            Else
                counts.Add customer, 1
            End If
        End If
    Next i

    MsgBox counts.Count & " distinct customers found."
End Sub

To run it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure. On Mac, the Visual Basic Editor is available through Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary by Mac keyboard and Excel version. This code uses a Windows scripting component, so it may not run in Excel for Mac.

Choosing Early or Late Binding

Late binding, shown in the example, declares the dictionary as an Object and creates it by name. It avoids a missing-reference compile error and is often practical for a macro distributed to Windows computers. The trade-off is that the editor cannot provide the same dictionary-specific IntelliSense or catch misspelled member names at compile time.

Early binding can be more comfortable during development. With the Microsoft Scripting Runtime reference enabled, declare the variable as Dim dict As Scripting.Dictionary and instantiate it with Set dict = New Scripting.Dictionary. The code is more explicit, and the editor can identify the object’s members. If you share an early-bound workbook, check the reference on the target computers; an unavailable or broken reference can prevent VBA from compiling.

Neither approach changes the dictionary’s key and value behavior. Late binding is not a cross-platform substitute for an unavailable component, and early binding is not inherently faster for every workbook. Choose based on the development support and deployment environment you actually have.

Common Problems and Misconceptions

  • Duplicate key error: Add rejects a key already in the dictionary. Use Exists to decide whether to add, replace, or accumulate the value.
  • Case-sensitive matches: The default comparison is binary. Set CompareMode to vbTextCompare before inserting text keys if case should not matter.
  • Unexpected empty entries: Referencing a missing key with dict(key) can create it. Test with Exists before reading a key that may be absent.
  • Wrong worksheet or range: The sample assumes a sheet named Orders, headers in row 1, and customer names in column A. Change those references to match the workbook; a misspelled sheet name causes a subscript error.
  • Blank or inconsistent keys: Trim text when leading or trailing spaces should be ignored. Decide how to handle blanks, errors, and differently formatted IDs before combining values; two strings that look similar may still be distinct keys.
  • Assuming sorted output: Dictionary enumeration is not a sorting feature. Sort explicitly when the order matters.

A dictionary is not automatically faster for every task. Creating it has a cost, so for a tiny range or a one-time lookup, a straightforward worksheet formula or loop may be simpler. It is especially useful when many lookups or repeated-key aggregations happen during one macro run.

Platform and Macro Safety

Scripting.Dictionary is a Windows scripting-runtime COM object. Excel for the web does not run VBA macros, and Excel for Mac does not provide the same Windows COM environment; CreateObject("Scripting.Dictionary") therefore should not be treated as a portable Mac solution. If a workbook must run on both Windows and Mac, test on each platform and choose a platform-neutral data structure or separate implementation rather than assuming late binding solves compatibility.

Macros can change workbook contents and are not reversible with Excel’s normal Undo command. Keep an untouched backup, review code before running it, and do not enable macros in an untrusted workbook just to use a dictionary. A dictionary itself does not access the internet or make a macro safe; the operations performed by the surrounding code determine the risk.

Alternatives and When to Use Them

VBA’s built-in Collection can store values and objects and is available without the Scripting Runtime, but it does not offer the same straightforward Exists method or convenient key-based update pattern. A collection can be suitable for an ordered sequence or a small set of values, but checking whether a key exists often requires extra error-handling code.

Arrays are useful when data has a fixed structure and code processes it sequentially. They can be very efficient for bulk work, but a plain array does not provide dictionary-style key lookup by itself. Worksheet functions such as XLOOKUP or COUNTIF may be easier when the result should remain visible and update with worksheet data; a dictionary fits better when a macro needs many in-memory lookups or aggregation before writing a result.

For a set of distinct keys with no associated values, a dictionary is still practical: add each key once and use Exists to test membership. For multiple values per key, store a collection or array as the item, or use a composite key if appropriate. Construct composite keys carefully so that separators cannot make different combinations collide.

Frequently Asked Questions

How do I check whether a key exists in a VBA dictionary? Call dict.Exists(key). It returns True if the key is present and False otherwise. This is the clearest way to guard a lookup when the key may be missing.

How do I make dictionary keys case-insensitive? Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any entries. If keys have already been added, create a new dictionary with the desired comparison mode and populate it again.

Does a Scripting.Dictionary keep keys in sorted order? No. Do not rely on the order returned by Keys or by iterating the dictionary. Sort the resulting keys separately if presentation or processing requires a particular order.

Can I use Scripting.Dictionary in Excel for Mac? It depends on the available platform components, but the Windows scripting-runtime COM object is not a dependable cross-platform VBA feature. Excel for Mac and Excel for the web should not be assumed to support the Windows example; use a portable alternative or test a separate Mac implementation.

What is the difference between Add and assigning dict(key)? Add explicitly creates a new pair and errors if the key already exists. Assigning to dict(key) sets the item’s value and can create the key when it is missing, so use Add when duplicates should be detected and assignment when adding-or-replacing is intended.

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.