Excel VBA: Does destroying a collection of objects destroy every single object?

All the MyClass objects will be destroyed when you destroy MyCollection, unless they are referenced somewhere else.

VBA uses a reference counter on the class. It ticks up one every time there's a reference to the class and ticks down one every time a reference is destroyed. As long as MyCollection is something and is in scope, every MyClass reference counter contained therein will be at least one. If the reference counter is exactly one, destroying MyCollection will tick every element's reference counter down to zero and it will be garbage collected.

The last MyClass variable in the middle of your sub will reference one instance of MyClass unless you explicitly set it to Nothing. One class variable isn't likely going to cause a noticeable memory problem.

Sub MakeClassColl()    Dim MyCollection As Collection    Dim i As Long    Dim clsMyClass As MyClass    Set MyCollection = New Collection    For i = 1 To 3        Set clsMyClass = New MyClass        MyCollection.Add clsMyClass        'Check1    Next i    Set MyCollection = Nothing    'Check2End Sub

Check1:

• i=1: MyClass1 (instance 1) has a reference counter of 2. One for the variable, one for the collection
• i=2: MyClass1 has an rc of 1 (lost clsMyClass, still has collection), MyClass2 has an rc of 2
• i=3: MyClass1 still 1, MyClass2 drops to 1, MyClass3 has an rc of 2

Check2:

• Every MyClassi instance in the collection drops by one. MyClass1 and 2 go to zero. MyClass3 drops to 1 because clsMyClass still references it (I didn't destroy clsMyClass after adding it to the collection).

The short answer is yes. In the below example, which is very similar to yours except that it shows one particular way you might have created your MyClass instances, all of the individual instances of MyClass will be destroyed right after the collection is destroyed:

Dim MyCollection As CollectionSet MyCollection = New CollectionCall MyCollection.Add(New MyClass)Call MyCollection.Add(New MyClass)Call MyCollection.Add(New MyClass)Set MyCollection = Nothing

The longer answer is that it depends. The answer is "yes" if the only reference to the contained objects is the one held by the collection, which is the case in your simple example. VBA will know that all of your MyClass instances are no longer referenced anywhere and destroy them. (This will result in a call to each object instance's Class_Terminate method.)

But you have to be careful if you have made other references to those objects. There is nothing magic about the statement Set MyCollection = Nothing. It's the fact that doing that causes VBA to destroy the collection, which in turn causes it to destroy the object within. (And of course, the collection is only destroyed by that line if MyCollection contaqins the only reference to it.)

A good source to learn more about how VBA object lifetimes is the old Visual Basic 6.0 Programmer's Guide, specifically the section on "Object References and Reference Counting":

http://msdn.microsoft.com/en-us/library/aa263495(v=VS.60).aspx