To create a Dictionary in Excel VBA on Windows, declare an object and set it to CreateObject("Scripting.Dictionary"). You can then add unique keys, look up values, and test whether a key exists; for a macro that works without a VBA reference being selected, this late-bound approach is usually the simplest choice.
What a VBA Dictionary Does
A Dictionary stores pairs of related data: a key and an item (its value). For example, a product code can be the key and its description, quantity, or total sales can be the item. Unlike a worksheet lookup range, the dictionary is held in memory while the macro runs, so VBA can check a key with Exists rather than searching a range each time.
Each key must be unique. If a workbook contains the same customer ID on many rows, the dictionary can store that ID once and update its associated total as the macro reads each row. A dictionary is useful for deduplicating values, counting occurrences, joining data, and building fast lookups. It does not automatically sort entries, preserve a worksheet-style table, or save its contents after the macro ends; write its contents somewhere if you need to keep them.
Choose Early Binding or Late Binding
There are two common ways to create the object. Early binding tells VBA the Dictionary type in advance. In the Visual Basic Editor, open Tools > References and select Microsoft Scripting Runtime. Then declare a variable as Scripting.Dictionary and create it with New Scripting.Dictionary. The editor can provide type information and catch some spelling mistakes while you write code.
Late binding uses a generic object variable and asks Windows to create the scripting object at runtime. It does not require the user to select the reference in the workbook’s VBA project, which can reduce missing-reference problems when sharing a file among Windows computers. The trade-off is that the editor provides less Dictionary-specific assistance, and an unavailable scripting component will still prevent creation.
- Use early binding when you control the Windows environment and want clearer declarations and editor assistance.
- Use late binding when distributing a macro to other Windows users and avoiding a manually selected reference is useful.
- Do not mix the approaches casually: a variable declared as
Scripting.Dictionaryneeds the reference, even if a different line usesCreateObject.
Create a Dictionary in the VBA Editor
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then put the macro in the new module. The following late-bound line creates an empty Dictionary:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Set assigns an object to an object variable. The first line declares that variable; the second creates the Dictionary. In a complete procedure, declare the other variables you use as well, and use Option Explicit at the top of the module to require variable declarations. If you choose early binding instead, select the reference first and use Dim dict As Scripting.Dictionary followed by Set dict = New Scripting.Dictionary.
Before running any macro that changes cells, save a copy of the workbook. VBA actions generally cannot be undone with Ctrl+Z, so a backup is the safer way to recover from an unintended change. Run macros only from workbooks and sources you trust, and follow your organization’s macro-security rules.
Example: Combine Repeated Names and Amounts
This example reads names in column A and numeric amounts in column B on a worksheet named Data. It combines amounts for repeated names, then writes one row per name in columns D and E. It skips blank names and nonnumeric amounts. Use a blank output area, or change the output columns before running it; the procedure writes headers and results there but does not clear old content below the new results.
Option Explicit
Sub TotalAmountsByName()
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim r As Long
Dim nameKey As String
Dim amount As Variant
Dim itemKey As Variant
Dim outRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
nameKey = Trim$(CStr(ws.Cells(r, "A").Value2))
amount = ws.Cells(r, "B").Value2
If Len(nameKey) > 0 And IsNumeric(amount) Then
If dict.Exists(nameKey) Then
dict(nameKey) = CDbl(dict(nameKey)) + CDbl(amount)
Else
dict.Add nameKey, CDbl(amount)
End If
End If
Next r
ws.Cells(1, "D").Value = "Name"
ws.Cells(1, "E").Value = "Total"
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 code assumes row 1 contains headers and data starts in row 2. For example, if the rows contain Alex / 10, alex / 5, and Bri / 8, the text comparison setting treats Alex and alex as the same key. The output totals are Alex / 15 and Bri / 8. The dictionary returns entries in an order that should not be treated as a sorting rule; sort the output range separately if alphabetical order is required.
Keys, Items, and Case Sensitivity
Common Dictionary members include Add, Exists, Item, Keys, Items, Count, Remove, and RemoveAll. Use dict.Add key, value to add a new pair, and dict.Exists(key) to test for a key before accessing it. The Count property reports the number of stored pairs. Remove deletes one entry; RemoveAll empties the Dictionary.
Set CompareMode before adding any entries when you want text keys compared without regard to letter case. vbTextCompare makes names such as Alex and ALEX compare as equal. The default binary comparison distinguishes different letter casing. Changing the comparison mode after keys have been added can cause an error, so set it immediately after creating the object. Trimming a key, as the example does, also avoids treating leading or trailing spaces as meaningful differences.
A common trap is adding a key that already exists: Add raises an error for a duplicate key. Check with Exists first if duplicates are possible, then update the existing item or handle the duplicate according to the task. Also avoid using a missing key as though it were a harmless lookup. With this Dictionary object, reading the Item for a nonexistent key can create that key with an empty item. An explicit Exists check makes the intended behavior clearer.
Common Errors and How to Diagnose Them
- ActiveX component cannot create object: Windows cannot create the requested scripting object in the current environment. Confirm that the macro is running in desktop Excel on Windows and that the system permits the scripting runtime. A VBA reference setting alone cannot fix a missing or blocked component.
- User-defined type not defined: This commonly occurs when code declares
Scripting.Dictionarywithout selecting Microsoft Scripting Runtime under Tools > References. Select the reference or change to anObjectvariable with late binding. - Key already exists: The code called
Addwith a key already in the Dictionary. Test withExistsand decide whether to skip, replace, or combine the value. - CompareMode error: Set the comparison mode before inserting the first key, not after filling the Dictionary.
- Unexpected distinct keys: Check for spaces, inconsistent data types, blank cells, and differences in case. Normalize values deliberately, but do not remove characters that are significant identifiers.
When the code stops, use the VBA editor’s highlighted line and inspect the variable values. Confirm the worksheet name, the starting row, and the key column. A worksheet name that differs from Data will fail at the worksheet assignment, while a formula error or text in the amount column may be skipped by the example’s IsNumeric test.
Windows, Mac, and Excel for the Web
Scripting.Dictionary is a Windows scripting-runtime COM object. The examples above are for desktop Excel for Microsoft 365 on Windows; they should not be assumed to work in Excel for Mac because Mac Excel does not provide the same Windows COM/ActiveX environment. For a macro intended to run on Mac, consider a VBA Collection, arrays, or a cross-platform design, and verify that the chosen approach meets the need for unique-key lookup and comparison behavior.
Excel for the web does not run VBA macros. A workbook can be opened there, but the VBA procedure must be run in desktop Excel. On Mac, the Visual Basic Editor can generally be opened with Option+F11, sometimes requiring the Fn key depending on the keyboard; ribbon and keyboard behavior can vary by setup. The Windows shortcut Alt+F11 is not the Mac shortcut.
Dictionary Compared with Other Approaches
A Dictionary is a good fit when VBA needs to build or query key-value pairs repeatedly. A Collection can hold items and can be keyed, but it does not expose the same convenient Exists method; checking whether a key is present therefore takes different handling. Arrays are compact and useful for processing a fixed set of values, but they do not automatically enforce unique keys. Worksheet formulas or Excel tables can be preferable when users need visible, refreshable results rather than an in-memory structure hidden inside a macro.
For very large ranges, avoid reading and writing one cell at a time if performance becomes a problem. VBA can load a contiguous range into a Variant array, process the array in memory, and write the result back in a block. A Dictionary may still be used for the lookup, but the range transfer strategy can make a substantial difference. For small lists, the simpler cell-by-cell example is often easier to understand and maintain.
Frequently Asked Questions
Do I need to add a reference to use a VBA Dictionary?
Not when using late binding with Dim dict As Object and CreateObject("Scripting.Dictionary"). Early binding with Scripting.Dictionary requires selecting Microsoft Scripting Runtime in Tools > References.
How do I check whether a Dictionary contains a key?
Use dict.Exists(key). It returns True when the key is present and False otherwise. Check before adding or retrieving a key when the data may contain duplicates or missing entries.
How do I make Dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare immediately after creating the Dictionary and before adding keys. This applies text comparison without regard to letter case; it does not remove spaces or otherwise standardize the key.
Can I use Scripting.Dictionary on a Mac?
Do not rely on it for Mac Excel. It depends on a Windows scripting-runtime COM object, which is not the same environment available to Mac VBA. Use and verify a cross-platform alternative, such as a Collection or arrays, when Mac support is required.
Does a Dictionary keep its data after the macro ends?
No. Its entries exist in memory for the lifetime of the object. To retain the results, write the keys and items to worksheet cells, a file, or another persistent store before the procedure ends.
Related
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Offset: How to Reference Nearby CellsIn Excel VBA, Offset refers to a cell or range a chosen number of rows and columns away…
- Excel VBA: Find the Bottom Row of Data in a SheetTo find the bottom row in a particular Excel column, start at the worksheet’s last row and move…
- Excel VBA Data Entry Forms: Setup, Code, and TipsAn Excel VBA data entry form is a custom window for collecting information and writing it to a…
- VBA UserForm References: How to Show and Control FormsIn Excel VBA, reference a UserForm by its form class name, such as UserForm1, or create an explicit…
- Excel VBA: Change Cell and Range FormatsTo change a cell’s format with Excel VBA, assign a property such as NumberFormat, Font.Bold, or Interior.Color to…
- Excel VBA Scroll Bar Form Control: Setup, Uses, and CodeIn Excel for Microsoft 365 on Windows, insert a Form Control scroll bar from Developer > Insert >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.