Is a VBA object destroyed when the variable is set

Question

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?

Answer 1

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.