Yes. In VBA, change a Dictionary value by assigning a new value to its key, for example dict("North") = 135; you can also write dict.Item("North") = 135. The key point is that the assignment updates the item when the key exists, while a missing key can be created by that same assignment.
The basic syntax for changing a value
A Scripting.Dictionary stores key-and-value pairs. Its Item property retrieves or sets the value associated with a key, and the shorthand dict(key) refers to that property. For an ordinary value such as text or a number, use a regular assignment; do not put Set before it.
For example, suppose a dictionary already contains the key North with the value 120. Assigning 135 to that key replaces the old value. The following complete macro demonstrates the operation:
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11, sometimes with Fn as well, depending on the keyboard.
Sub ChangeDictionaryValue()
Dim dict As Object
' Create a late-bound Dictionary and add an initial item.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 120
' Replace the value associated with the existing key.
dict("North") = 135
Debug.Print dict("North") ' Prints 135.
End Sub
The output is 135: the key remains North, and its associated value changes from 120. This operation does not require removing the key first. Removing and re-adding it is usually unnecessary and can make code harder to follow.
How a Dictionary stores keys and items
The Dictionary object is commonly used in VBA when code needs to look up a value quickly by a unique key, such as a product code, employee number, or category name. A key identifies an item; the item is the data stored under that key. Changing an item does not, by itself, change the key.
In VBA, a Dictionary’s item can hold different kinds of data, including numbers, strings, dates, Boolean values, and object references. The item is exposed through the Dictionary’s Item property. That property has both a retrieval role and an assignment role, which is why the same notation can appear on either side of an assignment:
currentValue = dict("North")retrieves the item.dict("North") = 135assigns a new item.dict.Item("North") = 135does the same assignment using the property name explicitly.
Dictionary keys must be unique within the dictionary. If a key already exists, assigning a new value to it replaces the old item; it does not create a second copy of that key. This makes the assignment form convenient for updating a lookup table as new information arrives.
Updating an existing key versus adding a new one
There is an important distinction between Add and assignment. The Add method is intended to add a new key and item. If you call Add with a key that is already present, VBA raises an error because duplicate keys are not allowed. Assignment through dict(key), by contrast, is useful when you want the key to have a particular value whether it is already there or not.
For example, dict("North") = 135 changes the existing item if North is present. If that key is absent, assignment creates it with the assigned item. This can be a helpful upsert-style pattern, but it can also hide a spelling mistake: dict("Nroth") = 135 may create a new entry instead of warning that the intended key was North.
When you specifically require an existing key, check with Exists before assigning. The check makes the intended behavior clear and lets you choose what to do when the key is missing:
Sub UpdateOnlyIfPresent()
Dim dict As Object
' Create an example dictionary and an existing key.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 120
' Change the item only when the requested key exists.
If dict.Exists("North") Then
dict("North") = 135
Else
Debug.Print "The key was not found."
End If
End Sub
Use dict.Add key, value when a missing key should be added but an existing key should be treated as an error. Use assignment alone when either inserting or replacing is acceptable. Use Exists when the two cases need different handling, such as updating a record only if it has already been loaded.
Changing object values requires Set
When an item is an object rather than a simple value, use Set to assign the object reference. Without Set, VBA treats the assignment as a value assignment and may report an error. The same distinction applies when retrieving an object from a Dictionary: use Set to put the returned object reference into an object variable.
For instance, a Dictionary item could hold a Collection. This example creates a replacement object and stores it under an existing key:
Sub ReplaceDictionaryObject()
Dim dict As Object
Dim oldRecord As Collection
Dim replacement As Collection
' Create a Dictionary and store an initial Collection object.
Set dict = CreateObject("Scripting.Dictionary")
Set oldRecord = New Collection
oldRecord.Add "Open", "Status"
Set dict("Ticket-1") = oldRecord
' Build a different object and replace the stored reference.
Set replacement = New Collection
replacement.Add "Closed", "Status"
Set dict("Ticket-1") = replacement
End Sub
This replaces the reference held by the Dictionary; it does not edit the original Collection in place. If you retrieve the object with Set oldRecord = dict("Ticket-1") and change a property or member of that object, you are changing the object itself. That is different from assigning a different object reference to the Dictionary item. A common source of confusion is thinking that every Dictionary update needs Set: it is needed for object references, not for a number such as 135 or a string such as "Closed".
Common problems and how to avoid them
Several errors that look like “VBA cannot change the value” are actually about the key, the item’s type, or the way the Dictionary was created. Check these points when an assignment fails or produces an unexpected result:
- A key was misspelled. Assignment can create a missing key, so a typo may not produce an error. Use
Existswhen the key must already be present. Addwas used for an existing key. Use item assignment to replace the value, or checkExistsand decide whether to update or add.- An object was assigned without
Set. UseSet dict(key) = objectVariablefor object references. - The code reads an absent key before checking it. A Dictionary item lookup for a missing key can create that key with an empty item. Check
Existsbefore retrieving a key when absence matters. - The Dictionary variable was never initialized. With late binding, create it using
Set dict = CreateObject("Scripting.Dictionary")before calling its methods.
Also remember that an item’s data type can affect what happens next. A Dictionary can store a Variant value, but later code might expect a number and fail if the item has become text or an object. If the data is used in calculations or comparisons, validate or convert it deliberately rather than assuming every item has the same type.
Changing values while looping through a Dictionary
It is often useful to update items in a loop, for example to apply a percentage adjustment or normalize text values. Changing the item associated with an existing key leaves the set of keys intact, so it is different from adding or removing entries during enumeration. A simple loop can use a Variant variable for each key:
Sub IncreaseExistingValues()
Dim dict As Object
Dim key As Variant
' Create sample numeric items.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 120
dict.Add "South", 80
' Replace each item without changing the keys.
For Each key In dict.Keys
dict(key) = dict(key) + 10
Next key
End Sub
After the loop, the values are 130 for North and 90 for South. The key variable is declared as Variant because the keys returned by Keys are an array of values. If the loop may add or remove keys, take a separate snapshot of the keys first or redesign the loop; changing the collection’s membership while enumerating it can make iteration unreliable. For a value-only update, keep the keys unchanged and make the assignment explicit.
Key comparison and the difference between a key and a value
Changing an item is not the same as changing a key. If the label itself must change, the Dictionary has a Key property for renaming a key. That is a separate operation from dict(oldKey) = newValue, which leaves the key alone and replaces its item. Keeping this distinction clear helps when debugging code that appears to update the wrong part of a pair.
String key matching also depends on the Dictionary’s CompareMode. The default comparison is binary, so keys such as "North" and "north" are treated as different strings under that mode. If you want text comparison that ignores case, set CompareMode to vbTextCompare before adding keys. Changing the comparison mode after items have been added can raise an error, so decide on the rule at initialization. A value assignment cannot fix a lookup that uses a key with different capitalization under binary comparison.
Windows, Mac, and ways to create a Dictionary
In Excel for Microsoft 365 on Windows, a common approach is late binding with CreateObject("Scripting.Dictionary"). Late binding avoids setting a project reference, and the variable is usually declared As Object. Another Windows option is early binding: in the Visual Basic Editor, open Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. Early binding provides the Dictionary type in the editor and can offer better code completion, but the reference must be available in the project.
Excel for Mac does not provide the same Windows COM-based Scripting Runtime in the same way, so CreateObject("Scripting.Dictionary") may not work there. If a workbook must run on both platforms, do not assume a Windows Dictionary reference or COM object is portable. Consider a VBA Collection, arrays, or a custom class and test the chosen implementation on the target versions of Excel. A Collection supports keyed items, but it does not offer the same direct item-replacement pattern, so replacing an entry generally requires different logic.
For a Windows workbook, use the method that matches the project: late binding is convenient for distributing code without a reference setting, while early binding is useful when the reference is managed consistently. For cross-platform work, portability should shape the data structure choice before the dictionary-based code is built.
Frequently Asked Questions
Can I change a VBA Dictionary value without deleting the key?
Yes. Assign a new item with dict(key) = newValue or dict.Item(key) = newValue. The key stays in the Dictionary; only its associated item changes.
Does dict(key) = value add a key if it is missing?
Yes. Assignment can create a missing key with the supplied item. If you intend to update existing entries only, use If dict.Exists(key) Then before assigning.
Why does VBA say an object variable or With block variable is not set?
The Dictionary may not have been initialized. Create it first, for example with Set dict = CreateObject("Scripting.Dictionary") on Windows, and make sure the variable refers to a valid Dictionary before using its items.
Do I need Set when changing a Dictionary item?
Use Set when the item being assigned is an object reference. For ordinary values such as numbers, dates, or strings, use a normal assignment without Set.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume the Windows Scripting Runtime is available on Mac. For code that must run on both platforms, consider a Collection, arrays, or another portable design, and verify it in the Excel versions you support.
Related
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Sort Columns in Excel with VBAYes. In Excel VBA, use the Sort object to sort entire rows by values in a chosen column,…
- Defender Risky Action Excel Macro: Meaning and Safe StepsYes—Microsoft Defender can flag an Excel macro as risky or block an action it tries to take. That…
- Excel VBA Array Indexing: Bounds, Loops, and ExamplesAn Excel VBA array index is the number used to identify an element in an array. In VBA,…
- Google Sheets Macros: How They Work and How to Use ThemYes. Google Sheets has macros: you can record a sequence of spreadsheet actions and replay it later, or…
- Excel VBA: How to Open an Excel File as Read-OnlyTo open an Excel workbook as read-only with VBA, pass ReadOnly:=True to Workbooks.Open. For example, Set sourceBook =…
- Excel VBA: How to Delete the Current RowTo delete the worksheet row containing the active cell, use VBA’s ActiveCell.EntireRow.Delete method. For a safer macro that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.