VBA Dictionary: Fast Lookups and Unique Lists in Your Macros

📎 This article includes 1 downloadable practice file ↓

A Dictionary stores key → value pairs and finds any key instantly, even among 100,000. It’s the VBA tool for “total by customer”, “unique list of regions” and “is this code already seen?” — jobs that are slow with loops and VLOOKUP.

Create one

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")   ' late binding: no reference needed
d.CompareMode = vbTextCompare                   ' "North" and "north" are the same key

For autocomplete, add Tools › References › Microsoft Scripting Runtime and declare Dim d As New Scripting.Dictionary (early binding).

Add, update, read

d("North") = 100           ' adds or overwrites
d.Add "South", 50          ' errors if the key exists
If d.Exists("East") Then Debug.Print d("East")
d.Remove "South"
Debug.Print d.Count

Total by key (SUMIF in VBA)

Dim data, r As Long
data = Range("A2:D" & Cells(Rows.Count, 1).End(xlUp).Row).Value   ' read once
For r = 1 To UBound(data)
    d(Trim(data(r, 2))) = d(Trim(data(r, 2))) + data(r, 4)          ' region in col B, amount in D
Next r
Range("F2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys)
Range("G2").Resize(d.Count, 1).Value = Application.Transpose(d.Items)

Reading the range into an array and writing results once is what makes this fast: 100,000 rows in well under a second.

Unique list

Add each value as a key with any item (d(x) = 1); d.Keys is the distinct list, in first-seen order.

Where people go wrong

The practice workbook has 5,000 orders with messy region names and two macros, one correct and one showing what goes wrong without cleaning.