VBA Excel Dictionary Example: Code and Setup

VBA & macros · Updated November 22, 2026

A VBA Dictionary stores values under unique keys, making it useful for fast lookups and totals by name or code. In Excel for Microsoft 365 on Windows, create one with CreateObject("Scripting.Dictionary"); the example below reads product codes from column A and returns the matching description from column B.

A Basic VBA Dictionary Example

Put product codes in A2:A100 and their descriptions in B2:B100 on a worksheet named Products. This macro builds a dictionary from those columns and looks up the code P-104, placing its description in D2. Change the worksheet name, ranges, or lookup code to suit your workbook.

Save a copy of the workbook before running the macro. VBA changes cannot be undone with Ctrl+Z, so a backup is the safest way to recover if a macro writes to the wrong cells.

Sub FindProductDescription()
    Dim dict As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim productCode As String

    Set ws = ThisWorkbook.Worksheets("Products")
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        productCode = Trim$(CStr(ws.Cells(r, "A").Value2))
        If Len(productCode) > 0 Then
            dict(productCode) = ws.Cells(r, "B").Value2
        End If
    Next r

    If dict.Exists("P-104") Then
        ws.Range("D2").Value = dict("P-104")
    Else
        ws.Range("D2").Value = "Code not found"
    End If
End Sub

To try it, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, paste the code, and run FindProductDescription with F5 while the cursor is in the procedure. The macro writes only to D2 in this example. If P-104 appears more than once, the later row replaces the earlier description because assigning a value to an existing key updates its item.

What a Dictionary Is and When to Use One

A Dictionary is an object that pairs a key with an item. The key is the unique identifier you use to retrieve a value: for example, a product code can be the key and its description the item. A dictionary is not an Excel worksheet function; it is a VBA data structure that a macro creates and uses while it runs.

This key-and-item model suits tasks where a macro needs to check whether a value has already appeared, retrieve information associated with a code, or keep a running total for each category. It can be simpler than repeatedly searching a worksheet for each lookup, especially when a procedure performs many lookups in a loop. It is held in memory, however, so it does not automatically update when a cell changes. Run the macro again or write code to rebuild the dictionary when the source data changes.

  • Keys: unique identifiers such as employee IDs, email addresses, or product codes.
  • Items: the values associated with those keys, such as names, descriptions, or accumulated quantities.
  • Exists: a check that tells the macro whether a key has already been added.

Keys must be unique, but items do not have to be. Different keys can point to the same item. If the source contains duplicate keys, decide what they mean before writing the macro: keep the first value, replace it with the latest value, or combine the rows. The basic example replaces duplicates, while the totals example below adds them together.

How to Set Up and Run the Code

The example uses late binding: Set dict = CreateObject("Scripting.Dictionary") asks Windows to create the Dictionary object when the macro runs. This avoids selecting a reference in the Visual Basic Editor and makes the workbook less dependent on a particular reference setting. It does not remove the requirement for the underlying scripting component to be available.

  1. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains macros you need to keep.
  2. Open the Visual Basic Editor with Alt+F11 on Windows. You can also use Developer > Visual Basic if the Developer tab is displayed.
  3. In the editor, choose Insert > Module, paste the procedure into the module, and adjust the sheet name and columns to match your data.
  4. Return to Excel, select a cell on the relevant sheet if needed, and run the macro from the editor or through Developer > Macros.

On a Mac, the Visual Basic Editor can be opened from Tools > Macro > Visual Basic Editor; the keyboard shortcut may be Option+F11, or Fn+Option+F11 on keyboards where function keys control hardware features. The Windows Scripting Dictionary is not a dependable cross-platform choice for Mac VBA. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros; use desktop Excel for these steps.

Adding Values for Duplicate Keys

For a common related task, suppose A2:A100 contains product codes and B2:B100 contains numeric quantities. The following macro combines repeated codes into one total, then writes each code and its total to columns D and E. For example, if A2:A4 contain P-104, P-104, and P-205, with quantities 3, 5, and 2 in B2:B4, the output includes P-104 with 8 and P-205 with 2.

This macro ignores blank product codes and ignores quantities that Excel does not recognize as numbers. It does not clear old output below the results, so remove previous results yourself if a later run has fewer unique codes.

Sub TotalQuantityByProduct()
    Dim dict As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim productCode As String
    Dim quantity As Double
    Dim itemKey As Variant
    Dim outRow As Long

    Set ws = ThisWorkbook.Worksheets("Products")
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        productCode = Trim$(CStr(ws.Cells(r, "A").Value2))
        If Len(productCode) > 0 And IsNumeric(ws.Cells(r, "B").Value2) Then
            quantity = CDbl(ws.Cells(r, "B").Value2)
            If dict.Exists(productCode) Then
                dict(productCode) = CDbl(dict(productCode)) + quantity
            Else
                dict.Add productCode, quantity
            End If
        End If
    Next r

    ws.Range("D1").Value = "Product code"
    ws.Range("E1").Value = "Total quantity"
    outRow = 2

    For Each itemKey In dict.Keys
        ws.Cells(outRow, "D").Value = itemKey
        ws.Cells(outRow, "E").Value = dict(itemKey)
        outRow = outRow + 1
    Next itemKey
