Cat On A Spreadsheet

Mastering Dictionaries in Excel VBA: The Key to Faster, Cleaner Code

If you're writing Excel VBA code that deals with large datasets, comparisons, or de-duplication, chances are you’ve written your fair share of nested loops. But nested loops are slow, hard to debug, and make your code bloated.

In last week's post I touched upon something that can help with that, namely the Scripting.Dictionary — a powerful object that turns slow, nested logic into lightning-fast, readable code.

In this post, you'll learn:

What a Dictionary is and why it's better than collections or arrays in many cases.
How to use it for lookups, frequency counts, de-duplication, and more.
Real-world examples and performance tips.

🧠 What is a Dictionary?

A Dictionary is a data structure that stores key-value pairs. Think of it like a super-efficient table where you can instantly look up a value based on a unique key — just like using VLOOKUP, but in VBA and lightning fast.

Basic structure:

  Set dict = CreateObject("Scripting.Dictionary")
 
 dict.Add "apple", 3
 
 dict.Add "banana", 5
 
 Debug.Print dict("apple") ' Outputs 3

🚀 Why Use Dictionaries?

The Key-based lookup is much faster than looping through arrays;

Easy existence checking - use .Exists() instead of searching;

Their size is dynamic, there is no need to resize like arrays;

Flexible key types - you can use anything, not just numbers. Strings and even dates are fine.

🔁 Replacing Nested Loops with Dictionaries

Traditional approach:

  For i = 1 To lastRow
 
      For j = 1 To lastRow
 
           If Cells(i, 1).Value = Cells(j, 2).Value Then
 
                ' Match found
 
           End If
 
      Next j
 
 Next i

Dictionary version (much faster):

  Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
 ' First, load values from column B into dictionary
 
 For i = 1 To lastRow
 
      dict(Cells(i, 2).Value) = True
 
 Next i
 
 ' Now check values in column A
 
 For i = 1 To lastRow
 
      If dict.Exists(Cells(i, 1).Value) Then
 
           ' Match found instantly
 
      End If
 
 Next i

This version will run much faster, especially when dealing with larger data sets.

🔄 Frequency Counts with Dictionaries

Want to count how many times each item appears in a column? Easy:

  Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
 For i = 2 To lastRow
 
      item = Cells(i, 1).Value
 
      If dict.Exists(item) Then
 
           dict(item) = dict(item) + 1
 
      Else
 
           dict.Add item, 1
 
      End If
 
 Next i
 
 ' Print results
 
 For Each key In dict.Keys
 
      Debug.Print key & ": " & dict(key)
 
 Next

🧽 De-Duplicating a Range Using a Dictionary

Let’s say you want to extract only unique values from a list:

  Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
 
 Dim data As Variant
 
 data = Range("A2:A1000").Value
 
 For i = 1 To UBound(data, 1)
 
      val = data(i, 1)
 
      If Not dict.Exists(val) Then
 
           dict.Add val, True
 
      End If
 
 Next i
 
 ' Output to column B
 
 i = 1
 
 For Each key In dict.Keys
 
      Cells(i, 2).Value = key
 
      i = i + 1
 
 Next

This is way faster than looping with If Not InStr(...) or comparing against a list.

🧪 Real-World Use Case: Matching Two Lists

Let’s say you have two columns, one with product IDs from sales, and one with the master product list. You want to flag missing IDs in the sales list.

  Set dict = CreateObject("Scripting.Dictionary")
 
 ' Load master list
 
 For i = 2 To masterLastRow
 
      dict(Cells(i, 1).Value) = True
 
 Next i
 
 ' Check for missing sales entries
 
 For i = 2 To salesLastRow
 
      If Not dict.Exists(Cells(i, 2).Value) Then
 
           Cells(i, 3).Value = "Missing"
 
      End If
 
 Next i

💡 Pro Tips

Use Exists() before Add() to avoid errors.
Keys are case-sensitive by default. Use .CompareMode = vbTextCompare to make them case-insensitive.
Combine dictionaries with arrays for extremely fast in-memory processing.

dict.CompareMode = vbTextCompare

🧯 Gotchas and Limitations

Duplicate keys cause an error on .Add. To avoid that, use .Exists() checks before adding or use .Item() to overwrite existing data.

There is no native sorting in dictionaries, but you can use Collection or arrays to sort the keys.

Only keys can be unique, which means that if you need a multi-key structure you should consider using a concatenated key instead - think "Region-Agent-Date".

🎯 Final Thoughts

Dictionaries are one of the most underused and powerful tools in Excel VBA. By learning to use them, you can:

Write cleaner code
Eliminate slow loops
Build more intelligent macros

If you're working with large data sets or repeated lookups, this tool is a must.