VBA Copy Dictionary: Methods, Code, and Deep Copies

To copy a VBA Scripting.Dictionary, create a second dictionary and add each key and item from the first; there is no built-in Clone method. The copy below also preserves the source dictionary’s comparison mode. It makes a shallow copy, so object values inside the two dictionaries remain shared references.

What copying a dictionary means

A Scripting.Dictionary stores key-item pairs and provides methods such as Add, Exists, and Remove. Copying one usually means creating another dictionary with the same keys and values, so you can add or remove entries in the new dictionary without changing the original dictionary’s entries.

There is an important distinction between copying a dictionary and assigning another variable to the same dictionary. For example, Set second = first does not make a copy: both variables refer to the same object. Changes made through either variable affect that one dictionary. To create an independent set of entries, initialize a new dictionary and transfer the pairs.

A dictionary’s keys and items are transferred, but how each item behaves depends on its type. Numbers and strings are values; an item that is an object is a reference. That distinction determines whether a change to a copied item can still affect something held by the original.

Copy a dictionary with a loop

This function uses late binding, so it does not require adding a reference to Microsoft Scripting Runtime. Put it in a standard VBA module. It copies each key, preserves CompareMode, and handles object items without accidentally treating them as ordinary values.

Private Function CopyDictionary(ByVal source As Object) As Object
    Dim result As Object
    Dim key As Variant

    ' Reject an uninitialized dictionary reference.
    If source Is Nothing Then
        Err.Raise vbObjectError + 513, "CopyDictionary", _
                  "The source dictionary is Nothing."
    End If

    ' Create a separate dictionary and set its comparison rule first.
    Set result = CreateObject("Scripting.Dictionary")
    result.CompareMode = source.CompareMode

    ' Transfer each key and item.
    For Each key In source.Keys
        If IsObject(source.Item(key)) Then
            result.Add key, Empty
            Set result.Item(key) = source.Item(key)
        Else
            result.Add key, source.Item(key)
        End If
    Next key

    Set CopyDictionary = result
End Function

CompareMode must be set before adding entries to the destination. The property controls how string keys are compared, such as whether differently cased keys count as the same key. Setting it after keys have been added can cause a run-time error. Copying the source setting avoids silently changing how later lookups or additions behave.

The function assumes the source is a valid dictionary object. If you want to call it from another module, change Private to Public, or keep the call and function in the same module. The returned object is a new dictionary; it is not the original object.

Use the copied dictionary

Here is a small example that creates a dictionary, copies it, and changes one entry in the copy. The original retains its original value because the example stores strings, not shared object references.

Public Sub ExampleDictionaryCopy()
    Dim original As Object
    Dim copied As Object

    ' Create a dictionary with two text values.
    Set original = CreateObject("Scripting.Dictionary")
    original.Add "Color", "Blue"
    original.Add "Size", "Large"

    ' Make a separate dictionary, then change only the copy.
    Set copied = CopyDictionary(original)
    copied.Item("Color") = "Green"

    Debug.Print original.Item("Color")  ' Blue
    Debug.Print copied.Item("Color")    ' Green
End Sub

Save a copy of the workbook before running a macro that changes workbook data: VBA macros cannot be undone with Ctrl+Z. This sample only prints values to the Immediate window and changes an in-memory dictionary, but the precaution matters when adapting the code to edit cells or other workbook content. In the VBA editor, open the Immediate window with Ctrl+G on Windows.

Late binding and early binding

The examples use late binding: CreateObject("Scripting.Dictionary") creates the object at run time, and variables are declared As Object. This avoids requiring a project reference and can make a workbook easier to move between Windows computers with compatible Office installations. The trade-off is that the VBA editor cannot provide the same dictionary-specific autocomplete and compile-time type checking.

With early binding, declare variables as Scripting.Dictionary and create one with New Scripting.Dictionary. To enable that type on Windows, open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Early binding can make code easier to explore and catch some mistakes sooner, but a missing or unavailable reference can prevent a project from compiling on another computer.

On Excel for Mac, do not assume the Windows scripting runtime is available just because the VBA editor is present. COM-based object creation and references can differ by platform and Office version. Test the workbook on each target Mac version; for code that must run across platforms, consider a VBA Collection or a custom class-based structure instead.

