To add a key and its associated value to a VBA Dictionary, call the dictionary’s Add method: dict.Add "key", value. For example, dict.Add "North", 125 stores the value 125 under the key North; the key must not already exist.
What a VBA Dictionary Is
A Dictionary is a key-value collection: each unique key identifies one item. It is useful when you want to look up a value by a meaningful label, such as a product code, employee name, category, or invoice number. Instead of finding an item by its position, as you would in an array, you ask for the item associated with its key.
In Excel VBA, the commonly used Dictionary is the Scripting.Dictionary object from the Microsoft Scripting Runtime. It is not a worksheet function or a special VBA language keyword. Your code creates an object, adds entries, and then reads or changes those entries through the object’s properties and methods.
A Dictionary is handy for tasks such as counting repeated values, checking whether a code has already appeared, and matching records in memory. It can also make some repeated lookups more convenient than scanning a worksheet range each time. It does not automatically connect to worksheet cells; your macro must read data from and write results to the workbook.
Basic Add Syntax and Example
The method takes two arguments: the key first and the item (the value) second. This example creates a late-bound Dictionary, adds three entries, and retrieves one value:
Sub AddDictionaryKeys()
Dim dict As Object
' Create a Scripting.Dictionary object.
Set dict = CreateObject("Scripting.Dictionary")
' Add each unique key and its associated item.
dict.Add "North", 125
dict.Add "South", 98
dict.Add "West", 143
' Show the item stored under the North key.
MsgBox dict("North")
End Sub
After these statements, the Dictionary contains three key-item pairs. The message box displays 125 because the item is retrieved using the key North. The key and item can be different data types; for example, a text key can point to a number, a date, or another value. In practical code, choose a stable key that uniquely identifies the record you want to retrieve.
Before running any macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z, so a backup gives you a way to recover if the macro changes data unexpectedly.
Creating the Dictionary: Late and Early Binding
The example uses late binding: Dim dict As Object and CreateObject("Scripting.Dictionary"). This avoids setting a project reference, which can make a workbook easier to share among Windows computers with the relevant scripting component available. The trade-off is that the VBA editor has less information about the object while you write code, so it may not provide the same method and property suggestions as it does for a specifically typed object.
With early binding, you add a reference in the Visual Basic Editor and declare the Dictionary using its library type. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11, choose Tools > References, and select Microsoft Scripting Runtime if it appears in the list. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can offer editor assistance, but another computer may need the same reference available for the project to compile correctly.
Both approaches use the same Add, Exists, Keys, and Items concepts. For a small macro that needs to be portable across Windows installations, late binding is often simpler. For a project maintained in a controlled Windows environment, early binding can make the code easier to explore in the editor.
Adding a Key Only When It Is Missing
Add requires a new key. If the key is already in the Dictionary, VBA raises run-time error 457, which means that the key is already associated with an element. When repeated input is possible, check for the key with Exists before adding it:
Sub AddIfMissing()
Dim dict As Object
Dim keyName As String
Set dict = CreateObject("Scripting.Dictionary")
keyName = "North"
If Not dict.Exists(keyName) Then
dict.Add keyName, 125
End If
End Sub
Exists returns True if the key is present and False otherwise. This pattern avoids an error when duplicate input should simply be ignored. If duplicates should instead be treated as a data problem, leave out the check and let Add expose the duplicate so that you can handle it deliberately.
Do not confuse an existing key with an existing value. Two different keys can point to the same item, and the same key cannot be added twice under the Dictionary’s comparison rules. If the input data has duplicate identifiers, decide whether to keep the first item, replace it, combine the items, or report the duplicate. The appropriate behavior depends on what those identifiers mean.
Adding, Updating, and Retrieving Items
Use Add when you intend to create a new key-item pair and want VBA to signal a duplicate. You can also assign through the Item property, commonly written as dict(key) = value. If the key exists, that assignment replaces its item; if it does not exist, assigning the Item property creates an entry for it. This can be concise, but it can also conceal an unexpected missing key.
For example, when processing a list of category names and totals, you may want to create a total the first time a category appears, then add later amounts to it. The following pattern separates those two cases:
Sub AddOrUpdateTotal()
Dim dict As Object
Dim category As String
Dim amount As Double
Set dict = CreateObject("Scripting.Dictionary")
category = "Office"
amount = 35.5
If dict.Exists(category) Then
dict(category) = dict(category) + amount
Else
dict.Add category, amount
End If
End Sub
Here the first occurrence creates the Office key with a total of 35.5. A later occurrence adds its amount to the stored total. This is a common use of a Dictionary, but the sample handles just one amount; a complete worksheet macro would need to loop through its source rows and read each category and amount.
To retrieve an item, use the key in parentheses, as in dict("Office"). Check Exists first if the key might be absent: requesting an item for a missing key through the Item property can create an entry with an empty item. That behavior can make a simple-looking lookup change the contents of the Dictionary, so existence checks are helpful when a missing key should remain missing.
Key Comparison and Text Case
By default, a Dictionary uses binary comparison for string keys, so keys that differ only by capitalization can be distinct: North and NORTH may be treated as separate keys. If your data should treat capitalization as irrelevant, set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any entries.
Sub AddCaseInsensitiveKeys()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", 125
If dict.Exists("NORTH") Then
MsgBox dict("NORTH")
End If
End Sub
Because this example uses vbTextCompare, the Exists check finds the North key even though the capitalization differs. If you try to add NORTH as a second key after adding North, it will be considered a duplicate. Set the comparison mode before inserting entries; changing it after the Dictionary contains data can raise an error. The default binary comparison is appropriate when capitalization genuinely distinguishes identifiers, such as certain codes or passwords.
Looping Through Keys and Items
After adding entries, you can inspect them with the Dictionary’s Keys and Items properties, or loop over the keys. A key-based loop makes the relationship between a key and its value explicit:
Sub PrintDictionaryEntries()
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key
End Sub
The output appears in the Immediate window in the Visual Basic Editor; open it with Ctrl+G on Windows. Declaring the loop variable as Variant is useful because the Keys property returns an array of keys. For example, the loop prints North with 125 and South with 98. Do not assume that the Dictionary is automatically sorted. If you need a particular order, sort the keys separately or write the results to a range and sort them there.
Common Problems and Practical Checks
- Duplicate-key error: Run-time error 457 usually means the code called
Addwith a key that already exists. Check withExists, or decide explicitly how duplicates should be handled. - Object variable not set: A Dictionary variable declared as an object must be initialized with
Set dict = CreateObject("Scripting.Dictionary")before callingAddor another method. - Unexpected capitalization behavior: Set
CompareModebefore adding keys and choose binary or text comparison according to the identifiers in your data. - Missing key changes the Dictionary: Reading or assigning through
dict(key)can create an entry when that key is absent. UseExistsbefore a lookup when you do not want that side effect. - Reference or creation failure: Early-bound code depends on the Microsoft Scripting Runtime reference being available. Late-bound code avoids the project reference, but still relies on the Scripting.Dictionary component being supported on the computer running the macro.
When a macro reads worksheet data, also check for blank keys and inconsistent values. A blank string can become a key, which may unintentionally combine records that lack an identifier. Trim or normalize source text if spaces should not distinguish keys, and make that choice consistently before calling Add.
Windows, Mac, and Alternatives
Scripting.Dictionary is associated with the Windows scripting runtime and COM. Excel for Microsoft 365 on Windows is the expected environment for the CreateObject("Scripting.Dictionary") examples here. Excel for the web does not run VBA macros. On Mac, the Windows scripting runtime is not generally available in the same way, so this object-creation approach should not be assumed to work; test on the exact Mac Excel version and deployment environment rather than relying on Windows compatibility.
If your VBA solution must run on Mac as well as Windows, consider a VBA Collection, arrays with a search routine, or a custom class-based structure, and verify the design in both environments. A Collection supports keyed items but does not offer the same convenient Exists method, so missing-key handling takes extra care. Arrays can be straightforward for small datasets, while repeated linear searches may become less suitable as the data grows. Choose based on the scale of the data and the platforms your users actually need.
For a Windows-only macro that needs fast, convenient key lookup, a Dictionary is often a practical option. For data that belongs in the workbook and must remain visible to users, a worksheet table may be easier to inspect and maintain. These approaches are not interchangeable: a Dictionary is an in-memory VBA object, whereas a table is stored in the workbook and can be filtered, edited, and reviewed directly.
Frequently Asked Questions
What is the VBA syntax to add a key to a Dictionary?
Use dict.Add key, item, such as dict.Add "North", 125. Create and initialize the Dictionary first, and make sure the key is not already present.
How do I add a key if it does not already exist?
Use If Not dict.Exists(key) Then dict.Add key, item. This prevents a duplicate-key error while leaving an existing item unchanged.
Does assigning dict(key) add a new key?
Yes. Assigning to the Item property with dict(key) = value creates the entry if the key is missing and replaces its item if it is already present. Use Add when you want duplicate keys to be reported instead.
How do I make Dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare after creating the Dictionary and before adding any keys. With this setting, capitalization differences do not create separate text keys.
Why does Add give run-time error 457?
The key being added is already present under the Dictionary’s comparison mode. Check with Exists, change the key if appropriate, or update the existing item instead of calling Add again.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume that the Windows Scripting Runtime object is available in Mac Excel. Test the target Mac setup, or choose a VBA Collection, array, or another cross-platform design if the macro must run on both platforms.
Related
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Bubble Sort: How It Works and When to Use It in ExcelA VBA bubble sort repeatedly compares neighboring values and swaps them when they are out of order, until…
- Excel VBA in VS Code: What Works and What You NeedYes, you can use Visual Studio Code (VS Code) to edit Excel VBA code, but VS Code cannot…
- Excel VBA Rows: Select, Count, and Manage Worksheet RowsIn Excel VBA, Rows refers to worksheet rows or the rows within a specified range. For example, Worksheets("Data").Rows(5)…
- VBA Code to Sort Data in Excel: Examples and TipsTo sort Excel data with VBA, define the complete data range, add the column or columns to sort…
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s…
- Excel VBA AutoFilter and Sort: Apply Both to Data SafelyYes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.