Standard VBA arrays manage data using numeric indices, such as myArray(0). However, there are many situations where you want to handle data using strings as keys—for example, retrieving a “Price” using a “Product Name” as the key.
This mechanism of managing data in “Key and Value pairs” is called an associative array. In VBA, using the Scripting.Dictionary object is the most powerful and common way to implement this.
This article provides a comprehensive explanation of the Dictionary object, from basic usage to practical applications.
[Preparation] Reference Setting
To use the Dictionary object, you need to enable a reference.
- In the VBA Editor, go to Tools > References.
- Check “Microsoft Scripting Runtime”. This allows you to declare variables as
Scripting.Dictionary, enabling IntelliSense (auto-completion).
Basic Usage of the Dictionary Object
1. Creating a Dictionary
' Declare and create a new instance of the Dictionary object
Dim productPrices As Scripting.Dictionary
Set productPrices = New Scripting.Dictionary
2. Adding Items (.Add)
Use the .Add method to pair a “Key” with its corresponding “Item” (Value). Keys must be unique.
' .Add Key, Item
productPrices.Add "Item A", 1200
productPrices.Add "Item B", 850
productPrices.Add "Item C", 2100
3. Retrieving Values
Specify the key to get the corresponding value.
' Get the price for "Item B"
Dim price As Long
price = productPrices("Item B") ' Or productPrices.Item("Item B")
MsgBox "Price of Item B: " & price & " yen" ' Output: 850 yen
4. Checking if a Key Exists (.Exists)
Returns True or False. This is essential to check before calling .Add to avoid errors.
If productPrices.Exists("Item D") Then
MsgBox "Item D is already registered."
Else
productPrices.Add "Item D", 980
MsgBox "Item D has been added."
End If
5. Getting the Item Count (.Count)
MsgBox "Total number of items: " & productPrices.Count
Retrieving Lists of Keys and Items
6. Getting All Keys (.Keys)
The .Keys method returns an array containing all keys.
Dim allKeys As Variant
allKeys = productPrices.Keys ' Result: ("Item A", "Item B", "Item C", "Item D")
MsgBox "Product List: " & Join(allKeys, ", ")
7. Getting All Values (.Items)
The .Items method returns an array containing all values.
Dim allPrices As Variant
allPrices = productPrices.Items ' Result: (1200, 850, 2100, 980)
Removing Items
8. Removing a Specific Item (.Remove)
productPrices.Remove "Item C"
9. Removing All Items (.RemoveAll)
productPrices.RemoveAll
Practical Example: Creating a Unique List
The unique-key constraint of a Dictionary is perfect for removing duplicates from a list.
' Create a unique list from values in Column D
Sub CreateUniqueList()
Dim uniqueList As Scripting.Dictionary
Set uniqueList = New Scripting.Dictionary
Dim cell As Range
' Loop from D2 to the last row in Column D
For Each cell In ActiveSheet.Range("D2", ActiveSheet.Cells(Rows.Count, "D").End(xlUp))
' If the value hasn't been registered as a key yet, add it
If Not uniqueList.Exists(cell.Value) Then
uniqueList.Add cell.Value, "" ' Value is not needed, so we use an empty string
End If
Next cell
MsgBox "Unique List:" & vbCrLf & Join(uniqueList.Keys, vbCrLf)
End Sub
Summary
The Scripting.Dictionary object allows for more intuitive and faster data access than standard arrays by using key-value pairs. Its ability to simplify duplicate-check logic makes it an incredibly powerful tool for real-world Excel automation.
