The Scripting Dictionary

Just recently discovered the scripting dictionary which comes as part of the Windows family of operating systems, so unfortunately this can't be levered by VBA on Macs.

Dictionaries are a common enough feature of various languages, but something that VBA lacks natively. The closest VBA has is collections, but these are read-only. You can add or remove items from a collection, but you can't change an item's value. You could use an array, but the index is always a number in an array. In a dictionary, the key (index) can be anything you like, as long as it's unique. The value can be anything too (even objects).

This is great for looping through lists and finding unique values, assigning them to a key and adding the value in a related column. You can even set the item equal to a range that you can do something with later.

This script finds unique values in Column A, uses Union to create a multiple range object encompassing each of the unique values, then colours each of the ranges in a different colour.

Sub foo()
    Dim c As Range, sKey As String, i As Long    
    With CreateObject("Scripting.Dictionary")
        For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
            sKey = c.Value
            If Not .exists(sKey) Then
                Set .Item(sKey) = c.Resize(1, 2)
            Else
                Set .Item(sKey) = Union(.Item(sKey), c.Resize(1, 2))
            End If
        Next
        For i = 0 To .Count - 1
            Debug.Print "Values :" & .Keys()(i) & ", with Range(" & _
            .Items()(i).Address & ") has " & .Items()(i).Cells.Count & " cells"
            .Items()(i).Interior.ColorIndex = 34 + i
        Next i
    End With
End Sub