To empty a VBA Scripting.Dictionary, call its RemoveAll method: dict.RemoveAll. This removes every key and item while leaving the dictionary object available for reuse; there is no Clear method on Scripting.Dictionary.
What clearing a dictionary means
A dictionary stores values under unique keys. For example, a macro might use employee IDs as keys and names as items, or product codes as keys and quantities as items. Clearing the dictionary means deleting all of its key-item pairs, so its Count becomes zero. It does not mean erasing worksheet cells, clearing VBA variables generally, or deleting the dictionary object itself.
The relevant object in common Excel VBA code is Scripting.Dictionary, provided by the Windows Scripting Runtime. Its method for removing every entry is named RemoveAll. After that call, you can add new entries to the same object, check its count, and use its other methods as usual. This is useful when a procedure reuses a dictionary for a new group of data without needing to create another object.
That distinction matters because VBA has several collection-like structures. A Collection is not a dictionary and does not have a RemoveAll method. The method name depends on the object type, not on the general idea of clearing a set of stored values.
The basic RemoveAll method
Here is a complete, late-bound example. It creates a dictionary, adds two entries, removes both, and prints the count before and after. The count after removal is zero.
Option Explicit
Sub ClearDictionaryExample()
Dim dict As Object
' Create a Scripting.Dictionary without setting a VBA reference.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 12
dict.Add "South", 18
Debug.Print dict.Count ' Prints 2
' Remove every key-item pair; the dictionary object remains usable.
dict.RemoveAll
Debug.Print dict.Count ' Prints 0
dict.Add "West", 9
Debug.Print dict.Count ' Prints 1
End Sub
Run a macro from the Visual Basic Editor or Excel’s Developer tab. On Windows, Alt+F11 opens the Visual Basic Editor. On Mac, Option+F11 or Fn+Option+F11 may open it depending on the keyboard settings. Before running any macro that changes workbook data, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.
RemoveAll compared with Remove and setting the object to Nothing
Choose the operation that matches what you want to happen:
dict.RemoveAlldeletes all entries but keeps the same dictionary object alive. Use it when you want to refill or reuse that dictionary.dict.Remove keydeletes one entry. Use it when the other keys and items must remain.Set dict = Nothingreleases the object reference held by that variable. It is not the method for emptying a dictionary that you intend to keep using.Set dict = CreateObject("Scripting.Dictionary")assigns a newly created dictionary to the variable. If the variable was the only reference to the old object, that old object can be released; this is replacement, not clearing.
For example, after dict.RemoveAll, the variable still points to the original dictionary and its configuration can remain in place. After Set dict = Nothing, attempting dict.Count causes an object-variable error because the variable no longer refers to an object. If you need an empty dictionary for the next step, use RemoveAll or create and assign a replacement object.
Remove one entry or a chosen group
To remove a single key without disturbing the rest, check whether it exists first. The following example avoids an error if the key is absent:
Option Explicit
Sub RemoveOneDictionaryEntry()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A100", "Notebook"
dict.Add "B200", "Pen"
' Remove only this entry when it is present.
If dict.Exists("A100") Then
dict.Remove "A100"
End If
Debug.Print dict.Count ' Prints 1
End Sub
For a chosen group of keys, call Remove for each key you intend to delete, usually after checking Exists. If the goal is to delete entries based on their items or another rule, avoid changing a dictionary while relying on its live key enumeration. A safer approach is to copy its keys to an array, then inspect that snapshot and remove matching keys. If the intended rule is simply “delete everything,” using RemoveAll is shorter and less error-prone than looping over entries.
Reusing a dictionary and its settings
RemoveAll clears the contents, not the dictionary’s identity. That makes it a natural choice inside a loop that processes multiple files, worksheets, or data groups. For each group, clear the dictionary, read the next group, and add its entries. The object does not need to be recreated on every pass unless replacing it is specifically useful to the design.
One setting worth knowing is CompareMode, which controls how the dictionary compares string keys. For example, text comparison can treat keys such as "abc" and "ABC" as equivalent, whereas binary comparison distinguishes them. Set the desired mode before adding entries. Changing CompareMode after keys have been added raises an error; after RemoveAll, you can change it because the dictionary has no entries. Clearing does not itself choose a new comparison mode, so set the mode explicitly when a later batch needs different behavior.
Early binding and late binding
The examples above use late binding: the variable is declared As Object, and CreateObject creates the dictionary at runtime. This avoids adding a VBA reference and is convenient when sharing code among Windows users with different reference configurations. The trade-off is that the editor offers less type-specific assistance, and some mistakes are detected only when the code runs.
With early binding on Windows, add the Microsoft Scripting Runtime reference in the Visual Basic Editor using Tools > References, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding provides clearer type information and editor assistance, but a missing or broken reference can cause a “user-defined type not defined” or “missing reference” problem. Regardless of binding style, the clearing call remains RemoveAll.
Common errors and how to fix them
- “Object doesn’t support this property or method” on
.Clear: For aScripting.Dictionary, replace.Clearwith.RemoveAll. Verify that the variable really refers to a dictionary and not a different object. - “Object variable or With block variable not set”: The dictionary was not created, or its variable was set to
Nothing. UseSet dict = CreateObject("Scripting.Dictionary")before calling a method. - “ActiveX component can’t create object”:
CreateObject("Scripting.Dictionary")depends on the Windows Scripting Runtime. Check the platform and environment; this approach is not a portable Mac solution. - A duplicate-key error when adding data after clearing: Confirm the clear runs before the next batch, and check whether the code adds the same key twice within that batch.
RemoveAllprevents old entries from carrying over, but it cannot prevent duplicates in newly added data. - A key comparison behaves unexpectedly: Review
CompareModeand set it before adding keys. Case sensitivity is controlled by that setting, not byRemoveAll.
When troubleshooting, inspect dict.Count immediately before and after the clear call. If it is not zero afterward, the call may not have run, the code may be referring to a different variable, or an error may have interrupted execution.
Platform and workbook considerations
Excel VBA runs in desktop Excel, not in Excel for the web; the web app does not execute VBA macros. The common Scripting.Dictionary technique shown here is intended for Excel on Windows. The Scripting Runtime is a Windows COM component, so code that creates it with CreateObject should not be assumed to work on Excel for Mac. Mac support and available automation components differ, so test the actual target environment rather than treating Windows VBA examples as cross-platform by default.
If a workbook must work on both Windows and Mac, consider whether a VBA Collection, worksheet-based lookup table, or another cross-platform design meets the need. A Collection can store items and be rebuilt, but it does not have dictionary-style keyed lookup or a built-in clear-all method. Excel for the web users can instead work with worksheet tables and formulas, or use Office Scripts where available; those are alternatives, not VBA dictionary methods.
Macro security is separate from dictionary behavior. Save macro-enabled code in a suitable workbook format such as .xlsm, and only enable macros from sources you trust. Clearing a dictionary changes only the in-memory object; it does not erase worksheet values unless the macro separately contains code that changes cells.
Frequently Asked Questions
Does a VBA dictionary have a Clear method?
No. A Scripting.Dictionary uses RemoveAll to delete every entry. Calling dict.Clear produces an error because that method is not part of the dictionary object’s interface.
Does RemoveAll delete the dictionary variable?
No. It removes the entries and leaves the object available. Its count becomes zero, and you can add new key-item pairs to it afterward. Use Set dict = Nothing when you intend to release the variable’s object reference instead.
Can I clear a VBA Collection with RemoveAll?
No. A Collection has no RemoveAll method. You can replace it with a new Collection, or remove its entries individually, taking care to remove from the end toward the beginning when using numeric indexes.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume the Windows Scripting Runtime is available on Mac. Code that depends on CreateObject("Scripting.Dictionary") is Windows-specific in ordinary VBA use; choose and verify a cross-platform alternative if Mac compatibility is required.
Does RemoveAll reset CompareMode?
No. It removes key-item pairs but does not automatically select a new key-comparison mode. Set CompareMode explicitly when needed, and do so before adding entries to the dictionary.
Related
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox Default: How to Set It and Handle CancelIn VBA, set an InputBox default by supplying the third argument: InputBox(prompt, title, default). That value appears in…
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header…
- VBA Copy Paste Formats: Methods and ExamplesIn Excel VBA, copy formatting without changing cell contents by copying a source range and pasting with Paste:=xlPasteFormats.…
- Creating Macros in Google Sheets: A Practical GuideTo create a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
- Excel VBA Find LookIn: What It Means and How to Use ItIn Excel VBA, LookIn is an argument of the Range.Find method that tells Excel what kind of content…
- Access VBA MsgBox: How to Show Messages and Get AnswersIn Access VBA, use the MsgBox function to show a message or ask a simple question. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.