A VBA dictionary stores unique keys with associated values, making it useful for counting repeated entries or looking up information by name. In Excel for Microsoft 365 on Windows, create one with CreateObject("Scripting.Dictionary"); the example below counts each nonblank name in column A and writes the totals to a Results worksheet.
A VBA Dictionary Example
Set up a worksheet named Data with names in cells A2 downward, and a worksheet named Results for the output. The macro treats names that differ only by letter case as the same, trims leading and trailing spaces, and skips blank cells. It writes each distinct name and its count in columns A and B of Results.
Sub CountNamesWithDictionary()
' Count nonblank names in Data column A and write totals to Results.
Dim dict As Object
Dim ws As Worksheet
Dim resultWs As Worksheet
Dim cell As Range
Dim item As Variant
Dim key As String
Dim lastRow As Long
Dim outputRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set resultWs = ThisWorkbook.Worksheets("Results")
Set dict = CreateObject("Scripting.Dictionary")
' Set comparison behavior before adding any keys.
dict.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No names were found in Data column A."
Exit Sub
End If
For Each cell In ws.Range("A2:A" & lastRow).Cells
If Not IsError(cell.Value) Then
key = Trim$(CStr(cell.Value2))
If Len(key) > 0 Then
If dict.Exists(key) Then
dict(key) = dict(key) + 1
Else
dict.Add key, 1
End If
End If
End If
Next cell
resultWs.Range("A1").Value = "Name"
resultWs.Range("B1").Value = "Count"
outputRow = 2
For Each item In dict.Keys
resultWs.Cells(outputRow, 1).Value = item
resultWs.Cells(outputRow, 2).Value = dict(item)
outputRow = outputRow + 1
Next item
End Sub
Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. To run this example in Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, then run the procedure with F5 while the cursor is inside it. On a Mac, Option+F11 (or Fn+Option+F11, depending on the keyboard) opens the editor; Mac keyboard layouts and settings can affect the shortcut. The code’s Scripting.Dictionary dependency is intended for Windows, not Mac.
What a Dictionary Stores
A dictionary is a collection of key-and-value pairs. A key identifies an entry, and its associated item holds the information you want to store. In the example, a person’s name is the key and the number of times that name appears is the item. Each distinct key occurs once, even if the source contains that name many times.
This structure is different from a plain list, where the same value can appear repeatedly without being combined. It is also different from a worksheet lookup formula: a dictionary exists in VBA memory while the macro runs, unless your code writes its contents to cells or saves them elsewhere. That makes it useful for building a quick in-memory index, grouping records, or checking whether a value has already appeared.
Dictionaries are often used when a macro must repeatedly search a large set of values. Rather than scan all earlier rows each time it encounters a name, code can check whether a key exists and retrieve its item. The practical benefit depends on the size and shape of the task, but this key-based approach is generally a natural fit for repeated lookups and counting.
How the Example Works
CreateObject("Scripting.Dictionary") creates a dictionary object at runtime. The variable is declared As Object, so VBA does not need a compile-time reference to the Scripting Runtime library. This is called late binding. The worksheet variables point to the sheets named Data and Results in the workbook containing the macro, and the last-row calculation finds the last used cell in column A.
The loop examines each cell from A2 to that last row. It ignores Excel error values, converts other cell values to text, and trims spaces around the text. The blank check keeps an empty string from becoming a key. When the key is already present, the macro adds one to its stored count; otherwise, it creates the key with an initial count of one.
Finally, the code writes headers and loops through the dictionary’s keys, placing each key and its associated count into the Results sheet. Dictionary key order should not be treated as a sorting guarantee. If alphabetical output matters, sort the resulting worksheet range after the macro runs or add explicit sorting logic.
Useful Dictionary Properties and Methods
The example uses a small set of members that cover many common tasks:
Add key, itemcreates a new pair. It raises an error if the key already exists.Exists(key)returns True when that key is present, which is why the counting loop checks before adding.dict(key)retrieves or assigns the item for a key. In the example, assigning a larger number updates the count.Countreturns the number of distinct keys, not the total number of source rows.KeysandItemsreturn arrays of keys and their associated values, useful for looping or transferring data.Remove keydeletes one entry, whileRemoveAllempties the dictionary.
For example, after the sample macro processes rows containing Ana, Ben, Ana, and a blank, the dictionary has two keys: Ana with a count of 2 and Ben with a count of 1. Its Count is 2, while the sum of the stored counts is 3. Keeping those two meanings separate prevents a common reporting mistake.
Choosing How to Compare Keys
The CompareMode property controls how string keys are compared. The sample sets it to vbTextCompare, so Ana and ANA are treated as the same key. With vbBinaryCompare, the comparison is case-sensitive, and those spellings can be separate keys. The default behavior is binary comparison unless the code changes it.
Set CompareMode before adding any entries. Changing it after the dictionary contains data can cause a runtime error. Case-insensitive comparison does not remove spaces or standardize punctuation, which is why the sample separately uses Trim$. If values may contain inconsistent internal spaces, punctuation, or other formatting, define a normalization rule that suits the data before using each value as a key.
Normalization changes what counts as a match, so apply it deliberately. For instance, trimming outer spaces can be sensible for names copied from a worksheet, but removing punctuation could incorrectly combine distinct product codes. If codes can include leading zeroes, keep them as text in the worksheet and in VBA; converting them to numbers can change their identity.
Late Binding and Early Binding
Late binding, as used above, creates the object with CreateObject and declares it as a generic Object. It avoids requiring each user to enable a library reference in the VBA editor, which can reduce missing-reference problems when a workbook is shared among Windows users. The trade-off is that the editor provides less specific autocomplete and compile-time checking for dictionary members.
With early binding on Windows, open the VBA editor and choose Tools > References, then select Microsoft Scripting Runtime if it is available. The code can then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can make the object’s members easier to discover while writing code, but a workbook may fail to compile on a machine where that reference is unavailable or broken. For a shared workbook, consider the recipients’ environment before choosing.
Common Problems and Safe Use
- “ActiveX component can’t create object”: The Scripting.Dictionary object is a Windows COM component. Check that the macro is running in desktop Excel on Windows, rather than Excel for the web or an unsupported environment.
- Duplicate-key error: Calling
Addfor a key that already exists raises an error. Check withExistsfirst, or assign an item deliberately when updating or creating a key. - Unexpected separate entries: Case, spaces, punctuation, and data types can affect comparisons. Choose a comparison mode and normalize input consistently.
- Missing worksheet error: The example expects sheets named exactly Data and Results in the workbook that contains the macro. Rename the sheets or update the worksheet names in the code.
- Old results remain below new output: The code overwrites the current output rows but does not erase unused rows left by a previous, longer result. Use a dedicated Results sheet and clear only the previous output area if that behavior is desired.
Macros do not run in Excel for the web, and the Scripting.Dictionary approach is not a cross-platform solution for Mac Excel. If the workbook must run on a Mac, avoid assuming that a Windows COM object is available; consider a VBA Collection or a platform-independent approach that fits the required operations. Do not enable macros in workbooks from untrusted sources, and save a separate copy before running code that changes workbook contents.
Dictionary Compared With Collection and Worksheet Tools
A VBA Collection can store items and can use keys, so it is a built-in alternative for some tasks. However, dictionaries provide convenient operations such as Exists, Keys, and Items, and are especially straightforward for checking membership and maintaining counts. A Collection may suit simple ordered storage, but the choice depends on whether the code needs dictionary-specific lookup and comparison behavior.
Worksheet features may be a better choice when the goal is visible, refreshable analysis rather than VBA automation. A PivotTable can summarize repeated categories, and Excel formulas can produce counts in a worksheet without a macro. A dictionary is useful when the result is an intermediate step in a larger procedure, such as matching records, validating entries, or creating a custom report. It is not automatically better than a formula or PivotTable; choose the tool that makes the result easiest to maintain.
Frequently Asked Questions
How do I check whether a key exists in a VBA dictionary?
Call dict.Exists(key). It returns True if the key is present and False otherwise. Use that check before calling Add when duplicate keys are possible.
How do I get the number of entries in a dictionary?
Use dict.Count. It counts distinct keys. If each item stores a frequency, add the item values to get the total number of counted records.
Is a VBA dictionary case-sensitive?
By default, string comparison is binary and case-sensitive. Set dict.CompareMode = vbTextCompare before adding keys if capitalization should not distinguish entries.
Can I use Scripting.Dictionary in Excel for Mac?
The Windows Scripting.Dictionary COM object is not a dependable cross-platform choice for Mac Excel. For a workbook that must run on both platforms, consider a Collection or another approach and verify that it supports the operations your code needs.
Why does the macro say it cannot create the object?CreateObject("Scripting.Dictionary") depends on the Windows Scripting Runtime COM component. The error can occur in Excel for Mac, Excel for the web, or another environment that does not provide that component.
Can a dictionary sort its keys?
Do not rely on dictionary key order for sorted output. Write the keys and items to a worksheet and sort that range, or implement explicit sorting in VBA when the output order is important.
Related
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox With a Drop-Down List: Your OptionsNo: VBA’s built-in InputBox does not include a drop-down list, and Application.InputBox does not add one. To let…
- VBA UserForms: What They Are and How to Use ThemA VBA UserForm is a custom dialog window you build in the Visual Basic for Applications editor and…
- VBA Modify Pivot Tables in Excel: Examples and TipsYes. VBA can modify an Excel PivotTable by moving fields between rows, columns, filters, and values; setting report…
- Print GMT Time Using Excel VBATo print the current GMT time with Excel VBA on Windows, call the Windows GetSystemTime API and send…
- VBA Function List: Built-In Functions, Groups, and UseA VBA function list is a reference to built-in functions you can call from Visual Basic for Applications,…
- VBA Function Arguments: Types, Defaults, and ExamplesVBA function arguments are the values or variables supplied when a function is called; the function’s parameter list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.