VBA Dictionary Set: Create, Add, and Retrieve Items

VBA & macros · Updated November 18, 2026

In VBA, use Set to assign a Dictionary object to an object variable, for example Set dict = CreateObject("Scripting.Dictionary"). Add ordinary data with dict.Add key, value or assign it with dict(key) = value; Set is not needed for strings or numbers.

What Set means in a VBA Dictionary

Set is VBA’s assignment statement for object references. A Dictionary is itself an object, so its variable must be assigned with Set. Without it, VBA will generally report an object-related error when you try to assign the result of CreateObject or New.

A Dictionary stores pairs: a unique key and an associated item. For example, a customer ID can be the key and a name, number, or object can be the item. The syntax depends on what you are assigning:

  • Set dict = ... assigns a Dictionary object to the variable dict.
  • dict.Add "A17", "Mina" adds a key and a string item.
  • dict("A17") = "Mina Patel" assigns or replaces a simple item.
  • Set savedObject = dict("A17") assigns an object item retrieved from the Dictionary.

This distinction is the source of many “VBA Dictionary Set” questions: use Set for object references, not for every operation involving a Dictionary.

Create a Dictionary with late binding

Late binding creates the Dictionary at run time, without requiring a reference to the Microsoft Scripting Runtime in the VBA project. It is a convenient choice for a small macro that needs to work across Windows computers where the reference might not be configured.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z; on Mac, Command+Z does not undo macro changes either.

Option Explicit

Sub CreateDictionaryLateBound()
    Dim dict As Object

    ' Create a Scripting.Dictionary at run time.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "A17", "Mina"
    Debug.Print dict("A17")
End Sub

The variable is declared As Object, and Set assigns the object returned by CreateObject. The final line prints the item associated with key A17 in the Immediate window. In the Visual Basic Editor, open that window from View > Immediate Window.

A Dictionary cannot contain the same key twice. If the key is already present, Add raises an error. You can test first with Exists, or assign a new scalar item using the Dictionary’s default Item property: dict("A17") = "Mina Patel".

Use early binding when you want type information

Early binding declares the variable as Scripting.Dictionary. The editor can then provide member names and type checking, which can make code easier to write and maintain. On Windows Excel, enable the reference in the Visual Basic Editor using Tools > References, then select Microsoft Scripting Runtime. The exact reference list can vary by Office installation.

Option Explicit

Sub CreateDictionaryEarlyBound()
    Dim dict As Scripting.Dictionary

    ' Create a Dictionary using the selected reference.
    Set dict = New Scripting.Dictionary

    dict.Add "A17", "Mina"
    Debug.Print dict.Count
End Sub

Here, Set is still required because dict is an object variable. The difference is that the type is known when the project is compiled rather than being resolved from the generic Object type at run time. If you share a workbook, remember that another Windows user may need the same reference enabled; a missing reference can cause a compile error.

Add, update, and retrieve ordinary items

For strings, dates, and numbers, the normal workflow is to add a key-value pair, check whether a key exists when that matters, and then read or update the item. This example uses late binding so it does not depend on the early-binding reference:

Option Explicit

Sub AddAndUpdateDictionaryItems()
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "North", 125
    dict.Add "South", 90

    If dict.Exists("North") Then
        dict("North") = 140
    End If

    Debug.Print dict("North")
End Sub

The output is 140. The assignment to dict("North") replaces the existing item; it does not add another copy of the key. If you instead call Add "North", 140 after the first addition, VBA raises a duplicate-key error. For code that might encounter existing keys, choose deliberately between updating and adding rather than relying on an error to decide.

Exists checks for a key and returns True or False. It is often clearer than trying to infer whether a missing item returned an empty value, because an empty string, zero, or an uninitialized item may also be a legitimate value in your data.

Store and retrieve object items

A Dictionary can hold object references as items as well as simple values. For example, you might associate a short label with a worksheet. You still use Set when assigning the retrieved worksheet to a worksheet variable.

Option Explicit

Sub StoreWorksheetInDictionary()
    Dim dict As Object
    Dim sourceSheet As Worksheet
    Dim savedSheet As Worksheet

    Set dict = CreateObject("Scripting.Dictionary")
    Set sourceSheet = ThisWorkbook.Worksheets("Data")

    ' Add the worksheet object as the item.
    dict.Add "source", sourceSheet

    ' Retrieve the object reference with Set.
    Set savedSheet = dict("source")
    Debug.Print savedSheet.Name
End Sub

