VBA Dictionary in Excel — Lookups, Dedup & Grouping (6 Examples)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-05

TL;DR — A Dictionary stores key → value pairs and finds any value by its key instantly — no looping. Create one, add to it, look things up:

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

    dict("AAPL") = 182.5          ' add or update by key
    dict("MSFT") = 410.2

    If dict.Exists("AAPL") Then    ' check before you read
        MsgBox "AAPL = " & dict("AAPL")
    End If
End Sub

Where an array makes you scan every item to find one, a Dictionary jumps straight to it. That's why analysts use it for two things above all: removing duplicates and counting/grouping. Both are below.

Why a Dictionary — and what a key is

A key is a unique label; a value is whatever you store under it. Keys must be unique, so the Dictionary is perfect whenever "have I seen this before?" or "how many of each?" is the question. Looking up by key is O(1) — effectively instant — no matter how many entries it holds.

Example 1 — Setup: late binding vs early binding

There are two ways to create a Dictionary, and mixing them up causes the dreaded "User-defined type not defined" error.

' ── Late binding (no setup, works everywhere) ──
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

' ── Early binding (faster + IntelliSense) ──
' First: Tools ▸ References ▸ tick "Microsoft Scripting Runtime"
Dim dict As New Scripting.Dictionary

Which to use? Late binding for code you'll share (nothing to configure on the other machine). Early binding while developing, for autocomplete. This guide uses late binding so every example runs as-is.

Example 2 — Add, update, and the "silent add" trick

There are two ways to put data in, and the difference matters:

dict.Add "AAPL", 182.5      ' explicit — ERRORS if "AAPL" already exists
dict("MSFT") = 410.2        ' assignment — adds if new, updates if it exists

That second form is the silent add: assigning to a key that doesn't exist creates it. It never errors on duplicates, which is exactly what makes the next two patterns so clean.

Example 3 — `.Exists` before you read (avoid phantom keys)

Reading a missing key with dict(key) will silently create it (empty). To test membership without adding anything, always use .Exists:

Dim sku As String
sku = Range("A2").Value

If dict.Exists(sku) Then
    Range("B2").Value = dict(sku)        ' safe — key is really there
Else
    Range("B2").Value = "not found"
End If

Example 4 — Build a unique list (dedup in one pass) ⭐

The classic job: column A has thousands of rows with repeats; you want each value once. The silent add collapses duplicates automatically:

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

    Dim cell As Range
    For Each cell In Range("A2:A10000")
        If cell.Value <> "" Then dict(cell.Value) = 1   ' dup keys overwrite, list stays unique
    Next cell

    ' dict.Keys is now the de-duplicated list — write it down column D:
    Range("D2").Resize(dict.Count).Value = Application.Transpose(dict.Keys)
    MsgBox dict.Count & " unique values"
End Sub

No nested loops, no sorting, no helper column — and it stays fast on 100,000 rows.

Example 5 — Count / group (a frequency tally) ⭐

Want "how many orders per region"? Add one to the value each time you see the key. The silent add starts a missing key at 0, so the very first + 1 works:

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

    Dim cell As Range
    For Each cell In Range("A2:A10000")          ' column A = Region
        dict(cell.Value) = dict(cell.Value) + 1  ' first sight: 0 + 1
    Next cell

    ' Spill the tally to columns F:G
    Dim k As Variant, r As Long
    r = 2
    For Each k In dict.Keys
        Cells(r, 6).Value = k                    ' F = region
        Cells(r, 7).Value = dict(k)              ' G = count
        r = r + 1
    Next k
End Sub

This is a PivotTable's worth of grouping in 12 lines — and you control exactly what happens.

Example 6 — Loop keys and items together

.Keys and .Items each return a 0-based array, lined up by position:

Dim keys As Variant, items As Variant, i As Long
keys = dict.Keys
items = dict.Items
For i = 0 To dict.Count - 1
    Debug.Print keys(i) & " => " & items(i)
Next i

Need case-insensitive keys (so "AAPL" and "aapl" are the same)? Set dict.CompareMode = vbTextCompare before adding anything.

Dictionary vs Collection — which to use

VBA also has a built-in Collection. Reach for a Dictionary when you need keyed access; a Collection is fine for a plain ordered list.

Need	Dictionary	Collection
Check if a key exists	`.Exists(key)` ✅	none — must trap an error
Update a value in place	`dict(key) = x` ✅	remove + re-add
Get all keys / all values	`.Keys` / `.Items` ✅	not available
Count items	`.Count` ✅	`.Count` ✅
Setup required	`Scripting.Dictionary` reference or `CreateObject`	built-in, none

Common Dictionary mistakes (and the fix)

Symptom	Cause	Fix
"User-defined type not defined"	`As New Scripting.Dictionary` without the reference	Add the reference, or use late binding `CreateObject(...)`
"This key is already associated…"	`.Add` on a key that exists	Use `dict(key) = value` to add-or-update
Membership test keeps growing the dict	`If dict(key) <> "" Then` creates the key	Test with `dict.Exists(key)` instead
`Transpose` of keys errors / truncates	Over ~65,536 keys hit Transpose's limit	Loop the keys and write them one by one
"aapl" and "AAPL" counted separately	Default key matching is case-sensitive	Set `dict.CompareMode = vbTextCompare` before adding

Skip the binding and boilerplate — just ask for the result

Dictionaries are the right tool for dedup and grouping — but early-vs-late binding, .Exists guards, and Transpose limits are a lot of ceremony for "give me the unique regions and their counts." ExcelMaster Agent lets you say exactly that in plain English — "list each unique region in column A and how many rows it has" — and it builds the dictionary, the guard, and the output for you. Try it free →

FAQ

What is a Dictionary in VBA? A Dictionary (Scripting.Dictionary) stores key → value pairs and retrieves any value by its key instantly, without looping. Keys are unique, which makes it ideal for de-duplicating data and counting or grouping.

How do I create a Dictionary in Excel VBA? Late binding needs no setup: Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary"). For early binding and IntelliSense, tick Microsoft Scripting Runtime under Tools ▸ References, then Dim dict As New Scripting.Dictionary.

What is the difference between a Dictionary and a Collection in VBA? A Dictionary has .Exists, lets you update values in place, and exposes .Keys/.Items. A Collection is a simpler built-in ordered list with no key-existence check and no way to read its keys back. Use a Dictionary whenever you need keyed lookups.

How do I check if a key exists in a VBA Dictionary? Use dict.Exists(key), which returns True/False without changing the dictionary. Avoid If dict(key)…, because reading a missing key silently creates it.

How do I get a unique list with a VBA Dictionary? Loop the source range and assign dict(cell.Value) = 1. Duplicate keys overwrite each other, so dict.Keys ends up as the de-duplicated list — then write it back with Range("D2").Resize(dict.Count).Value = Application.Transpose(dict.Keys).