Cat On A Spreadsheet

Multi-Level Dictionaries in Excel VBA

In the previous post, we explored how VBA dictionaries can simplify lookups, eliminate nested loops, and boost performance. But what if you want to group data across multiple dimensions? For example:

 

  • Region → Product Category → Sales Total

  • Department → Employee → Hours Worked

  • Year → Month → Sales Rep → Revenue

 

This is where multi-level (nested) dictionaries come in.

 

In this post, you’ll learn:

 

  • How to build and navigate multi-level dictionaries

  • Real-world use cases for grouping and aggregating

  • How to safely manage missing keys and create structure dynamically

 

🧱 Why Use Nested Dictionaries?

 

Multi-level dictionaries let you store and access hierarchical data without relying on extra arrays or database queries. Instead of flattening your data, you can build a tree-like structure:

dict("Region1")("ProductA") = 1000
dict("Region1")("ProductB") = 2000
dict("Region2")("ProductA") = 1500

 

This is similar to how JSON or Python dictionaries work—and it's possible in VBA too.

 

🏗️ Step-by-Step: How to Build a Multi-Level Dictionary

 

Let’s say you have data in this format:

 

Region                                                 Product                                                 Sales

North                                                       A                                                                 100

North                                                       B                                                                 150

South                                                       A                                                                 200

 

We want to group by Region → Product → Total Sales.

 

Step 1: Declare the main dictionary

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

 

Step 2: Loop through your data

Dim region As String, product As String
Dim sales As Double
Dim subDict As Object
For i = 2 To lastRow
     region = Cells(i, 1).Value
     product = Cells(i, 2).Value
     sales = Cells(i, 3).Value
     ' Check if region exists
     If Not dict.Exists(region) Then
          Set dict(region) = CreateObject("Scripting.Dictionary")
     End If
     Set subDict = dict(region)
     ' Add or update product sales
     If subDict.Exists(product) Then
          subDict(product) = subDict(product) + sales
     Else
          subDict.Add product, sales
     End If
Next i

 

Now your structure looks like this:

dict("North")("A") = 100
dict("North")("B") = 150
dict("South")("A") = 200

 

🔍 Accessing Nested Dictionary Data

 

To read from this structure:

Dim r As Variant, p As Variant
For Each r In dict.Keys
     Debug.Print "Region: " & r
     For Each p In dict(r).Keys
          Debug.Print " Product: " & p & ", Sales: " & dict(r)(p)
     Next p
Next r

 

🔁 Real-World Use Case: Year → Month → Total Sales

 

This time, we’ll group by two levels of time:

Dim yearDict As Object, monthDict As Object
Set yearDict = CreateObject("Scripting.Dictionary")
For i = 2 To lastRow
     y = Year(Cells(i, 1).Value)
     m = Month(Cells(i, 1).Value)
     val = Cells(i, 2).Value
     ' Ensure year dictionary exists
     If Not yearDict.Exists(y) Then
          Set yearDict(y) = CreateObject("Scripting.Dictionary")
     End If
     et monthDict = yearDict(y)
     ' Add to month total
     If monthDict.Exists(m) Then
          monthDict(m) = monthDict(m) + val
     Else
          monthDict.Add m, val
     End If
Next i

 

📦 Optional: Reusable Function to Create Nested Dictionary

 

To make your code cleaner, abstract the creation of nested dictionaries into a helper function:

Function GetOrCreateNestedDict(parentDict As Object, key As Variant) As Object
If Not parentDict.Exists(key) Then
Set parentDict(key) = CreateObject("Scripting.Dictionary")
End If
Set GetOrCreateNestedDict = parentDict(key)
End Function

 

Usage:

Set subDict = GetOrCreateNestedDict(dict, region)

 

📄 Outputting Multi-Level Data Back to Excel

 

Let’s say you want to print the values to the worksheet:

Dim r As Variant, p As Variant
Dim rowNum As Long: rowNum = 2
For Each r In dict.Keys
     For Each p In dict(r).Keys
          Cells(rowNum, 1).Value = r
          Cells(rowNum, 2).Value = p
          Cells(rowNum, 3).Value = dict(r)(p)
          rowNum = rowNum + 1
     Next p
Next r

 

🔄 Going Deeper: 3+ Levels of Nesting

 

You can keep nesting dictionaries inside dictionaries for even deeper hierarchies:

If Not dict.Exists(year) Then
     Set dict(year) = CreateObject("Scripting.Dictionary")
End If
If Not dict(year).Exists(month) Then
     Set dict(year)(month) = CreateObject("Scripting.Dictionary")
End If
If dict(year)(month).Exists(product) Then
     dict(year)(month)(product) = dict(year)(month)(product) + val
Else
     dict(year)(month).Add product, val
End If

 

🔚 Summary: When and Why to Use Multi-Level Dictionaries

 

✅ Best for:

  • Grouping and aggregating large datasets

  • Creating multi-dimensional reports

  • Replacing nested loops and fragile array logic

 

❌ Not ideal for:

  • Sorting (dictionaries are unordered)

  • Writing back to Excel in a pre-defined structure without flattening

 

 

a close up of a book with some type of text
19 May 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet