Cleaning house with the VBA.Collection

Posted on 2007-08-10
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

    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.
    LVL 3

    Expert Comment

    Set it to Nothing
    and then Dim it as new before next use
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    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…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now