Add Strings to a Dictionary in VBA: Step-by-Step

VBA & macros · Updated November 16, 2026

To add strings to a VBA Dictionary, create a Dictionary object and call its Add method with a unique key and the string you want to store, such as dict.Add "SKU-104", "Blue ceramic mug". This guide shows how to set it up in Excel for Microsoft 365 on Windows, add and retrieve text, and handle common errors.

What You’ll Need

  • Excel desktop for Windows and a workbook where macros are allowed. Excel for the web cannot run VBA macros.
  • A basic understanding of VBA variables and how to open the Visual Basic Editor.
  • A saved copy of your workbook. Macros cannot be undone with Ctrl+Z, so keep a backup before running one.

The examples use late binding, which means VBA creates the Dictionary at runtime and you do not need to enable a reference first. A Dictionary stores pairs: each unique key points to an item. Both the key and item can be strings. Often the key is a code or name and the item is the text associated with it.

Step-by-Step

  1. Open the Visual Basic Editor. In Excel for Windows, press Alt+F11. On a Mac, press Option+F11; depending on your keyboard, you may also need to hold Fn. You can also open the editor from Excel’s Developer tab if keyboard shortcuts are unavailable. If Developer is not visible on Windows, enable it through File > Options > Customize Ribbon and select Developer.
  2. Insert a standard module. In the Visual Basic Editor, choose Insert > Module. A blank code pane appears. A standard module is a convenient place for a reusable macro; it is different from a worksheet’s code module or the ThisWorkbook module.
  3. Paste in this complete example. It creates a Dictionary, sets text comparison before adding any entries, stores three string values under three string keys, retrieves one value, and prints all pairs in the Immediate window.
Sub AddStringsToDictionary()
    Dim dict As Object
    Dim key As Variant

    ' Create a late-bound Dictionary object.
    Set dict = CreateObject("Scripting.Dictionary")

    ' Set this before adding entries to compare keys without case sensitivity.
    dict.CompareMode = vbTextCompare

    ' Add a string key and a string item in each pair.
    dict.Add "SKU-104", "Blue ceramic mug"
    dict.Add "SKU-205", "Green glass bottle"
    dict.Add "SKU-318", "Cotton tote bag"

    ' Print one stored string to the Immediate window.
    Debug.Print CStr(dict("SKU-104"))

    ' Print each key and its associated string.
    For Each key In dict.Keys
        Debug.Print CStr(key) & " -> " & CStr(dict(key))
    Next key
End Sub
  1. Understand the key and item positions. In dict.Add "SKU-104", "Blue ceramic mug", the first argument is the key and the second is the item. The key identifies the entry; the item is the string returned when you look up that key. You can reverse the roles if that suits your data, but keys must be unique under the Dictionary’s comparison rules.
  2. Run the macro. Click inside the procedure and press F5, or choose Run > Run Sub/UserForm. If Excel shows a security warning, only enable macros for a workbook you trust. To see the printed results, open the Immediate window in the editor with Ctrl+G on Windows. On a Mac, use the editor’s View menu to show the Immediate window if the shortcut differs.
  3. Retrieve a string by its key. The expression dict("SKU-104") returns the item associated with that key. In the example, it returns Blue ceramic mug. You can assign it to a declared string variable, for example Dim productName As String followed by productName = CStr(dict("SKU-104")). The key must exist before you retrieve it this way.
  4. Check for an entry before reading or adding it. Use the Dictionary’s Exists method to avoid requesting a missing key or adding a duplicate. For example, use If dict.Exists("SKU-104") Then before reading the item. To add a new key only when it is absent, check If Not dict.Exists(newKey) Then and call dict.Add newKey, newText inside that block. Declare newKey and newText as strings and assign them first.
  5. Save the workbook in a macro-enabled format. If you want to keep the macro, use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Saving as a standard .xlsx file removes VBA code. Keep the backup you made before testing, since running a macro is not reversible through Excel’s Undo command.

If you want the macro to update an existing entry instead of reporting a duplicate, test the key first. Assigning to dict(newKey) changes the item for an existing key and adds an item if the key is absent. If you want to make the add-versus-update behavior explicit, use this pattern:

If dict.Exists(newKey) Then
    dict(newKey) = newText
Else
    dict.Add newKey, newText
End If

Here, newKey and newText need to be declared and assigned earlier in the same procedure. This pattern prevents the duplicate-key error while ensuring that the latest text is stored.

Common Mistakes

Using a key twice. A Dictionary cannot contain two entries with the same key. Calling Add a second time with a key that already exists raises an error. Check with Exists before adding, or deliberately replace the existing item with dict(key) = newText.