Shallow copy versus deep copy

The function makes a shallow copy. It creates a new dictionary and transfers the entries, but it does not recursively duplicate objects stored as items. If an item is another dictionary, a worksheet, or a custom class instance, both dictionaries point to that same object. Changing the object through one reference can therefore be visible through the other, even though adding or removing dictionary entries does not affect the other dictionary.

For instance, suppose a dictionary’s Customer item refers to a custom customer object. Copying the outer dictionary transfers that reference. If code then changes a property of the customer through the copied dictionary, it is changing the same customer object that the original dictionary refers to. The dictionary containers differ; the contained customer does not.

A deep copy means duplicating the contained objects as well. There is no universal safe deep-copy routine for VBA dictionaries because the code must know how to construct each object and copy its properties. A nested dictionary can be handled recursively, but custom classes may need their own copy method. Circular references also need special handling to avoid repeatedly copying the same objects. Arrays and variants containing objects need deliberate treatment if an independent copy of every nested value is required.

Before choosing a deep-copy strategy, list the item types the dictionary can hold. If all items are simple values, the loop above is often sufficient. If items include objects, decide whether sharing those objects is intended. If it is not, implement copying for the specific object types rather than assuming that a generic transfer of key-item pairs duplicates them.

Common mistakes and errors

Using Set second = first and expecting a copy: this creates another reference to the same dictionary. Use a loop to create a separate object.
Looking for a Clone method: the standard Scripting.Dictionary object does not provide a built-in dictionary-cloning method. A helper function or your own copy routine is needed.
Copying Keys or Items and expecting a dictionary: those properties provide arrays of keys or items, not another dictionary. They can be useful for iteration, but you still need to build the destination object.
Setting CompareMode too late: set it on the new, empty dictionary before adding any keys. Otherwise VBA can raise an error when you try to change the comparison setting.
Ignoring object references: a new dictionary does not guarantee that objects stored inside it are new objects. Check whether shared nested objects are acceptable.
Assuming the runtime exists on every computer: test late-bound creation on the target Windows or Mac installation. A run-time error at CreateObject can indicate that the required component is unavailable.

If you see an error while copying, check the failing line and the item type involved. A duplicate-key error may indicate that the destination was not empty or that the copy loop is being run twice against the same destination. A type mismatch can point to an item that needs object-aware assignment. If the error occurs when creating the object, check platform and runtime availability rather than the loop itself.

Choosing the right approach

For an ordinary dictionary of text, dates, numbers, Boolean values, or other simple values, a loop that adds each key and item is straightforward and easy to review. The function above extends that approach to object items by retaining their references correctly. Choose early or late binding based on the deployment environment and whether compile-time assistance is useful.

For code that needs a fully independent copy of nested data, write a type-specific deep-copy routine. For a one-time transfer to a worksheet or another data structure, it may be simpler to iterate through the keys and write the needed values directly rather than create a second dictionary. The right choice depends on whether the goal is a separate lookup table, a snapshot of values, or a recursively independent object structure.

Frequently Asked Questions

Does Scripting.Dictionary have a Clone method?
No. The standard Scripting.Dictionary does not provide a built-in Clone method. Create a new dictionary and transfer the key-item pairs, or write a helper function for the kinds of values your project uses.

Does changing the copy change the original?
Adding or removing an entry in the copied dictionary does not change the original dictionary’s entries. But when an item is an object, both dictionaries can refer to that same object, so changes to the object itself may be visible through either dictionary.

Can I copy a dictionary with Set?
Set destination = source assigns an object reference; it does not duplicate the dictionary. Use Set when you intentionally want both variables to refer to one dictionary, and a copy loop when you want a separate dictionary container.

Why copy CompareMode?
CompareMode determines how string keys are compared. If the destination keeps a different setting, key lookups and attempts to add keys can behave differently from the source. Set the destination mode before inserting keys.

Will this code work in Excel for Mac?
Not necessarily. The examples create the Windows scripting dictionary through a COM object, whose availability differs by platform and Office version. Test the target Mac installation, or use a cross-platform alternative such as a Collection or a custom VBA class.

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.