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

Say I have a collection开发者_高级运维 MyCollection of objects of MyClass.

Does Set MyCollection = Nothing call each contained object's destructor or should I take care of setting each object = Nothing individually?

I obviously care for reasons of memory leakage.

Dim MyCollection As Collection
Set MyCollection = New Collection
... 'add objects of type MyClass here
Set MyCollection = Nothing 

Does destroying this class call every single object's destructor?

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
    'Check2

End 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 Collection
Set MyCollection = New Collection

Call 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