A Collection is an ordered list of items; a Dictionary (from the Scripting runtime) is a key-value store with fast Exists checks. Dictionaries replace slow lookups and duplicate checks in loops.

Code

Set d = CreateObject("Scripting.Dictionary")
d("West") = 120
If d.Exists("West") Then ...

• Dictionary basics

  Set d = CreateObject("Scripting.Dictionary") d("West") = 120 If d.Exists("West") Then MsgBox d("West") → A keyed store with an existence test

  Late binding needs no reference.

• Group by key

  For i = 2 To lastRow k = Cells(i, 1).Value d(k) = d(k) + Cells(i, 2).Value Next → Sums by key in one pass, a SUMIF in memory

  Group totals.

• Collection

  Dim c As New Collection c.Add "a": c.Add "b" MsgBox c.Count → A simple list

  Collections are 1-based.

• Set a reference to Microsoft Scripting Runtime for early binding and autocomplete.
• d.Keys and d.Items return arrays for writing to a sheet.
• CompareMode = vbTextCompare makes keys case-insensitive; set it before adding.

• Reading d(k) for a missing key silently creates it.
• Collections cannot test for a key without an error handler.