How to destroy Scripting Dictionary?

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.

dict_obj.RemoveAll

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.

Unexpected result

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.

Golden statement

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!

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *