When I first started playing around with VBA, everything I read emphasized how important it was that when you initialized an object, you remembered to set it to nothing afterwards in order to ‘release the memory’.
I did that for a while, but soon realized that this was not exactly correct.
As in many platforms, VBA has a mysterious garbage collector whose function is to return memory to the pool when it is no longer being referenced. The key here is ‘referenced’. When you create a reference like
set b = new cBidule
then the new instance of a cBidule class becomes referenced by the variable b. It follows then if you remove the reference to it by
set b = Nothing
then that reference is removed and it then qualifies for the garbage collector’s attention, but how to test?
VBA provides a special terminate and initialize function. When specified in your class, this will be called when it is created or terminated. If removing all references to an object destroys it, then we should see the class terminate being executed.
Here is my class code
Option Explicit Private Sub class_initialize() Debug.Print "initializing.." End Sub Private Sub Class_Terminate() Debug.Print "terminating" End Sub
Here is a test
Private Sub testLeakMain() Dim bMain As cBidule Debug.Print "starting.." Set bMain = New cBidule Set bMain = Nothing Debug.Print "done.." End Sub
And here is the result
starting..
initializing..
terminating
done..
Private Sub testLeakMain() Dim bMain As cBidule, bRef As cBidule Debug.Print "starting.." Set bMain = New cBidule Set bRef = bMain Set bMain = Nothing Debug.Print "done.." End Sub
Here is the result
starting..
initializing..
done..
terminating
Dim bMain As cBidule, bRef As cBidule Private Sub testLeakMain() Debug.Print "starting.." Set bMain = New cBidule Set bRef = bMain Set bMain = Nothing Debug.Print "done.." End Sub
and the result shows that the object is never terminated, since the bRef object is public
starting..
initializing..
done..
Now if we make the bRef variable private, but leave the bMain public, since we set bMain to nothing and bRef will go out of scope automatically, we should see a class termination.
Dim bMain As cBidule Private Sub testLeakMain() Dim bRef As cBidule Debug.Print "starting.." Set bMain = New cBidule Set bRef = bMain Set bMain = Nothing Debug.Print "done.." End Sub
and that’s exactly what happens
starting..
initializing..
done..
terminating
Option Explicit Private pChildren As Collection Private Sub class_initialize() Set pChildren = New Collection Debug.Print "initializing bidule" End Sub Private Sub Class_Terminate() Debug.Print "terminating bidule" End Sub Public Property Get children() As Collection Set children = pChildren End Property
and the cWidget Class
Option Explicit Private pParent As cBidule Private Sub class_initialize() Debug.Print "initializing widget" End Sub Private Sub Class_Terminate() Debug.Print "terminating widget" End Sub Public Property Set parent(p As cBidule) Set pParent = p End Property
Private Sub testLeakMain() Dim bBid As cBidule, bWid As cWidget Debug.Print "starting.." Set bBid = New cBidule Set bWid = New cWidget bBid.children.Add bWid Debug.Print "done.." End Sub
and it works perfectly, terminating both instances as they go out of scope
starting..
initializing bidule
initializing widget
done..
terminating bidule
terminating widget
But, let’s say we also create a back link to the parent bidule in each widget. This would be a pretty common thing to do.
Private Sub testLeakMain() Dim bBid As cBidule, bWid As cWidget Debug.Print "starting.." Set bBid = New cBidule Set bWid = New cWidget Set bWid.parent = bBid bBid.children.Add bWid Debug.Print "done.." End Sub
what..?? they don’t terminate on going out of scope
starting..
initializing bidule
initializing widget
done..
Private Sub testLeakMain() Dim bBid As cBidule, bWid As cWidget Debug.Print "starting.." Set bBid = New cBidule Set bWid = New cWidget Set bWid.parent = bBid bBid.children.Add bWid Set bWid = Nothing Set bBid = Nothing Debug.Print "done.." End Sub
starting..
initializing bidule
initializing widget
done..
Private pChildren As Collection Private Sub class_initialize() Set pChildren = New Collection Debug.Print "initializing bidule" End Sub Private Sub Class_Terminate() Debug.Print "terminating bidule" End Sub Public Property Get children() As Collection Set children = pChildren End Property Public Sub tearDown() Dim w As cWidget For Each w In children w.tearDown Next w End Sub
the updated widget class
Private pParent As cBidule Private Sub class_initialize() Debug.Print "initializing widget" End Sub Private Sub Class_Terminate() Debug.Print "terminating widget" End Sub Public Property Set parent(p As cBidule) Set pParent = p End Property Public Sub tearDown() Set pParent = Nothing End Sub
And the test
Private Sub testLeakMain() Dim bBid As cBidule, bWid As cWidget Debug.Print "starting.." Set bBid = New cBidule Set bWid = New cWidget Set bWid.parent = bBid bBid.children.Add bWid bBid.tearDown Debug.Print "done.." End Sub
now successfully releases the memory
starting..
initializing bidule
initializing widget
done..
terminating bidule
terminating widget
For help and more information join our forum,follow the blog or follow me on twitter .
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines