In my article on Memory Management in VBA, I listed three keys for avoiding memory leaks:
- Minimize the use of global variables (they never go out of scope)
- Minimize the use of nested subforms/subreports (they chew through memory)
- Avoid circular object references (most often found in objects with parent-child relationships)
Let's discuss circular references a bit.
As a brief refresher, VBA uses reference counting to dispose of objects in memory.
Objects remain in memory so long as there as at least one object variable pointing to it. The objects themselves keep track of this count via the IUnknown interface. As a VBA developer, you don't need to worry about the technical details of that process.
All you need to know is that when you create a reference to an object via
Set ... = or
As New, the reference count goes up by one. When the object variable goes out of scope or you explicitly
Set ... = Nothing, the reference count goes down by one. When the reference count reaches zero, the object is destroyed, and the memory is freed.
Read Managing Memory in COM for more details about reference counting.
What is a Circular Reference?
A circular reference is when two objects hold references to each other.
The problem is that the system can never reclaim the memory held by these two objects. Ted can't be destroyed as long as Phil holds a reference to him. Phil can't be destroyed as long as Ted holds a reference to him.
Both objects remain in memory even after they are no longer accessible from the program.
Demonstrating the Problem
We will create a simple class module named
oEmployee with a single public field, Manager, that points to an instance of the
Having a class module that includes references to itself is more common than you might think. Any time you have a parent-child hierarchical relationship, this sort of approach generally makes sense.
Here's a full, working version of the
Public Manager As oEmployee
The Manager field is a reference to another instance of the same class.
To make it easier to test our scenarios and see which ones cause memory leaks, we will use the VMMap (Virtual Memory Map) utility from ProcMon creator Mark Russinovich. This tool shows a breakdown of reserved memory by process.
To make each of our objects easier to see inside the utility, we will create a 65 MB string in the class's Initialize event. Inside VMMap, our object models will appear highlighted in red with Type "Heap (Private Data)" and size ~65,000 KB.
Here's the full class module code that we will use for testing:
'--== Class oEmployee ==-- Public Manager As oEmployee 'Make this object easier to see in VMMap: Private mVMMapBeacon As String Private Sub Class_Initialize() 'Occupy 65 MB of virtual memory mVMMapBeacon = Space(2 ^ 25) End Sub
Let's use this class to model a few sample organizational charts.
A Simple Organization
'Veronica -> Ted Sub BuildSimpleOrg() 'NO leak Dim Veronica As New oEmployee Dim Ted As New oEmployee Set Ted.Manager = Veronica End Sub
The total reference count for this system is 3. Veronica and Ted each have a single so-called "external" reference. "External" references in this context are those references made to the objects from outside the object's class module.
In the sample code above:
Veronicais the External Reference to the Veronica object
Tedis the External Reference to the Ted object
Ted.Manageris the internal reference from Ted to Veronica
In the image below (and the similar ones that follow), the graphic on the right is a visual representation of the object relationships represented in the code shown on the left.
If you step through this code up through the End Sub line, VMMap will display two "Heap (Private Data)" entries of 65,000 KB (left, below). If you step out of the module, the memory is reclaimed and the "Heap (Private Data)" entries are removed from VMMap (following a refresh of the VMMap window with F5).
Behind the scenes, the two "External References" get removed first when the Ted and Veronica variables go out of scope following the End Sub line. At that point, the Ted object has a reference count of zero and and Veronica has a reference count of one. The Ted object gets destroyed. With the destruction of the Ted object, the Veronica object now has a reference count of zero. Veronica gets destroyed.
There are no memory leaks in this simple implementation.
Multiple Levels of Organization
'Veronica -> Ted -> Phil Sub BuildMultiLevelOrg() 'NO leak Dim Veronica As New oEmployee Dim Ted As New oEmployee Set Ted.Manager = Veronica Dim Phil As New oEmployee Set Phil.Manager = Ted End Sub
A Simple Circular Reference
' Ted <-> Phil Sub BuildCircularRef() 'LEAKY Dim Ted As New oEmployee Dim Phil As New oEmployee Set Ted.Manager = Phil Set Phil.Manager = Ted End Sub
When we step out of the code above, the two orange "External Reference" pointers go away. However, Ted and Phil each have reference counts of one, owing to the fact they are pointing to each other.
This deadlock situation is visible in the VMMap utility. As a reminder, the screenshot on the left represents virtual memory at the time when the code is waiting at the End Sub line. The one on the right shows what happens after we press F8 to step out of the code and the object variables go out of scope:
Before moving on to the next test, I clicked on the blue square "Reset" button ([Alt] + [R], [R]) to perform a global code reset to reclaim the reserved memory.
A Multi-Level Circular Reference
' Veronica -> Ted -> Phil -> Veronica Sub BuildMultiLevelCircularRef() 'LEAKY Dim Veronica As New oEmployee Dim Ted As New oEmployee Dim Phil As New oEmployee Set Ted.Manager = Veronica Set Phil.Manager = Ted Set Veronica.Manager = Phil End Sub
One point I alluded to earlier is that there is no way to get to these leaked objects from within the application once all the variables go out of scope. Here's what the objects look like conceptually:
Notice that the orange "External Reference" links are all gone. Those links may have been external to the class module, but they were internal to our application. Now that they are gone, our application is completely cut off from these objects.
However, because they still reference each other, they cannot self-destruct. And because they cannot self-destruct, they continue to consume memory that was set aside for our application.
A Self-Referencing Circular Reference
' -> Veronica <- Sub BuildSelfRef() 'LEAKY Dim Veronica As New oEmployee Set Veronica.Manager = Veronica End Sub
When a class instance references itself internally, it is another form of circular reference. This, too, causes a memory leak:
UPDATE [2022-04-01]: Changed "property" to "field" in describing the
Manager field of the
oEmployee class. (h/t IvenBach)