VBA Dictionary Class Modules: A Practical How-To

VBA & macros · Updated November 16, 2026

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 .xlsm if 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

  1. 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.
  2. 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 from Class1 to CProduct. 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.
  3. Add the product properties. Paste this code into the CProduct class 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.

  1. 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.

  1. Run the procedure and inspect the output. Save a copy of the workbook first. In the Visual Basic Editor, click inside DemoProductDictionary and 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.
  2. 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 with Set. For example, Set storedProduct = products("SKU-101") retrieves an object reference, not a copy of its fields.
  3. Choose whether to use early or late binding. The example uses late binding: products is declared As Object and created with CreateObject. For early binding on Windows, open Tools > References in the Visual Basic Editor, select Microsoft Scripting Runtime, then declare the variable as Scripting.Dictionary and create it with Set 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.