Two Keyed Containers
Arrays are great for fixed, indexed data, but when you need to look items up by a text key or grow a list dynamically, VBA offers two containers. The built-in Collection is always available and stores items with optional string keys, but it can't tell you whether a key exists without trapping an error, and it can't retrieve keys back. The Scripting.Dictionary (from the Microsoft Scripting Runtime) is richer: it exposes Exists, can return both Keys and Items as arrays, and lets you overwrite values by key.
Cricket analogy: A Collection is a scorebook where you can add runs but can't easily ask 'has Rohit batted yet?'; a Dictionary is the modern scoring app with an Exists check that instantly confirms whether a player is already at the crease.
The Dictionary Workhorse Pattern
The classic use is aggregation: iterate rows, and for each key check If dict.Exists(key) Then dict(key) = dict(key) + amount Else dict.Add key, amount. Because assigning to a non-existent key with dict(key) = value also creates it, many people simplify counting to dict(key) = dict(key) + 1, which auto-creates missing keys as zero. Keys must be unique; a Dictionary's default CompareMode is BinaryCompare (case-sensitive), but setting dict.CompareMode = vbTextCompare makes 'Apple' and 'apple' the same key.
Cricket analogy: Tallying by key is like a scorer keeping a running total per batsman: each boundary adds to that player's key, so Kohli's tally grows independently while a new batsman's key is created on his first run.
Sub CountByCategory()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare ' case-insensitive keys
Dim data As Variant, i As Long
data = ThisWorkbook.Sheets("Sales").Range("A2:B1000").Value
For i = 1 To UBound(data, 1)
Dim cat As String
cat = CStr(data(i, 1))
If Len(cat) > 0 Then
dict(cat) = dict(cat) + data(i, 2) ' auto-creates missing keys
End If
Next i
' Spill results
Dim k As Variant, r As Long: r = 2
For Each k In dict.Keys
Cells(r, 4).Value = k
Cells(r, 5).Value = dict(k)
r = r + 1
Next k
End SubWhen to Prefer a Collection
The Collection still earns its place. It needs no external reference or CreateObject, so it works everywhere including non-Windows hosts, and its Add method accepts Before/After arguments for ordered insertion. It is a fine choice when you only need an ordered, growable list and don't require key existence tests or key enumeration. If you do need those, either switch to a Dictionary or wrap the Collection's Item call in On Error Resume Next to detect a missing key — though the Dictionary's Exists is far cleaner.
Cricket analogy: A Collection is a trusty net bowler always available for practice, no paperwork needed; you pick it for a simple ordered batting queue, but for quick 'is he in?' checks you call up the Dictionary specialist.
Scripting.Dictionary requires the Microsoft Scripting Runtime (scrrun.dll), present on Windows but not on Mac Office. If your workbook must run on Mac, prefer the Collection or a custom class, since Scripting.Dictionary is unavailable there.
Collection keys are write-only: you can add an item with a key but you cannot read the key back or test existence directly, so relying on error trapping for lookups is fragile. Also, Collection indices are 1-based, while Dictionary items are accessed strictly by key, not position — don't assume dict(1) means the first item.
- Use Collection or Dictionary when you need keyed or dynamically growing storage instead of fixed arrays.
- Collection is always available and supports ordered Add with Before/After, but can't test key existence or return keys.
- Scripting.Dictionary offers Exists, Keys, Items, and value overwrite, making it ideal for aggregation and de-duplication.
- dict(key) = dict(key) + n auto-creates missing keys, giving a concise counting/summing pattern.
- Set CompareMode = vbTextCompare for case-insensitive keys; the default BinaryCompare is case-sensitive.
- Scripting.Dictionary depends on scrrun.dll and is unavailable on Mac Office, where Collection is the fallback.
Practice what you learned
1. Which capability does Scripting.Dictionary have that the built-in Collection lacks?
2. What does dict(key) = dict(key) + 1 do when key is not yet present?
3. How do you make Dictionary keys case-insensitive?
4. Why might you choose a Collection over a Dictionary?
5. What is true about Collection keys?
Was this page helpful?
You May Also Like
Reading and Writing Cell Data
How to read from and write to worksheet cells in VBA using the Range and Cells objects, and how to move data efficiently between the sheet and VBA arrays.
Regular Expressions in VBA
Using the VBScript RegExp object to match, extract, and replace text patterns in VBA, including groups, global matching, and common gotchas.
VBA and ADO Database Access
Using ActiveX Data Objects (ADO) from VBA to connect to databases, run parameterized queries, and read results into a worksheet or recordset.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics