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

VBA & macros · Updated November 18, 2026

A VBA Scripting.Dictionary is an object that stores pairs of unique keys and associated values, letting a macro look up, count, or total information by key. It is especially useful when processing repeated values, such as finding each product’s total sales, because it can check whether a key exists and retrieve its value without repeatedly searching a worksheet range.

What the Scripting.Dictionary Object Is

Scripting.Dictionary is a Windows COM object provided by the Microsoft Scripting Runtime. It is not a built-in VBA collection, although it is commonly used in VBA because its key-based lookup methods are convenient. A dictionary has a collection of keys, and every key points to an item, or value. For example, the key "North" could point to the number 125, a customer name could point to a customer record, or a word could point to the number of times it occurs.

Each key must be unique under the dictionary’s comparison rules. Values do not have to be unique: two different keys can point to the same value. Items can hold many kinds of values, including numbers, strings, dates, arrays, and object references. That flexibility makes a dictionary useful for both simple tallies and more structured VBA work.

It is not a worksheet feature or formula. You create and use it in VBA code, typically in the Visual Basic Editor. Excel for the web does not run VBA macros, so a workbook that depends on this object needs a desktop version of Excel to run its VBA.

Creating a Dictionary: Early and Late Binding

There are two common ways to create the object. With late binding, VBA treats it as a general object and creates it by its COM programmatic identifier:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Late binding does not require a reference to the Microsoft Scripting Runtime in the workbook’s VBA project. This makes it convenient for sharing a macro with Windows users whose project references may differ. The trade-off is that the editor cannot provide the same level of type-specific autocomplete, and many dictionary constants may need to be written as their numeric values if they are not available through a reference.

With early binding, first open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare the variable using the dictionary type:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Early binding gives clearer declarations and editor assistance, and it can make code easier to read. It also means the project depends on that reference being available. If a reference is marked “MISSING” on another computer, the project may fail to compile until the reference is repaired. Both approaches use the same object; the choice is mainly about development convenience and portability within the environments you need to support.

How Keys, Items, and Lookups Work

The most-used members are Add, Exists, the default Item property, and Count. A small example illustrates their roles:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

dict.Add "North", 125
If dict.Exists("North") Then
    Debug.Print dict("North")  ' Prints 125
End If
Debug.Print dict.Count         ' Prints 1

Add creates a key-item pair and raises an error if that key already exists. Exists checks for a key and returns True or False. Writing dict(key) reads the item for that key. Assigning an item through that property can also create a missing key, which can be handy for tallying but is a source of accidental new entries if the key is misspelled.

Use Keys and Items when you need the contents as arrays, and use Remove to delete one key or RemoveAll to clear the dictionary. The Key property can change an existing key. If you need to loop over entries, a common pattern is For Each key In dict.Keys; declare the loop variable as a Variant. A dictionary is not a two-column worksheet range, so its enumeration order should not be treated as a sorting or reporting rule. Sort the keys separately if a particular order matters.

Practical Example: Totalling Rows by Category

Suppose a worksheet named Data has category names in column A and numeric amounts in column B, with headers in row 1. The following macro adds amounts for matching categories and prints each total in the Immediate window. It assumes category cells and amount cells contain usable values, with no worksheet error values in those columns.

Sub SummarizeByCategory()
    Dim ws As Worksheet
    Dim totals As Object
    Dim lastRow As Long
    Dim r As Long
    Dim category As String
    Dim amount As Double
    Dim key As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    Set totals = CreateObject("Scripting.Dictionary")
    totals.CompareMode = vbTextCompare

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

    For r = 2 To lastRow
        category = Trim$(CStr(ws.Cells(r, "A").Value2))
        If Len(category) > 0 And IsNumeric(ws.Cells(r, "B").Value2) Then
            amount = CDbl(ws.Cells(r, "B").Value2)
            If totals.Exists(category) Then
                totals(category) = CDbl(totals(category)) + amount
            Else
                totals.Add category, amount
            End If
        End If
    Next r

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

For instance, if the rows contain North/40, South/25, and North/10, the printed totals are North/50 and South/25. The dictionary performs the grouping in memory rather than requiring a separate worksheet search for every row. To display the results on a sheet instead, write the keys and items to cells, taking care to choose an output location that will not overwrite the source data.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even when the code’s purpose is only to automate a repetitive task. This example prints results rather than editing cells, but a copy is still a sensible precaution when adapting code.

Case Sensitivity and CompareMode

By default, a dictionary compares string keys using binary comparison. In practical terms, "North" and "north" are treated as different keys. Set CompareMode to vbTextCompare when capitalization should not distinguish keys. Do this immediately after creating the dictionary and before adding any entries:

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

