VBA has no built-in global dictionary type, but in Excel for Microsoft 365 on Windows you can create a Scripting.Dictionary object and declare it as a Public variable in a standard module. That makes the same dictionary available to procedures throughout that workbook’s VBA project while it is open; it does not save the dictionary’s contents between sessions.
What “global dictionary” means in VBA
A dictionary stores pairs of keys and values. A key identifies an item, and the value is the information associated with it. For example, a product code could be a key and its product name the value. Unlike a worksheet lookup formula, a dictionary lives in memory while VBA runs, so code can check for a key or retrieve its value without repeatedly searching a range.
“Global” describes the variable’s scope, not a special dictionary feature. In VBA, a variable declared with Public at the top of a standard code module can be used by procedures in other modules in the same VBA project. A dictionary is still an object that must be created with Set before use. Declaring the variable globally does not create or fill the object automatically.
The distinction matters: a global dictionary is shared by code in one project, but it is not automatically shared with every open workbook, every Excel user, or future sessions. Its contents are held in memory rather than written into the workbook file.
Where to declare the global variable
In the Visual Basic Editor, insert a standard module using Insert > Module. Put the declaration at the top of that module, outside any Sub or Function. A variable declared inside a procedure is local to that procedure, even if the procedure itself is public. A variable declared in a worksheet, workbook, or class module has different object-module behavior; for a project-wide global, use a standard module.
For example, this declaration makes the variable available across the project:
Public gLookup As Object
The g prefix is only a naming convention that signals a shared variable; VBA does not require it. Choose a descriptive name and avoid generic names such as Dictionary that can be confusing beside the object type. If you use Option Explicit at the top of the module, VBA will require variables to be declared, which helps catch spelling mistakes.
Choose early binding or late binding
There are two common ways to declare and create the dictionary on Windows. Early binding gives the variable the specific Scripting.Dictionary type and can provide IntelliSense in the editor. To use it, open the VBA editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare the variable with that type and create it with New.
Late binding declares the variable as Object and creates the dictionary at runtime with CreateObject. It avoids requiring the project’s users to have the same reference selected, so it is often more convenient for a workbook shared among Windows users. The trade-off is less editor assistance and the fact that spelling or member errors are detected later, when the code runs.
For a Windows workbook that will be distributed, late binding is a practical default. Early binding can be useful during development, particularly when you want IntelliSense, but a missing or mismatched reference can cause a “MISSING” reference or a compile error on another computer. Either approach still relies on the Windows scripting runtime being available.
Create, populate, and use a dictionary
This late-bound example belongs in a standard module. It creates a shared dictionary, sets it to compare text keys without regard to letter case, adds an example entry, and offers a function that safely checks for a key before reading its value.
Option Explicit
Public gLookup As Object
Public Sub InitializeLookup()
' Create a new dictionary for this VBA project.
Set gLookup = CreateObject("Scripting.Dictionary")
' Set comparison mode before adding any keys.
gLookup.CompareMode = vbTextCompare
gLookup.Add "SKU-104", "Notebook"
End Sub
Public Function LookupItem(ByVal key As String) As String
' Initialize on demand if no dictionary exists yet.
If gLookup Is Nothing Then InitializeLookup
If gLookup.Exists(key) Then
LookupItem = CStr(gLookup.Item(key))
Else
LookupItem = "(not found)"
End If
End Function
After initialization, another procedure in the same project can call LookupItem("sku-104") and receive Notebook, because the example uses a case-insensitive comparison mode. The example’s function also creates the dictionary on demand. In a larger project, you may prefer to initialize it in one deliberate place instead, so you can control when data is loaded and avoid silently rebuilding an empty dictionary.
To run initialization when the workbook opens, put this event procedure in the ThisWorkbook code module, not in the standard module:
Private Sub Workbook_Open()
' Prepare the shared dictionary when this workbook opens.
InitializeLookup
End Sub
Opening-event code runs only when macros are enabled and the workbook is opened in a way that triggers the event. You can also call InitializeLookup from a button or another procedure. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. Save macro-enabled workbooks as .xlsm if they need to retain VBA code.
Dictionary behavior to understand
A dictionary’s .Exists(key) method checks whether a key is present, and .Item(key) retrieves its value. Use .Add key, value when a key should be new; adding a duplicate key raises an error. Assigning to .Item(key) can create a new entry or replace the value for an existing key, so choose deliberately depending on whether duplicates indicate a problem or an update.
Keys must be unique within the dictionary. Values can be different kinds of data, including strings, numbers, or object references, but code should handle the value type it expects. If you store an object as a value, object assignment and retrieval require the appropriate use of Set. For simple text-to-text lookups, the example’s CStr conversion makes the expected return type explicit.
The comparison mode also affects how text keys are treated. vbBinaryCompare distinguishes uppercase and lowercase characters; vbTextCompare treats text keys as equal without that distinction. Set CompareMode before adding entries. Trying to change it after the dictionary contains data can raise an error. A dictionary is not a sorted table, so do not rely on its keys being returned in a particular order.
Useful members include .Count for the number of entries, .Remove(key) to delete one entry, .RemoveAll to clear the dictionary, and .Keys or .Items to retrieve arrays of keys or values. These are handy for iteration, but the returned arrays are not a replacement for the dictionary’s key lookup behavior.
Lifetime, reset, and persistence
A public dictionary remains available while the VBA project is running normally, but it is an in-memory variable, not saved workbook data. Closing the workbook ends that in-memory state. If the user closes and reopens the file, the workbook-open procedure can create a fresh dictionary, but the code must repopulate it from a worksheet, file, database, or other source if the entries are needed again.
The value can also be lost when VBA resets, such as after using the Reset button in the editor, encountering certain unhandled errors, or stopping execution. A common symptom is an “Object variable or With block variable not set” error when code tries to use the global after it has been reset. Check whether the object is Nothing and initialize it before use, or ensure the startup and error-handling paths create it reliably.
If entries must persist, store them somewhere persistent. A hidden or visible worksheet table is often sufficient for a workbook-only lookup; a text file or database may suit larger or shared datasets. Treat the dictionary as a fast in-memory representation that can be rebuilt from that source, rather than as the only copy of important data.
Common problems and practical checks
- “Object variable not set”: The public variable was declared but never instantiated, or the VBA project was reset. Create the object with
Setbefore using its members. - “ActiveX component can’t create object”: Windows could not create the scripting dictionary. Confirm the environment supports
Scripting.Dictionaryand that the code is running on Windows rather than relying on the COM object on Mac. - Duplicate-key error: The code called
.Addfor a key that already exists. Check with.Existsfirst, or intentionally assign a replacement value through.Item(key). - Unexpected case matching: Set the intended
CompareModebefore adding keys. If the dictionary is already populated, create a new dictionary with the desired mode and copy the entries. - Global variable not visible: Confirm the declaration is in a standard module and uses
Public, not inside a procedure. Also check that the calling code is in the same VBA project. - Missing reference: If using early binding, verify Microsoft Scripting Runtime is selected under Tools > References. Late binding avoids that reference dependency on Windows, but does not make the COM object portable to every platform.
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11 or use Developer > Visual Basic. On a Mac, the editor shortcut may be Option+F11 or Fn+Option+F11, depending on the keyboard settings. The scripting dictionary approach above is for Windows; Excel for Mac does not provide the same Windows COM-based Scripting.Dictionary creation path. For a workbook that must run on both platforms, choose a cross-platform data structure or use worksheet data and test the full workflow on each target platform.
When to use a dictionary instead of alternatives
A dictionary is a strong fit when code needs repeated lookups by unique key, such as matching an employee ID to a department or a product code to a description. It can also help build a unique list: test each value with .Exists before adding it. For a few entries or a simple sequence, an array may be easier. A VBA Collection can hold items and can be accessed by keys, but it has a different interface and does not provide the same convenient .Exists check.
For data that users need to inspect, edit, or preserve, a worksheet table is often better than keeping the only copy in memory. For lookups that must be calculated directly in cells, Excel functions such as XLOOKUP may be a more transparent choice in Microsoft 365. A VBA dictionary is most useful when the surrounding task is already procedural VBA, repeated lookups matter, and the project can manage initialization and persistence explicitly.
Frequently Asked Questions
Is there a built-in global Dictionary type in VBA?
No. VBA provides ways to declare variables with project-wide scope, but the commonly used Scripting.Dictionary is an object supplied through the Windows scripting runtime. Declare a public object in a standard module and create the dictionary before using it.
Does a Public dictionary work in every open workbook?
No. A public variable is available to code in its own VBA project, not automatically to all workbooks in Excel. To share code across workbooks, put it in an add-in or another shared VBA project and design how each workbook accesses it.
Does a global dictionary keep its entries after closing Excel?
No. Its entries are held in memory and disappear when the project closes or resets. Save the data to a worksheet or another persistent source, then rebuild the dictionary when needed.
Can I use Scripting.Dictionary in Excel for Mac?
The Windows CreateObject("Scripting.Dictionary") method depends on COM and is not a reliable cross-platform solution for Excel for Mac. For Mac-compatible VBA, consider a Collection, arrays, or worksheet-based data, and verify behavior on the specific versions you support.
Should I use early binding or late binding?
On Windows, early binding offers a specific type and editor assistance but requires the scripting runtime reference. Late binding avoids that project reference and is often easier to distribute among Windows users. Neither approach makes this COM dictionary a portable Mac solution.
Related
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA ComboBox: Load a UserForm List From RangeTo fill an Excel VBA UserForm ComboBox from cells, set its RowSource property to a worksheet range, or…
- Excel VBA Copy Range to Another Sheet: Methods and TipsTo copy a range to another worksheet with Excel VBA, qualify the source and destination sheets and use…
- Excel VBA Select Case: Syntax and ExamplesIn Excel VBA, Select Case runs one block of code based on the value of an expression, making…
- Date Picker in VBA UserForms: Setup and AlternativesExcel for Microsoft 365 on Windows does not provide a built-in date picker control for VBA UserForms. To…
- VBA Dictionary Functions and How to Use ThemIn VBA, a Dictionary is an object for storing unique keys alongside values, and its main functions are…
- Input Box in Excel VBA: Use InputBox and Get DataIn Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.