VBA Dictionary: How to destroy a Dictionary


VBA Dictionary Destroy

It is good practice to destroy a dictionary that is no longer needed.

By Set D = Nothing the object reference that is assigned to the variable is released.

Image showing situation before set to nothing
Image showing situation after set to nothing

Some prefer this.

After Set D = Nothing the variable of the type dictionary still exists. It can still be used. You can assign a new object reference to it.

Image showing situation after set to nothing
Image showing situation after set to nothing
Image showing situation when object that is nothing is set again

VBA will forgive you when you do not destroy your dictionaries. When the variable goes out of scope, the object reference is released automatically by VBA. The line Set D = Nothing is just an explicit way of doing that yourself.

The VBA editor will not warn you if you are working with an dictionary that has been destroyed. The code compiles. Intellisense still works.

Image showing VBA error object not set

Be careful when the dictionary has been created by the Dim ... As New ... statement. An object reference will be created automatically if you use the variable again after destroying it.

The line If (D Is Nothing) Then assigns a new object reference.

We find this code behavior a bit strange and hard to remember. For this reason, we never create dictionaries using the Dim ... As New ... statement.

The method RemoveAll does not destroy the object. It removes all key/item pairs, so that method Count will return 0.

Image showing situation before remove all
Image showing situation after remove all