This assumes that the workbook contains a worksheet named Data. The Dictionary holds a reference to that worksheet; it does not make a copy of the sheet. If you need to replace an object item, a straightforward approach is to remove the old key and add the new object. Keep the key-existence check in mind before calling Remove.

Choose key comparison rules before adding keys

By default, a Dictionary compares string keys using binary comparison, so keys such as "North" and "north" are treated as different. To make text keys case-insensitive, set CompareMode before adding any items:

Option Explicit

Sub SetDictionaryCompareMode()
    Dim dict As Object

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

    dict.Add "North", 125
    Debug.Print dict.Exists("north")
End Sub

The printed result is True. If you try to change CompareMode after the Dictionary already contains keys, VBA raises an error. Decide whether your application needs case-sensitive or case-insensitive keys and set the mode immediately after creating the Dictionary.

Case-insensitive comparison also means keys that differ only by letter case cannot be used as separate entries. That can matter when importing identifiers from data sources whose rules are case-sensitive. Test the comparison behavior with representative keys before building a larger process around it.

Check keys and loop through entries

Count reports the number of key-item pairs. Use Keys to obtain the keys and loop over them when you need to process every entry. Declare the loop variable as a Variant, since the returned keys are provided as an array of Variants.

Option Explicit

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 90

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

This prints each key and its item in the Immediate window. If you need to remove every entry, use dict.RemoveAll. Avoid removing entries from a Dictionary while looping over its keys unless you have designed the loop for that change. A safer pattern is to first capture the keys, then remove selected entries in a separate pass, or build a new Dictionary containing the entries you want to keep.

Common errors and practical fixes

Several common problems come from mixing up object assignment and item assignment, or from assuming that a key is already present. Use these checks when troubleshooting:

  • Object variable not set: Confirm that the Dictionary was initialized with Set dict = CreateObject(...) or Set dict = New Scripting.Dictionary before you use it.
  • Duplicate key error: Check dict.Exists(key) before Add, or update the existing item instead.
  • Missing reference or compile error: If you use Scripting.Dictionary in a declaration, check the Microsoft Scripting Runtime reference. Alternatively, use late binding with As Object.
  • Unexpected capitalization behavior: Set CompareMode before adding keys and confirm whether binary or text comparison is appropriate.
  • Wrong use of Set: Use Set for the Dictionary variable and for object variables receiving object items. Do not use it to assign a number or text item.

Also keep in mind that a missing key and a key whose item is blank are different situations. Call Exists before retrieving a key when absence is possible. That makes the intended behavior explicit and helps prevent errors from appearing only when the macro encounters an unusual row of data.

Windows, Mac, and alternatives

Scripting.Dictionary is a Windows COM component and is commonly used with Excel for Microsoft 365 on Windows. Excel for Mac does not provide the same Windows Scripting Runtime in the same way, so code that depends on CreateObject("Scripting.Dictionary") or the Microsoft Scripting Runtime reference may not be portable to Mac. Do not assume that changing from early binding to late binding makes a Windows-only component available on Mac.

If a workbook must run on both platforms, consider whether a built-in VBA Collection, a worksheet range, or parallel arrays are sufficient. A Collection stores keyed items but does not provide the Dictionary’s direct Exists method; you may need error handling or a separate lookup routine. Arrays can be fast for predictable data, but searching them requires your own logic. For cross-platform work, test the chosen design in the actual Excel versions and operating systems your readers use.

Frequently Asked Questions

Do I need Set when creating a VBA Dictionary?
Yes. Assign the Dictionary object to its variable with Set, such as Set dict = CreateObject("Scripting.Dictionary"). You do not use Set to assign ordinary string or numeric items.

How do I add an item to a VBA Dictionary?
Use dict.Add "key", value to add a new key and item. If the key might already exist, use Exists to check first, or assign a replacement item with dict("key") = value.

How do I change a Dictionary value?
For a scalar item, assign the new value with dict("key") = newValue. That creates the item if the key is absent or replaces its item if it is present, so check Exists if those cases need different handling.

How do I get an object stored in a Dictionary?
Assign the retrieved object to an appropriately typed object variable with Set, for example Set ws = dict("source"). The Dictionary stores a reference, not a copy of the object.

Why does Scripting.Dictionary fail on my Mac?
The Scripting Runtime is a Windows COM component and is not a dependable cross-platform choice for Excel VBA. For Mac-compatible workbooks, consider a Collection, arrays, or a platform-neutral lookup design, and test the macro on Mac Excel before distributing it.

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.