Updating Items in a VBA Dictionary: Examples and Tips

To update a value in a VBA Scripting.Dictionary, assign a new value to its existing key: dict("name") = "new value". This assignment also creates the key if it is missing, so check dict.Exists("name") first when you want to update existing entries only.

What a VBA dictionary stores

A dictionary stores pairs: a key that identifies an entry and an associated item, or value. For example, a workbook macro might use product codes as keys and stock counts as values. Unlike a worksheet range, a dictionary is held in memory while your VBA code runs; changing an item does not automatically write anything to a cell.

In Excel for Microsoft 365 on Windows, a common implementation is the Scripting.Dictionary object. You can create it with late binding, which avoids setting a project reference, or early binding by selecting Microsoft Scripting Runtime under the VBA editor’s Tools > References. The examples here use late binding. The Scripting Runtime is a Windows COM library and is not a built-in option on Excel for Mac; Mac users should verify availability in their environment or choose an alternative.

Each key is unique under the dictionary’s comparison rules, but an item can be changed repeatedly. That makes the dictionary useful for lookups, counts, and temporary data transformations. The key and item do different jobs: changing the item updates the value associated with a key, while changing which key identifies an entry is a separate operation.

Assign a new value to an existing key

The dictionary’s default Item property lets you write either dict(key) = value or the more explicit dict.Item(key) = value. Both forms assign the item for that key. The explicit form can be easier to read when you are learning VBA or reviewing code later.

For example, after a dictionary contains the key "P-104" with an item of 8, the statement dict("P-104") = 11 replaces that item with 11. The dictionary still has the same key, and its count does not increase. Any later lookup of dict("P-104") returns 11.

Assignment is not the same as the Add method. Use assignment when you intend to replace a value, or when adding a missing key is acceptable. Use Add when you specifically want to create a new entry and have duplicate keys treated as an error.

Check whether the key exists first

Setting an item for a key that is not present creates that key, rather than reporting that there was nothing to update. This behavior is convenient for “insert or update” logic, but can hide a misspelled key when the intention was to change an existing entry. The Exists method makes that choice explicit.

Sub UpdateExistingEntry()
    Dim dict As Object
    Dim productCode As String

    Set dict = CreateObject("Scripting.Dictionary")
    productCode = "P-104"

    dict.Add productCode, 8

    ' Change the item only if the key is already present.
    If dict.Exists(productCode) Then
        dict.Item(productCode) = 11
    End If
End Sub

In this example, the value for P-104 changes from 8 to 11, and the dictionary still contains one entry. If the key were absent, the condition would be false and nothing would be added. If you want to handle both cases, use an If...Then...Else branch: assign to the key when it exists, and call Add when it does not.

Before running a macro that changes a workbook, save a copy of the workbook. VBA macro actions cannot be undone with Ctrl+Z. This sample changes an in-memory dictionary only, but that precaution matters when adapting the pattern to write results into cells or alter workbook data.

Choose between update, insert, and upsert

Pick the operation that matches the intended behavior, rather than relying on a line of code to imply it. An “upsert” means insert the key if it is absent, or update its item if it is already present.

Update only: Call Exists; assign the new item only when it returns True. Decide what should happen when the key is missing, such as logging it or showing a message.
Insert only: Use Add key, value. If the key already exists, VBA raises an error, so check for duplicates first if that is an expected possibility.
Insert or update: Use If dict.Exists(key) Then to assign the item; otherwise, use dict.Add key, value.

There is a shorter upsert form: assign with dict(key) = value without checking first. It works because the item assignment creates a missing entry. However, that convenience means a typo such as "P-140" instead of "P-104" silently creates another key. In code where an unexpected key is a data-quality problem, the explicit check is safer and easier to diagnose.

A complete example with a deliberate upsert

This example shows both branches. If P-104 is already present, its value becomes 11; if it is absent, the macro adds it with that value. The sample starts with another entry so the existing-key branch is visible.

Sub InsertOrUpdateProduct()
    Dim dict As Object
    Dim productCode As String
    Dim newStock As Long

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "P-104", 8
    dict.Add "P-205", 3

    productCode = "P-104"
    newStock = 11

    ' Update the existing item or add a new key.
    If dict.Exists(productCode) Then
        dict.Item(productCode) = newStock
    Else
        dict.Add productCode, newStock
    End If
End Sub

After this macro runs, the item for P-104 is 11 and the item for P-205 remains 3. If you change productCode to P-300, the macro adds that key with an item of 11 instead. These values remain in the dictionary only; to show them on a worksheet, your code must explicitly write them to cells.