End Sub

The output order follows the dictionary’s key collection, so do not rely on it as a custom sort order. If the result needs sorting, sort the output range in Excel or add a separate sorting step. The macro also assumes quantity values are suitable to add; if cells contain text, blanks, or error values that need special treatment, define and code that rule explicitly.

Useful Dictionary Properties and Methods

These are the members most readers need when starting out. In VBA, a method performs an action, while a property provides or changes information about the object.

  • Add key, item adds a new pair. It raises an error if that key already exists, so check Exists first when duplicates are possible.
  • Exists(key) returns True if the key is present. It is useful before reading or adding a value.
  • dict(key) returns the item for a key. Assigning to it updates an existing item; assigning to a missing key can also add that key.
  • Keys returns the keys, and Items returns the corresponding items. A For Each loop can go through the returned values.
  • Count gives the number of key-item pairs. Remove key removes one pair, and RemoveAll empties the dictionary.
  • CompareMode controls how text keys are compared. Set it before adding keys; changing it after keys have been added can cause an error.

With vbTextCompare, keys such as ABC and abc are treated as equal. The default binary comparison distinguishes uppercase and lowercase characters. Choose deliberately: text-insensitive comparison is convenient for many codes, but it can combine values that your business rules consider different. Trimming surrounding spaces can also prevent visually similar keys such as "P-104" and " P-104 " from becoming separate entries.

Common Problems and Safe Practices

A frequent error is “ActiveX component can’t create object” when the macro reaches CreateObject("Scripting.Dictionary"). The object is associated with Windows scripting support, so verify that you are running desktop Excel on Windows and that the required component is available. A reference setting will not make this Windows-specific object a reliable Mac solution.

Another common source of trouble is a mismatch between the worksheet name in the code and the actual tab name. ThisWorkbook.Worksheets("Products") refers to the workbook containing the VBA code, not necessarily the workbook currently active on screen. Confirm the spelling of the tab name, including spaces. If the source is in a different workbook, qualify that workbook intentionally rather than relying on whichever workbook happens to be active.

Also check the data and the duplicate rule. A blank key should generally be skipped, as in the examples, or deliberately handled as a valid category. Values read with Value2 may have types other than text, so converting a code to a string and trimming it helps standardize keys. For identifiers with leading zeros, make sure Excel has retained those zeros as text; a number stored as 104 cannot tell VBA that it was originally entered as 00104.

Macros can change cells and are not reversed by Ctrl+Z. Keep a backup, review what ranges the procedure writes to, and run macros only from workbooks you trust. Do not lower Excel’s macro security settings globally just to run an unfamiliar file; follow your organization’s security guidance and use a trusted, reviewed workbook.

Dictionary Compared with Other Lookup Options

Choose a Dictionary when VBA needs to perform repeated key-based checks or build a summary in memory. For a one-off worksheet lookup, a formula may be easier for other workbook users to inspect and update. Microsoft 365 and Excel 2021 include functions such as XLOOKUP, which can return a matching value from a worksheet range without a macro. For a concise, visible lookup, a formula is often the more maintainable choice.

A VBA Collection can store items and retrieve them using keys, but it does not provide the same convenient Exists method or built-in key and item lists. Arrays are useful for processing rows in memory, particularly when speed and structured data handling matter, but searching an array for a key usually requires additional code. The best option depends on who must maintain the workbook, whether macros are permitted, and whether the result should respond automatically to cell edits.

Frequently Asked Questions

Do I need to add a reference to use a VBA Dictionary?
Not for the late-bound example in this article, which creates the object with CreateObject. Early binding is another approach: in the Windows Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime, then declare a Scripting.Dictionary variable. Early binding offers editor assistance, but the reference must be available to the workbook’s users.

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume that the Windows Scripting Dictionary is available in Mac Excel. A macro that depends on it may fail there even if its VBA code opens. If the workbook must work on both platforms, consider a cross-platform design using arrays or another supported approach, and check that design in the Excel versions your users actually have.

What happens when I add a duplicate key?
The Add method raises an error if the key already exists. In contrast, assigning with dict(key) = value updates the existing item, which is why the first example keeps the last description it reads. To preserve the first item, check Exists and add only when the key is absent; to combine values, check and update a total as in the second example.

Are Dictionary keys case-sensitive?
By default, comparisons use binary comparison, which distinguishes uppercase and lowercase text. Set CompareMode = vbTextCompare before adding any keys if ABC and abc should match. This affects key comparison, not the spelling or capitalization of the text stored as an item.

Can I run a VBA Dictionary macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel on a compatible platform to run VBA; if browser-based automation is required, investigate Office Scripts instead, noting that Office Scripts use TypeScript rather than 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.