No: a VBA Scripting.Dictionary cannot be declared as a Public Const, because VBA constants must hold compile-time values and a dictionary is a runtime object. To share one dictionary across procedures, declare a public object variable in a standard module and create it at runtime; if you need tighter control, keep it private and expose specific operations instead.
Why a dictionary cannot be a constant
A VBA Const declaration describes a value that the compiler can determine without creating or running an object. Numeric values and strings are common examples. A dictionary, by contrast, is an instance of a class: VBA must create it, allocate its internal storage, and then let code add or remove entries. That lifecycle requires an object variable and the Set keyword, not a constant declaration.
This will not compile:
Public Const MyDictionary As Scripting.Dictionary = New Scripting.Dictionary
The problem is not that the dictionary is empty, or that it has not yet been used. An object reference cannot be a VBA constant at all. The same restriction applies to other objects, such as a Collection, a worksheet, or a workbook. A scalar constant such as Public Const DefaultLimit As Long = 50 is valid because its value is fixed and does not refer to a runtime object.
What “Public” means in VBA
Public controls where a declaration can be accessed; it does not make a value constant or read-only. Put a public declaration in a standard module—one created with Insert > Module—and procedures in other modules in the same VBA project can refer to it. A public variable is shared state: any procedure with access can change it, replace its object reference, or set it to Nothing.
That distinction matters when choosing a design. A public dictionary can be convenient for a small workbook with a clear initialization routine. It can also make a larger project difficult to reason about, because the dictionary’s contents may change from many different places. Naming a variable to sound fixed does not prevent edits; VBA has no built-in declaration that makes a dictionary reference and all its entries immutable.
Declare and initialize a shared dictionary
For early binding, first enable the Microsoft Scripting Runtime reference if it is available in your VBA environment. In the Visual Basic Editor, use Tools > References and select Microsoft Scripting Runtime. Then put a declaration and an initialization procedure in a standard module. Initialize the dictionary before any procedure tries to use it.
Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The following example creates the shared dictionary only once and sets its key-comparison mode before adding entries.
Option Explicit
Public SharedItems As Scripting.Dictionary
Public Sub InitializeSharedItems()
' Create the shared dictionary if it does not exist yet.
If SharedItems Is Nothing Then
Set SharedItems = New Scripting.Dictionary
' Set this before adding keys.
SharedItems.CompareMode = vbTextCompare
End If
End Sub
Another procedure can call InitializeSharedItems and then use the same object. For example, this demonstrates adding and reading an entry; it is not a constant or a protected lookup table.
Public Sub AddExampleItem()
Dim itemCount As Long
' Ensure the shared object has been created.
InitializeSharedItems
SharedItems("Apples") = 12
itemCount = SharedItems("Apples")
Debug.Print itemCount
End Sub
In that example, the Immediate window displays 12. The dictionary persists in memory while the VBA project is running, but it is not automatically saved inside the workbook as a permanent object. If its contents must survive closing the file, write them to a worksheet, a file, or another storage location and load them when needed.
Choosing early binding or late binding
Early binding declares the variable as Scripting.Dictionary and creates it with New Scripting.Dictionary. The editor can provide member suggestions, and VBA can check the type when compiling. The trade-off is a dependency on the relevant reference. If another computer cannot resolve that reference, the project may show a missing reference and fail to compile until the dependency is fixed.
Late binding declares the variable as Object and creates the dictionary using CreateObject. This avoids a compile-time reference to the Scripting Runtime, but member names are not checked as specifically by the compiler and IntelliSense is more limited. It does not make the dictionary a constant, and it does not remove platform limitations associated with creating the underlying COM object.
Option Explicit
Public SharedItems As Object
Public Sub InitializeSharedItemsLateBound()
' Create the object only when needed.
If SharedItems Is Nothing Then
Set SharedItems = CreateObject("Scripting.Dictionary")
' Choose key comparison before adding entries.
SharedItems.CompareMode = vbTextCompare
End If
End Sub
Use one approach consistently in a project. If you declare the shared variable as Object, VBA will not enforce that it contains a dictionary; code could accidentally assign a different object. Early binding is usually clearer when you control the workbook environment and the reference is available. Late binding can be useful when avoiding a reference is important, but it still needs to be tested in the intended Excel versions and operating systems.
When and where to initialize it
A public object variable starts as Nothing; the declaration alone does not create a dictionary. A procedure that accesses it before initialization can raise an error. A guarded initializer, as above, gives callers one predictable way to get a usable object. For a workbook-specific setup, you can call the initializer from the Workbook_Open event in the ThisWorkbook module, or call it explicitly from the macro that first needs the dictionary.
Initialization timing depends on what the dictionary represents. If it is rebuilt from worksheet data, load it after that data is available and decide what should happen if the data changes. If it is temporary state for one operation, creating it inside that operation may be simpler than keeping a public variable. Do not assume a public object survives closing Excel, resetting the VBA project, or an unhandled runtime error that resets project state; after a reset, initialize it again.
If the dictionary is populated more than once, define whether initialization should preserve existing entries, clear them, or rebuild from scratch. A procedure that only creates the object when it is Nothing will not clear old entries on later calls. That behavior may be right for a shared cache, but it can cause stale results if callers expect a fresh dataset.
Common problems and safeguards
- “Object variable or With block variable not set.” The variable is still
Nothing. Call the initializer before accessing a key or property, and check the object state at public entry points. - Changing comparison mode too late. Set
CompareModebefore adding keys. Changing it after entries exist can raise a runtime error.vbTextComparetreats keys with different letter case as equivalent; the default binary comparison distinguishes case. - Duplicate keys. A dictionary has one item per key. Adding a key that already exists with
Addcauses an error; assignment such asSharedItems("Apples") = 12adds or replaces the item. Choose deliberately if replacing a value would hide a data problem. - Missing keys. Check
Existsbefore reading a key when it may not be present. Otherwise, code can fail or behave differently from what the caller expects. Decide separately what an absent key should mean; a missing value is not automatically the same as a stored zero or empty string. - Unexpected edits from other procedures. Because a public variable is accessible project-wide, any procedure can change its contents. Limit direct access, use descriptive names, and keep updates in a small number of procedures where possible.
- Reference or platform trouble. Early-bound code depends on a usable library reference. Late binding avoids that reference declaration but still relies on the object being creatable. The Scripting Runtime is a Windows COM component; do not assume
Scripting.Dictionarywill be available in Excel for Mac. Test on the target platform or choose a more portable alternative.
Safer designs than a public mutable variable
If callers only need a few actions, keep the dictionary private inside a class module and expose methods such as “add item,” “get value,” or “does key exist.” This hides the actual storage variable and lets the class enforce rules, such as rejecting duplicates or returning a defined result for a missing key. It is stronger encapsulation than a public variable, although it is not automatic immutability: any public method that changes data still changes the dictionary.
A public function that returns the dictionary can centralize initialization, but it does not make the returned object read-only. Callers still receive an object reference and can modify its contents. If the requirement is that callers must not alter the data, expose lookup methods or copies of values instead of returning the dictionary itself. This is a common source of confusion: controlling how an object is obtained is not the same as restricting what its reference can do.
For simple fixed configuration, use separate public constants for scalar settings rather than trying to package them into a constant dictionary. For example, a few fixed text labels or numeric limits can be constants. If there are many related values, a private lookup routine, a worksheet table, or a class with read-only accessors may communicate the intended rules more clearly.
Alternatives and when they fit
A Collection is built into VBA and can hold objects or values, but it does not provide the same dictionary interface for checking keys and retrieving items by key. Arrays work well for ordered data with a known size or index scheme, but searching them by a text key requires additional code. A worksheet table is often a better choice when users need to inspect or edit the mapping, or when data must persist between workbook sessions.
Choose based on the actual need rather than on whether a variable can be made “constant.” A dictionary is useful for fast key-based lookup during a run; a table is visible and persistent; constants suit fixed individual values; and a class is helpful when you need to control access and behavior. These options can also be combined: read a table at startup, load the values into a private dictionary for lookups, and provide only the lookup methods that the rest of the project needs.
Frequently Asked Questions
Can I write Public Const dict As Object in VBA?
No. Declaring the type as Object does not change the rule. A constant cannot hold an object reference; declare a public object variable and assign an instance at runtime instead.
Can a public dictionary be read-only?
Not if other code can directly access the dictionary object. A public variable exposes a mutable reference. Keep the object private and offer lookup-only methods if callers should not be able to add, remove, or replace entries.
Does a public dictionary keep its values after I close the workbook?
No. The in-memory object is not saved as a persistent dictionary when the workbook closes. Store its data in the workbook or another storage medium, then rebuild the dictionary when the project runs.
Should I use early binding or late binding?
Use early binding when you can manage the Scripting Runtime reference and want clearer type checking and editor assistance. Consider late binding when avoiding a reference is useful, but verify that the COM object is available on every target computer.
Will Scripting.Dictionary work in Excel for Mac?
Do not rely on it being available there: it is a Windows COM component, and Mac VBA does not provide the same COM environment. For a workbook that must run on both platforms, consider arrays, collections, worksheet data, or a tested cross-platform design.
Related
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Collection: Methods and Practical ExamplesVBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array,…
- Excel VBA: List All Sheets in a WorkbookTo list every sheet in the workbook that contains your VBA code, loop through its Sheets collection and…
- String in VBA: Meaning, Types, and Practical ExamplesIn VBA, String is the data type used to store text, such as names, messages, codes, and text…
- Excel VBA InputBox and IME: How Text Entry Works in WindowsExcel VBA’s built-in InputBox accepts text entered with a Windows IME (Input Method Editor), such as Japanese, Chinese,…
- Excel VBA: How to Add a Threaded Comment to a CellIn Excel for Microsoft 365 on Windows, add a threaded comment to a cell with the VBA method…
- Excel VBA Switch Case: Select Case Syntax and ExamplesExcel VBA does not have a switch-case statement named Switch; its equivalent is Select Case. Use it to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.