Mixing up keys and items. The first argument to Add is not the value you get back; it is the lookup key. The second argument is the stored item. If you add a product name first and a code second, you will need to look up the entry by product name.

Reading an absent key. Checking the Dictionary’s count does not prove that a particular key exists. Use Exists(key) for that specific lookup. A missing-key access can produce an error, so check before retrieving values when the input is uncertain.

Setting CompareMode too late. Choose vbTextCompare for case-insensitive key comparison or vbBinaryCompare for case-sensitive comparison before adding entries. Changing CompareMode after the Dictionary contains data can cause an error. Also remember that text comparison affects keys, not the stored item’s capitalization.

Expecting a plain .xlsx file to retain VBA. Use .xlsm to preserve the macro. The Dictionary exists while the code runs; this example does not save its contents into worksheet cells or preserve them automatically between macro runs.

Tips

Use meaningful, stable keys. A product ID, email address, or normalized label is generally easier to look up reliably than a description that may change. Keep the key and item types clear in your code: a string key can point to a string item, while the Dictionary itself can also store other kinds of values.

With late binding, declaring the variable As Object and using CreateObject("Scripting.Dictionary") avoids a compile-time reference. On Windows, an alternative is early binding: in the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can provide editor assistance, but the reference must be available on the computer running the workbook.

For worksheet data, build a Dictionary in memory when you need fast lookups or want to identify unique keys. If the output needs to remain visible after the macro ends, write the keys and items to cells as a separate step. Do not assume the Dictionary is a worksheet table or a permanent workbook feature.

Mac support differs from Windows because Scripting.Dictionary relies on Windows scripting components and may not be available in Excel for Mac. If CreateObject reports that it cannot create the object on your Mac, use a VBA Collection or a custom class-based structure, or run the macro in Excel for Windows. Excel for the web does not run VBA; use desktop Excel for this procedure.

Troubleshooting

“ActiveX component can’t create object” or a similar creation error: Excel could not create Scripting.Dictionary. Confirm that you are using desktop Excel on Windows and that the code spells the ProgID exactly as Scripting.Dictionary. On Mac, this component may not be present, so use an alternative such as a Collection or run the workbook on Windows. Excel for the web cannot run this VBA code.

“Key is already associated with an element” or error 457: The key is already in the Dictionary. Use Exists before Add; if replacement is intended, assign the new text to dict(key) instead. Check whether a case-insensitive CompareMode makes two differently capitalized keys count as the same key.

“Object variable or With block variable not set”: The Dictionary variable was not initialized. Confirm that the procedure includes Set dict = CreateObject("Scripting.Dictionary") before any call to Add, Exists, or a key lookup. In VBA, object variables require Set when assigning an object.

The Immediate window is blank: The macro may not have run, or it may have stopped before reaching Debug.Print. Run the procedure again, use F8 to step through it line by line, and open the Immediate window with Ctrl+G in the Windows editor. The printed output appears in the editor, not in a worksheet cell.

A macro will not run: Check that the code is in a standard module, the workbook is saved as .xlsm, and macros are permitted by your organization’s security settings. Do not lower security settings broadly to run unfamiliar files. If the code stops on a highlighted line, read the error message and verify that each key exists before accessing it.

Frequently Asked Questions

Can a VBA Dictionary store only strings?
A Dictionary can store strings, but it is not limited to them. Its keys and items can hold other compatible values too. In this guide, both arguments to Add are strings to make the pattern clear.

How do I add a string without overwriting an existing entry?
Use If Not dict.Exists(key) Then dict.Add key, text. This adds the string only if the key is not already present. Decide separately what the macro should do when the key exists, such as skip it or show a message.

How do I change a string that is already stored?
Assign the replacement item to its key, as in dict(key) = newText. If the key might not exist, test with Exists first and use Add for the missing-key case.

Can I use the same string as both key and item?
Yes. For example, dict.Add "Pending", "Pending" is valid if that key is not already present. It is more common to use a stable identifier as the key and descriptive text as the item.

Does a Dictionary keep its contents after the macro finishes?
No. The object in this example is held in memory while the procedure runs. To retain the information, write its keys and items to worksheet cells, a file, or another storage location, then rebuild the Dictionary when needed.

Do I need to add a VBA reference?
Not for the late-bound Windows example using As Object and CreateObject. Early binding requires selecting Microsoft Scripting Runtime in Tools > References on Windows. Mac availability differs, and Excel for the web cannot run VBA.

Related

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.