10

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?

Steve06
  • 741
  • 2
  • 15
  • 32
  • 1
    Guys, when processing a large dataset I receive an overflow runtime error 6 from time to time (generally after 1 hour or 2). The error goes away when I restart my macro from where it stopped. Could an overflow error be related to some issue of having created too many objects that are not destroyed properly after use? – Steve06 Apr 18 '11 at 05:23
  • Is there the possibility of 'circular references', (e.g. ObjectA references ObjectB references ObjectC references ObjectA)? In this case, even if that 'trio' is removed from the rest of the object tree, each object still has 1 reference pointing to it & this can cause issues... – Simon Cowen Apr 18 '11 at 10:26
  • @Simon, thanks, this is a very good point but I'm pretty sure this is not the case here. I posted this question seperately here http://stackoverflow.com/questions/5706170/excel-vba-overflow-error-from-too-many-undestroyed-objects and also supplied some code. – Steve06 Apr 18 '11 at 18:58

2 Answers2

11

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).
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • 1
    +1 Though you might want to add a "Check3" after the End Sub. At which point `clsMyClass` goes out of scope and `MyClass3` drops down to zero references and is then cleaned up. – mwolfe02 Apr 17 '11 at 01:07
  • Nice catch about the last instance of MyClass, assuming the missing example code was some kind of loop doing a `set x = new MyClass: MyCollection.add x`. – jtolle Apr 17 '11 at 01:18
  • ok, and MyCollection.Add clsMyClass only adds a POINTER to the collection, and not a copy of the class object, right? – Steve06 Apr 17 '11 at 01:34
  • @Steve, no doubt there is a pointer involved behind the scenes. But with garbage collected systems it's probably more helpful to think in terms of *references*, which are a little more abstract. VBA in particular uses a *deterministic* garbage collecting scheme involving *reference counting*. But yes, a VBA reference is a lot like a pointer. You can have multiple different variables (or elements of a `Collection`) that refer to the same actual object instance. – jtolle Apr 17 '11 at 03:30
2

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

jtolle
  • 7,023
  • 2
  • 28
  • 50
  • +1 One minor quibble: the last individual instance of MyClass is not destroyed until after exiting the procedure and MyClass goes out of scope. Dick Kusleika points this out in his answer. – mwolfe02 Apr 17 '11 at 01:09
  • Actually, I guess the original code doesn't say how the MyClass instances were created. I'm going to edit my answer with a supposed creation method. – jtolle Apr 17 '11 at 01:13