Updating different kinds of items

Dictionary items can be values of different types, including numbers, strings, dates, and Variants. Assign a value compatible with what later code expects. For instance, replacing a numeric stock count with text may be allowed by the Variant-based dictionary, but a later calculation that assumes a number could fail or produce an unintended conversion.

When an item is an object, the distinction between assigning an ordinary value and assigning an object reference matters. In VBA, object variables are set with Set. Retrieve an object item into an object variable with Set objectVariable = dict(key); assigning an object into a dictionary item also uses object assignment syntax. If the item is a class instance, changing one of its properties changes that object, which is different from replacing the dictionary’s item with a different object.

For numeric totals, a common pattern is to read the current item, calculate a new total, and assign it back. This is still an update; the dictionary does not automatically add numbers when you assign a new item. Make sure the key exists before reading it, or supply a clear initial value for a new key. For example, a frequency counter might add one to an existing count, but initialize a previously unseen word to one.

Keys, capitalization, and comparison mode

By default, a dictionary uses binary comparison for string keys, so capitalization can matter: "North" and "north" may be separate keys. If your data should treat those spellings as equivalent, set CompareMode to vbTextCompare before adding entries. Changing the comparison mode after the dictionary contains data can raise an error, so configure it immediately after creating the object.

Here is the relevant setup order:

Sub CreateTextCompareDictionary()
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    ' Set comparison rules before adding any keys.
    dict.CompareMode = vbTextCompare
    dict.Add "North", 8
    dict.Item("north") = 11
End Sub

With text comparison enabled, the last assignment refers to the existing key rather than creating a second entry that differs only by capitalization. This affects both Exists checks and key uniqueness. Choose the mode based on the meaning of your identifiers: a case-sensitive code system may need the default, while names intended to match regardless of case may be better served by text comparison.

Common mistakes and troubleshooting

Using Add to replace an item: Add creates a key; it does not overwrite an existing entry. A duplicate key can raise runtime error 457. Use item assignment for replacement.
Assuming assignment only updates: dict(key) = value can add a missing key. Use Exists when creating a new entry would be a mistake.
Using a different key by accident: Check spelling, spaces, and capitalization. With binary comparison, capitalization differences can identify different keys. Trimming incoming text may be appropriate when spaces are not meaningful, but do not remove them automatically if they are part of the identifier.
Changing comparison mode too late: Set CompareMode before adding keys. If the dictionary is already populated, create a new dictionary with the intended mode and add the entries again.
Expecting a worksheet to change: Updating a dictionary does not update a cell or table. Add separate code to write the result to the worksheet.

When debugging, inspect dict.Count before and after the assignment, and check dict.Exists(key) for the exact key you intend to change. If the count increases unexpectedly, an assignment probably created a new key. If Add raises an error, check whether the key already exists and whether the dictionary’s comparison mode considers it equivalent to another key.

When a dictionary may not be the right choice

A dictionary is useful when code needs fast key-based lookup and each key should identify one current item. It is less suitable when you need to preserve duplicate keys, maintain a visible worksheet table as the primary data store, or rely on an ordered list. A VBA Collection can store multiple items and supports keyed access, but it does not offer the same built-in Exists method or the same direct item-assignment pattern. A worksheet range may be easier when users need to inspect and edit the data directly.

For a small, fixed set of values, ordinary variables or arrays may be simpler. For many rows that need grouping or reporting, a dictionary can be populated from a range, updated in memory, and then written back in a batch. That approach separates the calculation from worksheet interaction, but it also means the macro must manage the data transfer and handle missing or duplicate keys deliberately.

Frequently Asked Questions

Does assigning a value to a missing dictionary key add it?
Yes. With a Scripting.Dictionary, assigning through dict(key) creates the key when it is not already present. Use Exists first if you want update-only behavior.

What is the difference between Add and dict(key) = value?
Add is for creating an entry and raises an error if the key already exists. Item assignment replaces an existing item, and it also creates a missing key.

How do I update a dictionary value without changing the key?
Assign the new value to the existing key, for example dict.Item("P-104") = 11. That changes the item associated with the key, not the key itself.

Can a dictionary key be changed?
The dictionary exposes a Key property for changing an existing key, but that is separate from changing its item. Check for a conflicting destination key and consider whether downstream code depends on the old key before changing it.

Why does my dictionary contain two entries that look the same?
String comparison may be case-sensitive by default, and keys may also differ because of spaces or other characters. Set CompareMode before adding data if case-insensitive matching is intended, and inspect the actual key text when troubleshooting.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.