In Excel VBA, add a key-item pair to a dictionary with its .Add method, such as dict.Add "North", 125. A dictionary has no dependable “end” position like a worksheet column or an array: it stores unique keys with associated items, so use a separate ordered structure if the sequence in which items were added matters.
What “add to the end” means in a dictionary
A VBA dictionary is a collection of key-item pairs. Each key identifies one item, much like a unique label for a value. For example, a sales dictionary might associate the key "North" with the value 125. You can add a new pair, look up an item by key, change an item, or remove a pair.
Unlike a worksheet range, a dictionary does not expose a numbered row position where you can instruct VBA to append the next pair. The .Add method adds a pair to the dictionary, but the object’s enumeration order should not be treated as a guaranteed insertion order. If by “end” you mean “add another unique key,” use .Add. If you mean “make this item appear after the previous item in a displayed list,” store the order separately.
This distinction matters when you later loop through dict.Keys or use For Each. Even if items appear to come out in the order you added them in a particular workbook or run, code that relies on that behavior can become fragile. Sort the keys explicitly or keep an ordered list when order is part of the requirement.
Add a new key-item pair with .Add
For Excel for Microsoft 365 on Windows, a common approach is to create a late-bound Scripting.Dictionary object. Late binding does not require selecting a library reference in the VBA editor, which makes the code easier to move between Windows computers with the required scripting component available.
The following macro creates a dictionary and adds three pairs. The key is the text label; the item is a number. The .Count property reports how many pairs are currently stored.
Sub AddItemsToDictionary()
Dim dict As Object
' Create a Scripting.Dictionary object.
Set dict = CreateObject("Scripting.Dictionary")
' Add new, unique key-item pairs.
dict.Add "North", 125
dict.Add "South", 98
dict.Add "West", 143
Debug.Print dict.Count ' Prints 3.
Debug.Print dict("South") ' Prints 98.
End Sub
Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so keep a recoverable version before executing code that changes workbook data or other important state. To run a macro in desktop Excel, open the Visual Basic Editor, insert a standard module, paste the code, and run the procedure. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros.
Set up the dictionary: late binding and early binding
With late binding, declare the variable As Object and create it with CreateObject("Scripting.Dictionary"), as in the example. The trade-off is that the editor cannot provide the same dictionary-specific autocomplete and compile-time type checking that it can with an explicitly referenced library.
Early binding uses the Dictionary type directly. In the VBA editor on Windows, choose Tools > References and select Microsoft Scripting Runtime if it is available, then declare the object with that library’s type:
Sub EarlyBoundDictionaryExample()
Dim dict As Scripting.Dictionary
' Create a strongly typed dictionary.
Set dict = New Scripting.Dictionary
dict.Add "North", 125
End Sub
Early binding can make code easier to write and maintain in a controlled Windows environment, while late binding can avoid a missing-reference problem on another Windows machine. Both examples use the Windows scripting dictionary. VBA availability and the scripting library differ across platforms; do not assume this Windows object will work unchanged in Excel for Mac. Excel for the web does not execute VBA, regardless of which binding style the macro uses.
What happens when the key already exists?
Dictionary keys must be unique. Calling .Add with a key that is already present raises an error rather than adding a second pair with the same key. This is often useful because it exposes accidental duplicates. If you expect a key might already exist, check it with .Exists before choosing whether to add a pair or replace its item.
Sub AddOrUpdateDictionaryItem()
Dim dict As Object
Dim keyName As String
Set dict = CreateObject("Scripting.Dictionary")
keyName = "North"
' Add the key once, then update its item on later runs.
If dict.Exists(keyName) Then
dict.Item(keyName) = 150
Else
dict.Add keyName, 150
End If
End Sub
In this example, the key is added when it is missing and assigned the value 150 when it is already present. Setting the .Item property for a key that is not present also creates that key in a Scripting.Dictionary, but the explicit .Exists branch makes the intended behavior clear. Prefer .Add when a duplicate should be considered a mistake; use an assignment or an add-or-update pattern when replacement is intended.
A frequent misconception is that a dictionary can hold two values under the same key and simply place the second value at the end. It cannot represent duplicate keys as separate entries. If repeated labels are meaningful, use a unique key for each record, or make each dictionary item a collection or array of the values associated with that label.
Keep insertion order when order matters
A dictionary is designed for key-based lookup, not as a queue or an ordered table. If you need both fast lookup and a reliable display sequence, maintain a second structure that records keys as they are first added. The dictionary provides the value for each key; the separate sequence determines the order in which to process or display those values.
For example, a Collection can store keys in append order while the dictionary stores their associated values. Only add a key to the collection when it is new, otherwise repeated updates would create duplicate entries in the ordered sequence:
Sub KeepAnExplicitKeyOrder()
Dim dict As Object
Dim keyOrder As Collection
Dim keyName As String
Set dict = CreateObject("Scripting.Dictionary")
Set keyOrder = New Collection
keyName = "North"
' Record a key's position only the first time it appears.
If Not dict.Exists(keyName) Then
keyOrder.Add keyName
dict.Add keyName, 125
Else
dict.Item(keyName) = 150
End If
End Sub
When displaying the results, loop through keyOrder and use each stored key to retrieve its value from dict. This keeps the first-seen sequence while still allowing values to be updated by key. If alphabetical order is desired instead, sort the keys before output rather than relying on the order returned by the dictionary.
Control how text keys compare
By default, dictionary text-key comparison is binary, so keys with different letter casing can be treated as distinct. If "North" and "north" should represent the same key, set the dictionary’s CompareMode to vbTextCompare before adding any items. Changing the comparison mode after the dictionary contains data can cause a runtime error.
Sub UseCaseInsensitiveTextKeys()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Set comparison behavior before adding keys.
dict.CompareMode = vbTextCompare
dict.Add "North", 125
Debug.Print dict.Exists("north") ' Prints True.
End Sub
Choose the comparison rule based on the data rather than setting it automatically. Case-sensitive identifiers, codes, or other exact labels may need the default comparison. Also standardize key types when possible: a number and a text value that looks like a number are not a sound substitute for one another in a lookup design. Consistent keys make duplicate checks and later lookups easier to reason about.
Common errors and practical checks
Several issues account for most problems when adding dictionary entries:
- Duplicate key:
.Addfails if the key already exists. Use.Existsto test first, or deliberately update the item. - Wrong assumption about order: Do not assume dictionary enumeration is a stable append sequence. Keep a separate key list or sort keys for output.
- Late binding object creation fails: Check that the code is running in desktop Excel on a supported system and that the scripting object is available. The Windows example is not a promise of identical Mac support.
- Comparison mode set too late: Set
CompareModeimmediately after creating the dictionary and before adding keys. - Key and item reversed: The first argument to
.Addis the key; the second is the item. A reversed pair may still run but produce confusing lookups.
When troubleshooting, inspect dict.Count, test a specific key with dict.Exists(keyName), and print or examine dict.Keys and dict.Items. These properties return arrays of keys and items, respectively. A dictionary can store values of different types, but keeping items consistent—for example, all numeric amounts or all record objects—usually makes later code clearer.
When to use a dictionary instead of another structure
Choose the structure based on what “add” needs to accomplish. A dictionary is a good fit when you need to find or update an item by a unique key, such as looking up a product description by product code. It is less suitable when duplicate keys or a guaranteed append position are central to the task.
- Use a dictionary for unique-key lookups and add-or-update logic.
- Use a Collection when you need an appendable sequence that can contain repeated values, or when a simple ordered list is more important than keyed updates.
- Use an array when you need indexed data and can manage its size and position explicitly.
- Use a worksheet range or Excel Table when the records need to be visible, edited by users, filtered, or saved as normal workbook data.
These options can also be combined. A dictionary can provide quick lookups while a worksheet table remains the authoritative, user-visible record. In that arrangement, define which object is the source of truth and update it consistently; otherwise the in-memory dictionary and the sheet can drift out of sync.
Frequently Asked Questions
How do I add a value to a VBA dictionary?
Use dict.Add key, item, after creating the dictionary object. For example, dict.Add "North", 125 adds the value 125 under the unique key "North".
Can a VBA dictionary have duplicate keys?
No. A key identifies one item, and adding the same key again with .Add raises an error. Check .Exists(key) and update the existing item if that is the intended behavior.
Does VBA Dictionary preserve insertion order?
Do not rely on it as a guaranteed ordering mechanism. Keep a separate collection of keys in the required sequence, or explicitly sort the keys before producing output.
How do I update an existing dictionary item?
Assign a new value to its item, such as dict.Item("North") = 150. If the key may not exist, use .Exists to decide whether to update it or add it.
Does Scripting.Dictionary work in Excel for the web or on Mac?
Excel for the web cannot run VBA macros. The examples here target Excel for Microsoft 365 on Windows; scripting-library availability differs on Mac, so verify the object on the target Mac version or use a suitable alternative structure.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Remove Item From Dictionary: Methods and ExamplesTo remove one entry from a VBA dictionary, call dict.Remove key, where key is the entry’s key—not its…
- Excel VBA Evaluate: How It Works and When to Use ItIn Excel VBA, Application.Evaluate interprets a text expression using Excel’s formula language and returns the result as a…
- Excel VBA: Get the Last Row Number ReliablyTo get the last nonempty row in a particular column, start at the bottom of that worksheet column…
- Excel VBA Timer: Measure Code and Schedule Macros SafelyIn Excel VBA, use the Timer function to measure elapsed time, and use Application.OnTime to schedule a macro…
- How to Run Macros in Google Sheets: A Step-by-Step GuideTo run a macro in Google Sheets, open the spreadsheet in a desktop web browser, choose Extensions >…
- Outlook Excel VBA: Automate Email and Calendar TasksYes—Excel VBA can automate classic Outlook for Windows, including creating email drafts from spreadsheet rows and working with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.