2

I haven't found an answer specific to this question so hopefully someone can clear it up for me.

I understand the VBA Garbage Collector uses a reference count to determine if an object is not longer required, and to explicitly disassociate a variable (thereby decrementing the reference count) you use:

Set objectVariable = Nothing

Here is what I have in a spreadsheet I'm working on right now:

Declare Function GetObject Lib "ObjectCreator.dll" () As Object
Public MyObject as Object

Sub MyMethod()
    Set MyObject = GetObject()
        ...do stuff with MyObject...

    Set MyObject = GetObject()
        ...do stuff with my new MyObject...

    Set MyObject = GetObject()
        ...do stuff with my even newer MyObject...

    Set MyObject = Nothing
End Sub

My question is: Do all three of the created objects get destroyed by the GC or only the last one? i.e. Does the reference count of an object get decremented when its referencing variable is set to another object rather than being set to Nothing?

zaphodalive
  • 203
  • 1
  • 7
  • 1
    If you really have a dll that has a `GetObject` method, you should alias it in the declare statement so it doesn't hide the VBA `GetObject` function. – Comintern Feb 02 '17 at 02:29
  • @Comintern: Ah perhaps I should have used a different name... no the DLL does not have a GetObject method, just a method that returns an object. I used that name only to keep the example simple. – zaphodalive Feb 02 '17 at 09:34

1 Answers1

3

When you assign an object reference to a variable, the reference count goes up by one, and when the variable loses the reference by some other assignment, the object's reference count goes down by one. For example:

Dim a As Collection
Set a = new Collection 'The reference count of this new Collection object is 1
Set b = a              'The reference count of the Collection object is now 2
Set b = new Collection 'The reference count of the original collection has gone back down to 1 because b is no longer a reference to it, and this new Collection has a reference count of 1

Set a = Nothing        'The original collection no longer has any active references, so VBA safely GCs it.
Set b = Nothing        'The newer collection now no longer has any active references either, so VBA safely GCs it.

Now, in your case you're talking about an external DLL, which may manage its own memory or running state differently internally. But the way that VBA handles COM reference counts is the same.

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
  • Thanks, that's all I needed to know. I wanted to make sure that the spreadsheet I'm working on doesn't create endless objects that aren't being dis-instantiated without explicitly breaking the variable reference with `= Nothing`. – zaphodalive Feb 01 '17 at 22:52
  • 1
    FWIW, *all* COM objects handle reference counting that way, and user objects are COM objects. IIR they extend a base class defined in a hidden 2nd typelib in VBE7.dll. VBA uses the COM reference counting interfaces and lets the COM server determine object lifespan. – Comintern Feb 02 '17 at 02:27