Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 37 ยท Objects and Collections

Collections and the Dictionary Object in VBA

Store and look up data with the VBA Collection and Scripting.Dictionary: add, remove, key lookups, Exists, For Each, counting duplicates and choosing the right container.

Arrays have fixed positions and no names. When you need to grow a list as you go, or find an item by name, VBA offers two container objects: the built-in Collection and the Dictionary from the Scripting library. Both are objects, so you create them with New or CreateObject and use Set.

The Collection object

VB
Dim names As Collection
Set names = New Collection

names.Add "Ada"
names.Add "Grace"
names.Add "Linus"

Debug.Print names.Count        ' 3
Debug.Print names(1)           ' Ada (1-based)
Debug.Print names.Item(2)      ' Grace
names.Remove 1                 ' removes Ada, the rest move up

Properties and methods: Add(item, [key], [before], [after]), Remove(index or key), Item(index or key) and Count. There is no Clear; to empty one, Set names = New Collection.

Keys

Give an item a key to fetch it by name:

VB
Dim ages As New Collection
ages.Add 36, "Ada"
ages.Add 85, "Grace"
Debug.Print ages("Grace")      ' 85

Collection keys are case-insensitive and must be unique: adding a duplicate raises run-time error 457. Asking for a missing key raises error 5. There is no built-in "does this key exist" test, so wrap the lookup:

VB
Function HasKey(col As Collection, key As String) As Boolean
    On Error Resume Next
    Dim tmp As Variant
    tmp = col(key)
    HasKey = (Err.Number = 0)
    Err.Clear
End Function

Limitations: items can't be modified in place (remove and re-add), keys can't be read back, and you can't list keys. If you need these, use a Dictionary.

Looping

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

Dim i As Long
For i = 1 To names.Count
    Debug.Print i, names(i)
Next i

Remove items while looping backwards (For i = names.Count To 1 Step -1), otherwise indexes shift under you.

The Dictionary object

Scripting.Dictionary stores key-item pairs with fast lookup and the features Collection lacks. It lives in the Microsoft Scripting Runtime library (Windows only; it is not available on Mac Office).

VB
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")     ' late binding

For early binding add Tools > References > Microsoft Scripting Runtime and write Dim d As New Scripting.Dictionary.

VB
d.Add "red", 3
d.Add "blue", 5
d("green") = 7                  ' assigning to a new key adds it
d("red") = 4                    ' existing key: replaces the item

Debug.Print d.Count             ' 3
Debug.Print d.Exists("blue")    ' True
Debug.Print d("blue")           ' 5
d.Remove "blue"
Dim k As Variant
For Each k In d.Keys
    Debug.Print k, d(k)
Next k

Facts: Add with an existing key raises error 457, but d(key) = value never fails. Reading a missing key (d("pink")) does not raise an error; it silently adds the key with an empty value, so always test Exists first. Set d.CompareMode = vbTextCompare (before adding items) to make keys case-insensitive; the default is case sensitive. Other members: Keys, Items, RemoveAll.

Pick the container

Array Fixed size, fastest, index only.
Collection Grows, optional keys, built in.
Dictionary Key lookup, Exists, Keys, Windows only.
1 / 2

Example: count words

A dictionary counts anything in one pass:

VB
Function CountWords(ByVal text As String) As Object
    Dim d As Object, w As Variant
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare
    For Each w In Split(LCase(text), " ")
        If Len(w) > 0 Then
            If d.Exists(w) Then
                d(w) = d(w) + 1
            Else
                d.Add w, 1
            End If
        End If
    Next w
    Set CountWords = d
End Function

For "the cat and the hat and the bat" the result is the 3, cat 1, and 2, hat 1, bat 1. The same approach finds unique values in a column of cells: add each as a key and read d.Keys.

Stand-in: Dictionary and Collection in JavaScript

The JavaScript stand-in (not VBA) uses a Map for the Dictionary and an array for the 1-based Collection.

JS Stand-in: count words and unique values

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Collection or Dictionary?

Need Use
Simple growing list Collection
Look up by key, test existence Dictionary
Read all keys, change items Dictionary
Code that must run on Mac Collection (or arrays)
Speed on large counts and lookups Dictionary

You can also create your own typed collections later with class modules.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How do you create a new Collection?

    Collection is a class; instantiate it with New and assign with Set.
  2. 2. What happens when you read d("pink") for a missing key in a Dictionary?

    Reading a missing key creates it, so test d.Exists(key) first.
  3. 3. How are Collection indexes numbered?

    Collection positions start at 1 (Option Base does not affect them).
  4. 4. Which container is NOT available on Mac Office?

    The Scripting runtime is a Windows library.
  5. 5. Which approach finds unique values in a column?

    Keys are unique, so adding values as keys removes duplicates.

Summary

  • Collection: built in, 1-based, grows, optional unique keys, no key tests.
  • Scripting.Dictionary: key/item pairs with Exists, Keys, Items, Remove, case options.
  • Test Exists before reading a Dictionary key.
  • Choose arrays for fixed lists, Collections for simple lists, Dictionaries for lookups and counting.

Next: Excel VBA: Workbooks and Worksheets