Yes. To use a VBA Dictionary with early binding in Excel for Microsoft 365 on Windows, add a reference to Microsoft Scripting Runtime, then declare the object as Scripting.Dictionary. Early binding gives VBA access to the Dictionary’s type information while you write and compile code; it also means the project depends on that reference being available.
What early binding means for a VBA Dictionary
A Dictionary stores values under unique keys, much like a lookup table. For example, a key such as a product name can point to a quantity, or an employee ID can point to a name. The Dictionary object is part of the Microsoft Scripting Runtime library, a Windows COM library that VBA can use.
With early binding, VBA knows the object’s type before the code runs. You add the library reference in the Visual Basic Editor and declare a variable using the Dictionary type. With late binding, you declare the variable as a generic Object and create it at runtime instead. Both approaches use the same Dictionary features after the object exists; the difference is how VBA resolves the object and its members.
Early binding is not a separate kind of Dictionary. It is a way of connecting your VBA project to the Dictionary’s type information. That distinction matters when deciding whether the convenience of a reference is worth the deployment dependency.
How to add the Microsoft Scripting Runtime reference
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, the usual shortcut is Option+F11, though the function-key setting on the keyboard can affect how you enter it. The Dictionary reference described here is for Windows; Mac support is covered below.
- In the Visual Basic Editor, choose Tools > References.
- Find and select Microsoft Scripting Runtime in the list, then choose OK.
- In a standard module, declare the variable as
Scripting.Dictionaryand create an instance.
After selecting the reference, the type name should be available to the editor. If the library is not in the list, do not select a similarly named item at random. The missing or unavailable library may indicate a Windows installation or reference-registration issue. A project can also open with a broken reference on a different computer; the References dialog identifies entries marked as missing.
A working early-bound example
This example counts occurrences of product names in cells A2:A100 on the active worksheet. It uses text comparison, so names that differ only by capitalization are treated as the same key. Set CompareMode before adding any keys: changing it after the Dictionary contains data raises an error.
Option Explicit
Sub CountProducts()
Dim dict As Scripting.Dictionary
Dim cell As Range
Dim product As String
Dim key As Variant
' Create the Dictionary after adding the library reference.
Set dict = New Scripting.Dictionary
dict.CompareMode = vbTextCompare
' Count nonblank product names in A2:A100.
For Each cell In ActiveSheet.Range("A2:A100").Cells
product = Trim$(CStr(cell.Value))
If Len(product) > 0 Then
If dict.Exists(product) Then
dict.Item(product) = dict.Item(product) + 1
Else
dict.Add product, 1
End If
End If
Next cell
' Print each product and its count in the Immediate window.
For Each key In dict.Keys
Debug.Print CStr(key), dict.Item(key)
Next key
End Sub
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and this example reads worksheet data and writes results to the Immediate window rather than changing cells. Open that window in the Visual Basic Editor with Ctrl+G; on Mac, the equivalent shortcut can depend on the keyboard and editor setup.
Useful Dictionary members and behavior
The most frequently used members are straightforward, but a few behaviors can surprise someone expecting a worksheet lookup function:
Add key, itemadds a new key and its associated value. Adding a key that already exists raises an error.Exists(key)returns whether the key is present. Use it before reading an optional key.Item(key)gets or sets the value associated with a key. Assigning an item for a key that is not present adds that key. Reading a missing item can also create the key with an empty value, so check withExistswhen absence matters.KeysandItemsreturn arrays of keys and values, respectively. They are useful for iterating or transferring data, but are snapshots of the contents at the time they are requested.Countreports the number of key-item pairs;Removedeletes one key, andRemoveAllclears the Dictionary.
Keys are unique according to the selected comparison mode. The default is binary comparison, which generally treats uppercase and lowercase text as different. Setting CompareMode to vbTextCompare makes text-key comparisons case-insensitive. Choose the mode based on what counts as a duplicate in the data, not merely on how the keys look in a worksheet.
Benefits and trade-offs of early binding
Early binding is often the clearest choice when a Windows workbook is maintained in a known environment. The Visual Basic Editor can offer member lists and argument information as you type, and it can identify misspelled or invalid members at compile time rather than waiting for that line to run. The declared variable also documents what kind of object the code expects.
Those advantages come with a deployment consideration: the VBA project records a reference to the library. If that reference cannot be resolved on another computer, code that uses the type may fail to compile, often with a “User-defined type not defined” message or a broken-reference warning. This can happen when a workbook moves between computers, Windows installations, or office environments with different library registration.
For a workbook used only on managed Windows desktops, the reference is commonly a reasonable choice. For a file distributed widely, especially where reference consistency is uncertain, late binding can reduce one particular kind of reference problem. It does not make the Dictionary universally portable, however, because the underlying COM object still has to be available.
Early binding versus late binding
Late binding creates the object by name at runtime and declares its variable as Object. It avoids a compile-time reference to the Scripting Runtime, but the editor cannot provide the same type-specific assistance, and spelling or member problems are more likely to appear only when the code runs. Early binding makes the dependency visible and gives better editing support; late binding can make a Windows workbook less sensitive to a missing project reference.
Choose early binding when you control the Windows environment and value compile-time checking and clearer declarations. Consider late binding when you need to avoid a stored reference in a Windows workbook that will move among machines. Do not expect a dramatic speed difference to decide the issue: performance depends on the wider task, and for many ordinary Excel macros the maintainability and compatibility trade-offs are more important.
Both methods need error handling when input data can contain duplicates or unexpected values. In particular, using Add without checking for an existing key can raise a duplicate-key error. Conversely, relying on an item read to signal that a key is missing is unsafe because an absent key may be created by the read. Explicitly test with Exists when the distinction matters.
Common problems and how to diagnose them
- “User-defined type not defined”: Check that Microsoft Scripting Runtime is selected under Tools > References. If the project shows a missing reference, resolve it on that computer or change the design to late binding where appropriate.
- Duplicate-key error: The code called
Addfor a key already in the Dictionary. Test withExistsfirst, or update the existing item instead. - Error changing CompareMode: Set the mode immediately after creating the Dictionary and before inserting the first key.
- Unexpected case-sensitive matches: The default comparison is binary. Set
CompareModetovbTextComparebefore adding keys if capitalization should not distinguish them. - Unexpected empty or extra entry: Check whether code reads
Itemfor a key without first checkingExists. A missing-key read can create an entry.
When debugging, inspect Count and the keys after the section of code that builds the Dictionary. This helps distinguish a comparison-rule problem from a data-cleaning problem, such as trailing spaces or blank cells. Trimming text, as in the example, addresses surrounding spaces but does not standardize every possible difference in spelling or punctuation.
Windows, Mac, and Excel for the web
Microsoft Scripting Runtime and its Scripting.Dictionary COM object are Windows technologies. Excel for Mac supports VBA in general, but this Windows COM library is not a dependable cross-platform Dictionary solution there. Late binding with CreateObject does not solve that limitation: it still asks the system to create the same COM object. Excel for the web does not run VBA macros.
If a workbook must run on both Windows and Mac, test the complete solution on both platforms and choose a data structure supported by both. VBA’s built-in Collection can cover some key-value use cases, though its interface and behavior differ from a Dictionary. A custom class or an array-based approach may be appropriate for other tasks. The right substitute depends on whether you need fast membership checks, easy key enumeration, case-insensitive keys, or simple storage.
Alternatives when Dictionary is not suitable
A Dictionary is useful for in-memory lookups and counting, but it is not automatically the best tool for every workbook. For a small fixed set of values, a Collection may be sufficient. For tabular data that users need to inspect or filter, an Excel table may be more transparent than keeping all results inside a macro. For formula-based lookup work, worksheet functions may be more maintainable, depending on the Excel version and the task.
There is no built-in VBA statement that makes Scripting.Dictionary available on every platform. If portability is a firm requirement, decide on the supported platforms first, then select a data structure and test its behavior there. If the workbook is Windows-only and the library reference is stable, early binding remains a direct, readable way to use the Dictionary.
Frequently Asked Questions
Is Scripting.Dictionary early-bound by default?
No. It is early-bound when the project references Microsoft Scripting Runtime and the variable is declared as Scripting.Dictionary. Declaring it as Object and creating it with CreateObject is late binding.
Do I need to set a reference for late binding?
No project reference is needed for late binding. The Windows system still needs to be able to create the Scripting Runtime object, so late binding does not make this COM-based Dictionary available on Mac or Excel for the web.
Should I use New Scripting.Dictionary or CreateObject?
Use New Scripting.Dictionary with an early-bound declaration when the reference is set and you want type information while coding. CreateObject("Scripting.Dictionary") is the usual late-bound creation method when the variable is declared as Object.
Does a Dictionary keep keys in alphabetical order?
Do not rely on Dictionary enumeration as an alphabetical sort. If ordered output is required, sort the keys separately before writing or displaying them.
Why does reading a missing key change the Dictionary?
The Dictionary’s Item property can create an entry when a missing key is read. Call Exists(key) before accessing the item whenever you need to distinguish a missing key from a stored empty value.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Enabled? What It Means and How to Use ItYes. Microsoft Excel for Microsoft 365 on Windows can run VBA macros, provided they are allowed by your…
- VBA AutoFilter Criteria1: Syntax, Examples & Tips in ExcelIn VBA, Criteria1 is the first filter condition passed to Excel’s AutoFilter method; for example, Criteria1:="East" keeps rows…
- 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 COUNTA: Count Nonblank Cells in MacrosIn Excel VBA, count nonblank cells with Application.WorksheetFunction.CountA and pass it a range. For example, Application.WorksheetFunction.CountA(Worksheets("Data").Range("A2:A100")) returns the…
- Excel VBA in VS Code: What Works and What You NeedYes, you can use Visual Studio Code (VS Code) to edit Excel VBA code, but VS Code cannot…
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.