I love dictionaries! This is amazing object to create magical list of anything and to find any in just a second! But after they are not in use anymore, how to set them to nothing? How to destroy Scripting Dictionary?
Straight to the topic
Or almost straight. I had a situation, where I created Scripting Dictionary with over 100k objects – classes to be precise. I thought to myself, that after it is no longer needed it is worth to get rid of it to empty the RAM memory. It was heavy report itself, so why to overload that even more with unnecessary object.
So I tried the most basic thing to empty the object – set to nothing.
Set dict_obj = Nothing
Unfortunately it took like 5 minutes to go throught that line. So it was like 4 times longer than whole code module. I was not satisfied at all.
Then I found out the dictionary function to clear it.
The results were almost the same as setting to nothing method. At this point I was surprised why built-in function is taking so long.
I also was so desparate that I tried something like this – remove each element one by one.
For i = 1 To dict_obj.Count Let keyString = dict_obj.keys()(i) dict_obj.Remove keyString Next
The result was even worse. I don’t even know what I was expecting to get knowing what was the result of RemoveAll function.
I was helpless and didn’t really know what to do. I wanted to stop digging into this topic and just forget about it hoping that there will be enough RAM memory to handle this report.
But there was 1 more chance to actually deal with this problem. As this report code relied a lot on dictionaries I thought to myself to use the same variable for another dictionary. So instead of trying to clear the dictionary I created a new one in the place of the old one.
Set dict_obj = CreateObject("Scripting.Dictionary")
And this was all I wanted. In just a second the old dictionary got cleaned. I’ve never thought , that the easiest way to destroy scripting dictionary is to create a new one in the place of old one.
To be sure I set this new one to nothing to be sure, that it is really cleared.
Set dict_obj = CreateObject("Scripting.Dictionary") Set dict_obj = Nothing
And yes! It was empty and I did not have to wait entire life to clear this object.
In the end it comes to that sometimes to get rid of something it is faster to build something new. It sounds weird, but that really helped me not only to destroy scripting dictionary, but also in other cases in VBA. Maybe one day You’ll discover that too!
2 thoughts on “How to destroy Scripting Dictionary?”
Am afraid your solution does not seem to work with Excel in Office 365 (Web Version)
the following statements
Set the_dict = CreateObject(“Scripting Dictionary”)
Set the_dict = Nothing
generate an error which is:
Run Time Error ‘429’
ActiveX component can’t create object
Would apprecitae your help in resolving the issue
Don’t forget about dot between Scripting and Dictionary 🙂