Changing CompareMode after the dictionary contains data raises an error. The setting affects string-key comparisons; it does not trim spaces or normalize spelling. For example, "North" and " North " remain different keys unless the code trims the text first. This is why the example uses Trim$ as well as text comparison. Choose the comparison rule based on the data: a product code may need exact matching, while names entered with inconsistent capitalization may be better handled case-insensitively.

Common Errors and How to Avoid Them

Several mistakes account for many dictionary problems. Check these points when a macro behaves unexpectedly:

  • Adding a duplicate key: .Add raises an error when the key already exists. Use .Exists first if duplicates should update an existing total rather than stop the macro.
  • Using a missing key without checking: Reading a missing item can produce an empty value, while assigning to that key can create a new entry. Validate keys when a typo would be consequential.
  • Setting comparison mode too late: Assign CompareMode before the first key is added; changing it afterward is not allowed.
  • Confusing a key with its item: dict.Keys returns keys; dict.Items returns values. They are different arrays, even though their positions correspond.
  • Ignoring blanks or errors from cells: Decide how to handle blank keys, worksheet error values, and nonnumeric amounts before converting cell contents with CStr or CDbl. Validate inputs when the source data is not controlled.
  • Assuming an order: Do not rely on dictionary enumeration to produce alphabetical, numerical, or worksheet order. Sort keys explicitly when presentation order matters.

When an error occurs, inspect the key being processed and the dictionary’s Count. A breakpoint in the Visual Basic Editor and the Locals or Watch window can help identify whether a key was added twice, a value has an unexpected type, or the code reached a missing reference.

When to Use It Instead of a Collection or Worksheet Formula

A VBA Collection can store values and lets you retrieve an item by a string key, but it does not provide the dictionary’s direct Exists method or the same set of key-management features. A collection can be a reasonable built-in alternative for some tasks, especially when you need a simple collection of objects. A dictionary is often clearer when the code repeatedly asks whether a particular key has been seen or updates a total associated with that key.

For calculations that should remain visible and update when worksheet data changes, formulas or a PivotTable may be a better choice than a macro. A dictionary is most useful when VBA is already processing data, when a macro needs fast repeated lookups, or when intermediate groupings do not need to appear on the sheet. It is not automatically the best choice for every workbook: a simple formula or PivotTable may be easier for other users to inspect and maintain.

For large data sets, a common VBA pattern is to read a worksheet range into a two-dimensional Variant array, process the array with a dictionary, and write results back in a batch. Repeatedly reading individual cells can be slow because each worksheet interaction adds overhead. The dictionary helps with in-memory lookups; it does not remove the cost of thousands of cell-by-cell operations.

Windows, Mac, and Portability

The Microsoft Scripting Runtime is a Windows COM component. Late-bound creation with CreateObject("Scripting.Dictionary") is therefore a Windows-oriented solution and should not be assumed to work in Excel for Mac. Mac versions of Excel support VBA, but they do not provide general Windows COM automation in the same way. If a workbook must run on both Windows and Mac, test on both platforms and consider a VBA Collection, arrays, or a custom class as a more portable design. Each alternative has different capabilities, so the code may need to change rather than simply substituting one object name.

Excel for the web cannot run VBA macros. A workbook can be stored or viewed there, but its VBA code must be run in desktop Excel. If users need browser-based automation, that calls for a different approach, such as Office Scripts, which is separate from VBA and does not use the Scripting.Dictionary object in the same way.

Frequently Asked Questions

Do I need to add a reference to use Scripting.Dictionary?

No. Late binding with CreateObject("Scripting.Dictionary") does not require adding the Microsoft Scripting Runtime reference. Early binding does require selecting that reference in Tools > References in the Visual Basic Editor. The object itself is a Windows component, so avoiding a reference does not make it universally portable to Mac or the web.

Is Scripting.Dictionary available in Excel for Mac?

Do not rely on it for cross-platform Mac VBA. The object is supplied as a Windows COM component, and Windows COM automation is not generally available in Mac Excel. Use and test a suitable alternative, such as a Collection or arrays, if Mac support is required.

Can a dictionary contain duplicate keys?

No. Each key is unique according to its comparison mode. Calling .Add with an existing key raises an error. To update its value, check .Exists and assign a new item; to keep multiple records, use a distinct key or store a collection or array as the item.

What is the difference between Keys and Items?

Keys returns an array of the dictionary’s keys, while Items returns an array of their associated values. Corresponding elements occupy matching positions, but neither array is guaranteed to be sorted for reporting purposes.

Can Scripting.Dictionary store objects?

Yes. A dictionary item can hold an object reference as well as a basic value. When retrieving an object into an object variable, use VBA’s Set statement. Keep the object’s lifetime and reference handling in mind, especially if multiple objects refer to one another.

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.