Cleaning house with the VBA.Collection

Posted on 2007-08-10
Medium Priority
Last Modified: 2010-08-05
I have a VBA program in Excel that makes fairly extensive use of the VBA.Collection object.  The I have a function that returns a collection object, and whenever I want to build a new one, I simply set the collection variable to the collection returned by the function, using a new set of parameters in the function.  

When I point the collection object to a new collection, what happens to the memory that was allocated for the old one?  
Should I be removing all the items from the collection before building a new one?  
If the collection members are themselves large objects, do I need to set the members to Nothing before removing them?
If the collection does not clean up after itself, am I correct in assuming that new build competes for a diminishing memory pool?  
Question by:PeterFrb
LVL 38

Accepted Solution

PaulHews earned 2000 total points
ID: 19673035
Setting the collection object to a new collection should free the memory from the old collection.  To make sure, set the collection variable = Nothing before you reassign it.

There's no need to loop through the collection and set all the inner objects = Nothing.  As long as the collection is the only reference to those objects, they will be destroyed.  (This I can confirm, because I've tested it by calling a debug.print from the objects Terminate event.)

The only real problem you will have is if you have a circular reference somewhere in your object structures.  If object A points to object B which contains a reference back to object A, then it can be difficult to free the memory associated.

Expert Comment

ID: 19673057
Set it to Nothing
and then Dim it as new before next use
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 19673464
It really depends on what you are doing!

If you are using MANAGED objects then there is no need to loop thru and set each item to Nothing.

If you are using unmanaged objects, such as those often used in Win API GDI drawings, then you would need to loop thru and manually release those objects.

If you are using only the built-in methods of the VB language then most likely you are using only managed objects and don't need to worry about it...

Author Comment

ID: 19674210
Good answers, all, thank you.  

Actually, I'm using a multi-tiered collections structure to store multiple fields from a query I return.  I leverage the use of a collection's ability to reference by both key and index.  At the end of the collection chain is always a string or numeric value.  I decided to write this in response to the relatively slow data retrieval methods of a find.  The following two syntaxes produce the same result:

            MyRcd.Find "Alpha = 'Apple' and Beta = 'Bread' and Gamma = 'Gold'"
            iResult = MyRcd("ReturnField")
            iResult = MyCollection("Apple")("Bread")("Gold")

The maximum number of items a multi-tiered collection can hold is the total possible number of items in the collection raised to the number of tiers.  If the maximum number is 256, a three-tiered collection can hold a maximum of over sixteen-million values.  And every one of those values is indexed on the key, making the retrieval time very fast, especially when compared to performing a Find on a theoretical recordset of sixteen million records.

The function call builds the multi-tiered collection set, with a SQL statement and an array of field names as parameters.  The last leaf on any given tree is always a field value: a number of string.  So if I change the query and set the collection to a new multi-tiered collection set, it sounds from your feedback that changing the collection pointer will free up the portion of the memory pool previously taken up by that structure.  Right?
LVL 38

Expert Comment

ID: 19674297
The hierarchical nature won't change the result.  Because the tree is well defined with one way references, setting the root to a new collection will release all the memory.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question