VBA Nested Dictionary: Create, Read, and Update Data

VBA & macros · Updated November 18, 2026

In VBA, a nested dictionary is a Scripting.Dictionary whose item is another dictionary. Create an outer dictionary, create an inner dictionary for each group, and add that inner object under an outer key; retrieve it with Set before reading or changing its contents. The examples below target Excel for Microsoft 365 on Windows.

What a nested dictionary is

A dictionary stores key–item pairs: a key identifies an item, and the key must be unique within that dictionary. A nested dictionary uses an object as an item, so one lookup leads to another. For example, an outer dictionary could use region names as keys, with each region’s inner dictionary holding product names and sales totals.

This structure is useful when data has multiple levels of grouping or when you need to look up a value by more than one label. Instead of searching every row in a worksheet, code can look up a region and then a product. A third dictionary level is possible too, such as region → product → month, though additional nesting can make code harder to follow.

  • Outer key: a group, such as North.
  • Inner key: a member of that group, such as Apples.
  • Inner item: the associated value, such as a sales total.

A dictionary is not the same as a two-dimensional worksheet range. It is an in-memory object used by VBA; it does not automatically read from or write to cells, and it is not a worksheet formula.

Availability and setup in Excel

On Windows, the examples use the Microsoft Scripting Runtime dictionary object. The simplest setup is late binding: call CreateObject("Scripting.Dictionary") and declare dictionary variables as Object. This avoids setting a reference in each workbook and is convenient for sharing a macro with other Windows users.

To run the example, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. In Excel for Microsoft 365 on Windows, run a macro from the editor with F5, or return to Excel and use Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

Early binding is another option: in the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime, then declare variables as Scripting.Dictionary and create them with New Scripting.Dictionary. Early binding provides editor assistance and compile-time checking, but another computer may not have the same reference available. Late binding is usually simpler for a small shared workbook.

Mac note: Scripting.Dictionary is a Windows COM automation component. A Windows CreateObject("Scripting.Dictionary") example is not portable to Mac Excel, whose VBA environment does not provide Windows COM in the same way. For a workbook that must run on both platforms, consider a Collection, arrays, or a custom VBA class, and verify the chosen implementation on each platform. No Windows API declarations are used here, so the sample does not need a PtrSafe declaration.

Create a nested dictionary

This complete example builds a dictionary of sales by region and product. It uses late binding, sets text comparison before adding keys, and prints one result to the Immediate Window. In the Visual Basic Editor, open that window with Ctrl+G.

Option Explicit

Public Sub DemoNestedDictionary()
    Dim salesByRegion As Object
    Dim salesByProduct As Object

    ' Create the outer dictionary and set comparison before adding keys.
    Set salesByRegion = CreateObject("Scripting.Dictionary")
    salesByRegion.CompareMode = vbTextCompare

    ' Build the North group's inner dictionary.
    Set salesByProduct = CreateObject("Scripting.Dictionary")
    salesByProduct.CompareMode = vbTextCompare
    salesByProduct.Add "Apples", 120
    salesByProduct.Add "Pears", 85
    salesByRegion.Add "North", salesByProduct

    ' Build a separate inner dictionary for the South group.
    Set salesByProduct = CreateObject("Scripting.Dictionary")
    salesByProduct.CompareMode = vbTextCompare
    salesByProduct.Add "Apples", 95
    salesByProduct.Add "Pears", 110
    salesByRegion.Add "South", salesByProduct

    ' Retrieve the North dictionary, then its Apples value.
    Set salesByProduct = salesByRegion.Item("North")
    Debug.Print salesByProduct.Item("Apples")
End Sub

The Immediate Window displays 120. Each call to CreateObject creates a distinct dictionary. The outer dictionary holds a reference to each inner dictionary; the variable salesByProduct is reused, but the two inner objects remain separate. In contrast, adding the same inner object under two outer keys would make both keys refer to that same object, so a change through either path would be visible through the other.

Before running any macro, save a copy of the workbook. This demonstration only prints a value, but macros that change cells or workbook structure cannot be undone with Ctrl+Z.

Read, test, and update values

To read a value, first retrieve the inner dictionary as an object, then retrieve the desired item. The Set keyword matters because the inner dictionary is an object. For example, after the sample has built its dictionaries, this pattern retrieves North’s Pears value:

Dim products As Object
Dim pearSales As Variant

If salesByRegion.Exists("North") Then
    Set products = salesByRegion.Item("North")
    If products.Exists("Pears") Then
        pearSales = products.Item("Pears")
        Debug.Print pearSales
    End If
End If

The snippet assumes salesByRegion has already been declared and populated as in the preceding procedure. A dictionary’s Exists method checks only keys at that level. Check the outer key before retrieving its inner dictionary, then check the inner key before reading its item. This avoids treating a missing group as if it contained the requested product.

To change an existing value, assign to the item for that key. With the inner dictionary in products, products.Item("Pears") = 90 replaces the earlier value. To add a new product, use products.Add "Plums", 40. Check Exists first if the key might already be present: Add raises an error when a key is already in that dictionary. The inner dictionary’s count reports its direct entries, not the total number of entries in the whole nested structure.

Loop through every level

Use a loop for the outer keys and then a second loop for each inner dictionary. The following procedure is complete when placed in the same module as the example; it assumes the built dictionary is passed to it.

Public Sub PrintSales(ByVal salesByRegion As Object)
    Dim region As Variant
    Dim product As Variant
    Dim products As Object

    For Each region In salesByRegion.Keys
        Set products = salesByRegion.Item(region)
        For Each product In products.Keys
            Debug.Print region, product, products.Item(product)
        Next product
    Next region
End Sub

For the sample data, this prints one line for each region and product, including North / Apples / 120. The order of dictionary entries should not be treated as a guaranteed sort order. If output must be alphabetical or follow a particular business order, collect the keys and sort or arrange them separately before writing results.

For a large worksheet, it is often more efficient to read a contiguous range into a VBA array, build the dictionaries from the array, and write results back in a batch. Repeatedly reading individual cells can be slower because each access crosses between VBA and Excel. A dictionary is most helpful when the code performs many lookups or needs to group irregular data; it is not automatically faster or simpler for every small range.

Common errors and how to avoid them

  • Object variable not set: create the dictionary with Set variable = CreateObject(...) before using it. Use Set when assigning one object variable to another or retrieving an inner dictionary.
  • Duplicate-key error: Add cannot add a key that already exists at that level. Use Exists to decide whether to update the item or add a new one.
  • Wrong comparison behavior: the default text comparison is binary, so differently cased text keys may be treated as different keys. Set CompareMode = vbTextCompare before adding entries if case-insensitive matching is wanted. Changing the mode after entries exist can raise an error.
  • Unexpected missing-value behavior: avoid reading a possibly absent key through Item without checking it. The dictionary’s item property can create a missing key with an empty value, which may hide a spelling or data problem.
  • Runtime error 429: Windows may be unable to create the requested automation object. Check that the code is running in a supported Windows Office environment and that the object name is spelled correctly.
  • Only one group changes: if you intend independent inner dictionaries, create a new one for each outer key. Reusing one inner object for multiple keys shares its contents.

Use consistent key types as well. A key that looks numeric but is stored as text may not match a numeric key. When keys come from worksheet cells, trim accidental spaces and normalize case or data types as appropriate before adding and looking them up.

When to choose a dictionary or an alternative

Nested dictionaries suit data with variable group sizes, sparse combinations, and frequent exact lookups. For example, not every region may sell every product, so the structure need only contain combinations that exist. They are also handy for counting, deduplicating, and grouping values in memory.

Other structures can be clearer in different situations:

  • Two-dimensional arrays: a good fit for a dense, fixed-size grid where rows and columns have known positions.
  • Collections: useful for ordered groups of objects, but they do not provide the same built-in Exists key-checking pattern as a dictionary.
  • Custom classes: useful when each record has named fields, validation rules, or behavior, though they require more setup.
  • Worksheet tables or Power Query: often preferable when the data should remain visible, refresh from sources, or be maintained without editing VBA.

A common misconception is that a nested dictionary automatically preserves the layout or order of the source table. It does not: it stores key–item associations in memory. If users need a report, write the values to a worksheet range and apply an explicit ordering and layout.

Frequently Asked Questions

Can a VBA dictionary contain another dictionary?
Yes. Add an inner Scripting.Dictionary object as an item in an outer dictionary, then retrieve it with Set. Each inner dictionary can have its own keys and values.

Do I need to add a reference to use Scripting.Dictionary?
No, not with late binding using CreateObject("Scripting.Dictionary") and variables declared as Object. Early binding requires selecting Microsoft Scripting Runtime in Tools > References.

How do I check whether a nested key exists?
Call Exists on the outer dictionary first. If the group exists, retrieve its inner dictionary with Set, then call Exists on that inner dictionary for the next key.

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume Windows code that creates this COM object will work on Mac. For cross-platform VBA, use a suitable alternative such as arrays, collections, or a custom class and confirm its behavior in Mac Excel.

Are nested dictionaries sorted?
No. Do not rely on a particular enumeration order. If a report requires sorted keys, sort them explicitly before displaying or writing the results.

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.