📎 This article includes 1 downloadable practice file ↓

Arrays (lesson 9) are great when you know where things are: row 5, column 3. But many everyday questions are about keys instead: “what’s the total for customer C-104?”, “which product codes appear more than once?”, “give me each region only once”. For those, VBA has two containers that store values under a name: the Collection and the Dictionary.

Collection: a simple growing list

Dim names As New Collection
names.Add "Asha"
names.Add "Ravi"
names.Add "Meena"
Debug.Print names.Count       ' 3
Debug.Print names(2)          ' Ravi (Collections start at 1)

Dim n As Variant
For Each n In names
    Debug.Print n
Next n

Collections grow without ReDim, which makes them handy for “collect everything that matches”. Their limits: you can’t easily check whether a key exists, you can’t change an item in place, and you can’t list the keys.

Dictionary: key → value, and it can answer “do you have this?”

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare        ' "delhi" and "Delhi" are the same key

d("Delhi") = 120
d("Mumbai") = 95
Debug.Print d("Delhi")               ' 120
Debug.Print d.Exists("Pune")         ' False
Debug.Print d.Count                  ' 2

Assigning d(key) = value adds the key if it’s new and overwrites it if it exists. That one behaviour powers almost every trick below.

1. Unique list in one pass

Sub UniqueRegions()
    Dim d As Object, data As Variant, r As Long
    Set d = CreateObject("Scripting.Dictionary"): d.CompareMode = vbTextCompare
    data = Worksheets("Sales").Range("B2:B50001").Value
    For r = 1 To UBound(data, 1)
        If Len(data(r, 1)) > 0 Then d(Trim$(data(r, 1))) = Empty
    Next r
    Worksheets("Out").Range("A2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys)
End Sub

50,000 rows, under a second. d.Keys returns a 1-D array, so Transpose turns it into a column for writing back.

2. Total and count by key (a pivot in code)

Sub TotalsByCustomer()
    Dim tot As Object, cnt As Object, data As Variant, r As Long, k As Variant, out() As Variant, i As Long
    Set tot = CreateObject("Scripting.Dictionary"): Set cnt = CreateObject("Scripting.Dictionary")
    data = Worksheets("Sales").Range("A2:E50001").Value          ' A = customer, E = amount
    For r = 1 To UBound(data, 1)
        k = data(r, 1)
        If Len(k) > 0 Then
            tot(k) = tot(k) + data(r, 5)                          ' missing key starts as Empty = 0
            cnt(k) = cnt(k) + 1
        End If
    Next r
    ReDim out(1 To tot.Count, 1 To 3)
    For Each k In tot.Keys
        i = i + 1: out(i, 1) = k: out(i, 2) = cnt(k): out(i, 3) = tot(k)
    Next k
    Worksheets("Out").Range("D2").Resize(tot.Count, 3).Value = out
End Sub

3. Look-ups without VLOOKUP

Load the price list into a dictionary once, then look up 50,000 order lines from memory:

Set price = CreateObject("Scripting.Dictionary")
pl = Worksheets("Prices").Range("A2:B500").Value
For r = 1 To UBound(pl, 1): price(pl(r, 1)) = pl(r, 2): Next r

For r = 1 To UBound(orders, 1)
    If price.Exists(orders(r, 2)) Then
        result(r, 1) = orders(r, 3) * price(orders(r, 2))
    Else
        result(r, 1) = "No price"
    End If
Next r

4. Find duplicates

For r = 1 To UBound(data, 1)
    k = data(r, 1)
    If seen.Exists(k) Then
        dupes(k) = dupes(k) & ", " & (r + 1)          ' remember the sheet rows
    Else
        seen(k) = r + 1
    End If
Next r

Collection or Dictionary?

Where people go wrong

• “Delhi” and “Delhi ” counted separately: trim keys before adding.
• Numbers vs text keys: 101 (number) and "101" (text) are different keys. Convert with CStr() when codes come from different sources.
• CompareMode error: it can only be set while the dictionary is empty — set it straight after creating it.

Practice

The workbook has 50,000 sales rows and buttons for unique lists, totals by customer, a price look-up, a duplicate finder and a Collection example — each with a timer so you can compare against formulas.

Last lesson: files and folders, and a final project that pulls the whole course together.