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.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it contains macros you need to keep. - Open the Visual Basic Editor with Alt+F11 on Windows. You can also use Developer > Visual Basic if the Developer tab is displayed.
- In the editor, choose Insert > Module, paste the procedure into the module, and adjust the sheet name and columns to match your data.
- 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, itemadds a new pair. It raises an error if that key already exists, so checkExistsfirst 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.Keysreturns the keys, andItemsreturns the corresponding items. AFor Eachloop can go through the returned values.Countgives the number of key-item pairs.Remove keyremoves one pair, andRemoveAllempties the dictionary.CompareModecontrols 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
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA InputBox Dropdown List: What Works InsteadNo: Excel VBA’s standard InputBox cannot display a dropdown list of choices. To offer a real dropdown, use…
- VBA Contains String Function? How to Find Text with InStrYes—VBA can check whether one string contains another, but it does not have a built-in string method named…
- Excel VBA Date Picker: Controls, Options, and FixesExcel for Microsoft 365 on Windows does not include a universal VBA date-picker control that you can simply…
- Excel VBA: Open a Text File for WritingIn Excel VBA, use Open filePath For Output As #fileNo to create a text file or replace its…
- VBA: Open an Excel File from SharePointTo open a SharePoint workbook with VBA, the most dependable approach is to sync its document library with…
- Excel UserForm Scrollbar: Setup, Events, and ExamplesTo use a scrollbar on an Excel UserForm, add a ScrollBar control in the Visual Basic Editor, set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.