To use a class module with a VBA Dictionary, define a class to represent one record, create an instance of that class, and add the object to a Dictionary with a unique key. Retrieve the object with Set when you need to read or change its properties. The example below uses Excel for Microsoft 365 on Windows and the Windows Scripting.Dictionary object.
What You’ll Need
- Excel for Microsoft 365 on Windows, with macros enabled for a workbook you trust. Save a copy before running any macro; VBA changes cannot be undone with Ctrl+Z.
- A macro-enabled workbook saved as
.xlsmif you want to keep the VBA code. - Access to the Visual Basic Editor. In Windows Excel, press Alt+F11, or select Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon.
- The example uses late binding, so it does not require adding a reference to Microsoft Scripting Runtime. The Dictionary itself is a Windows scripting component; it is not a built-in VBA collection and is not reliably available in Excel for Mac.
Step-by-Step
- Decide what one object represents. A class module is a template for objects. Here, each object represents one product with a name and a unit price, while the Dictionary key is its SKU. This separates the record’s data from the lookup key. Choose a key that is stable and unique, such as an employee ID or order number, rather than a display name that might change or repeat.
- Insert and name the class module. In the Visual Basic Editor, choose Insert > Class Module. Select the new class in the Project Explorer, press F4 to show the Properties window if needed, and change its
(Name)property fromClass1toCProduct. Use that exact name in the code that creates product objects. The class name is not the same as a worksheet name or a variable name. - Add the product properties. Paste this code into the
CProductclass module. The private variables hold the values for each individual object; the public Property procedures let other code read and write those values.
Option Explicit
Private mProductName As String
Private mUnitPrice As Currency
Public Property Get ProductName() As String
ProductName = mProductName
End Property
Public Property Let ProductName(ByVal value As String)
mProductName = value
End Property
Public Property Get UnitPrice() As Currency
UnitPrice = mUnitPrice
End Property
Public Property Let UnitPrice(ByVal value As Currency)
mUnitPrice = value
End Property
Because these properties contain simple values, they use Property Let for assignment. For an object-valued property, VBA uses Property Set instead. Keeping the backing variables private helps prevent unrelated procedures from changing the class’s internal fields directly.
- Create a standard module and add the Dictionary procedure. In the editor, choose Insert > Module. Paste the following procedure into that standard module. It creates the Dictionary, sets its key comparison behavior before adding anything, creates a product, and stores the object under a SKU.
Option Explicit
Public Sub DemoProductDictionary()
Dim products As Object
Dim product As CProduct
Dim storedProduct As CProduct
Dim key As Variant
' Late binding avoids a required library reference.
Set products = CreateObject("Scripting.Dictionary")
products.CompareMode = vbTextCompare
Set product = New CProduct
product.ProductName = "Notebook"
product.UnitPrice = 4.5
products.Add "SKU-101", product
If products.Exists("sku-101") Then
Set storedProduct = products("sku-101")
Debug.Print storedProduct.ProductName, storedProduct.UnitPrice
' This edits the object held in the Dictionary.
storedProduct.UnitPrice = 4.75
End If
For Each key In products.Keys
Set storedProduct = products(key)
Debug.Print CStr(key), storedProduct.ProductName, _
storedProduct.UnitPrice
Next key
End Sub
The first printed price is 4.5; the loop prints 4.75 because the code changes the stored object before looping. The line setting CompareMode makes text keys case-insensitive, so SKU-101 and sku-101 refer to the same key. If you want case-sensitive keys, use vbBinaryCompare or leave the default, but make that choice before adding keys.
- Run the procedure and inspect the output. Save a copy of the workbook first. In the Visual Basic Editor, click inside
DemoProductDictionaryand press F5. Open the Immediate window with Ctrl+G to see the two Debug.Print results. In Excel for Mac, use Developer > Visual Basic to open the editor; keyboard shortcuts can vary with the keyboard and function-key settings. - Use the same pattern for your own records. Replace the product properties with the fields you need and replace the SKU with your chosen unique key. Create a fresh class instance for each record, set its properties, then call
.Add. To look up a record, check.Exists(key)first and assign the result withSet. For example,Set storedProduct = products("SKU-101")retrieves an object reference, not a copy of its fields. - Choose whether to use early or late binding. The example uses late binding:
productsis declaredAs Objectand created withCreateObject. For early binding on Windows, open Tools > References in the Visual Basic Editor, select Microsoft Scripting Runtime, then declare the variable asScripting.Dictionaryand create it withSet products = New Scripting.Dictionary. Early binding gives you Dictionary-specific editor assistance and compile-time checking, but a workbook that depends on that reference can be less portable if the reference is missing on another computer.
Common Mistakes
Leaving out Set for an object assignment. A class instance and a Dictionary are objects. Assign them with Set, as in Set product = New CProduct and Set storedProduct = products(key). Without it, VBA may report an object-required error or attempt an incompatible value assignment.
Adding a duplicate key. .Add raises an error if that key already exists. Check .Exists(key) before adding, and decide whether a duplicate should be ignored, reported, or replaced. To replace an item safely, remove the old entry and add the new object; do not assume that calling .Add updates it.
Setting CompareMode too late. Set CompareMode immediately after creating the Dictionary and before adding its first item. Changing it after keys have been added raises an error. Also remember that case-insensitive comparison can make two differently capitalized keys duplicates.
Confusing a class with a collection of fields. The Dictionary stores a reference to the class object. It does not automatically turn the class’s properties into separate Dictionary entries. Retrieve the object, then access properties such as storedProduct.ProductName.
Using a key that is not unique. If two rows share an SKU, the Dictionary cannot keep both as separate entries under that same key. Choose a genuinely unique key or make the item stored under each key a collection of objects.
Tips
Give the class a clear name, such as CProduct or CEmployee, and use descriptive property names. A class becomes especially useful when one record has several related fields or needs its own behavior, such as a method that calculates a line total. For a single value-to-value lookup, a Dictionary of simple values may be easier to maintain than introducing a class.
Keep the Dictionary variable and class variables declared with explicit types where practical. In the example, the Dictionary is late-bound as Object, but each stored item is declared As CProduct. This still gives the editor useful knowledge of the object’s properties. Use Option Explicit in every module so misspelled variable names are caught when the project compiles.
Keep key creation consistent. If keys come from worksheet cells, trim accidental spaces and decide how to handle blank values before adding them. A Dictionary treats keys as identifiers; it does not automatically clean or validate the text you supply. If you need to store a large number of objects, consider the memory required and avoid creating duplicate objects unnecessarily.
Remember that a retrieved class variable points to the same object held by the Dictionary. Changing storedProduct.UnitPrice changes that object, so a later lookup sees the changed price. If you need an independent copy, create another CProduct instance and copy the property values yourself; assigning one object variable to another does not clone the object.
Troubleshooting
“User-defined type not defined” for CProduct: Check that the class module’s (Name) property is exactly CProduct, that the class is in the same VBA project, and that the code was pasted into a class module rather than a standard module. Compile the project using Debug > Compile VBAProject to reveal other naming or syntax problems.
“ActiveX component can’t create object” at CreateObject: Confirm that you are running Excel on Windows and that the Windows scripting component is available. The Windows Scripting Dictionary is not a dependable option in Excel for Mac. For Mac-compatible VBA, use a VBA Collection with your own key-search logic or a Dictionary implementation designed for Mac, and verify that implementation’s setup and behavior rather than assuming Scripting.Dictionary is present.
“This key is already associated with an element”: The key already exists. Test with .Exists before adding, or remove the existing item with .Remove key before adding its replacement. If keys should differ only by capitalization, inspect the Dictionary’s CompareMode and the source values for duplicates.
“Object variable or With block variable not set”: Confirm that the class was created with Set product = New CProduct before you use its properties. Before retrieving a key, check .Exists; a missing key can cause a separate lookup error. When retrieving an existing item, use Set for the object variable.
The code runs but the Immediate window appears empty: Make sure the procedure reached its Debug.Print lines, then open the Immediate window with Ctrl+G in the Windows editor. If you are running a different macro, place a breakpoint or step through the procedure with F8 and check that the Dictionary contains the expected key.
Frequently Asked Questions
Can a VBA Dictionary store class objects?
Yes. Add a class instance as the item, for example products.Add "SKU-101", product. Retrieve it into a class variable with Set, then use its public properties or methods.
Does a Dictionary make a copy of the class object?
No. It stores an object reference. A variable retrieved from the Dictionary refers to that stored object, so changes to its properties are visible when you retrieve it again.
Do I need to enable Microsoft Scripting Runtime?
Not for the late-bound example in this guide. Early binding requires selecting Microsoft Scripting Runtime under Tools > References in the Windows Visual Basic Editor. Late binding avoids that reference but provides less editor assistance.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume so. The Windows scripting component is not a dependable cross-platform feature. For Mac support, use a compatible alternative such as a Collection with your own lookup routine, or a Dictionary implementation intended for Mac.
How do I update an object already in the Dictionary?
Check that the key exists, retrieve the object with Set, and change its properties. Because the retrieved variable refers to the stored object, you do not need to add it again just to save a property change.
When should I use a class instead of a simple Dictionary item?
Use a class when each keyed record has several related values, needs clear property names, or benefits from its own methods. For a straightforward lookup from one key to one simple value, storing that value directly is often simpler.
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
- Excel VBA AutoFilter Method Failed? Causes and FixesYes—“AutoFilter method of Range failed” is usually a VBA runtime error caused by an invalid or unsuitable range,…
- Excel VBA: Copy and Paste Filtered Cells Only SafelyYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to select cells that remain visible, then…
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
- Excel VBA: How to Close a UserForm After a Button ClickTo close an Excel VBA UserForm when a button is clicked, put Me.Hide in that button’s Click event…
- Excel VBA Break Loop: Keyboard Shortcuts and FixesTo interrupt a running Excel VBA loop on Windows, press Ctrl+Break (sometimes labeled Pause/Break); on a Mac, press…
- VBA Create New Workbook: Code, Options, and Tips in ExcelTo create a new Excel workbook with VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.