Memory Management in VBA: 3 Keys to Avoiding Memory Leaks

Managing memory is an important part of any program.

Luckily for us, managing memory in VBA is something we can do literally without trying.  The language itself takes care of most of the low-level details.  If you follow three simple guidelines, you can avoid the major sources of VBA memory leaks.

Let's demystify memory management in VBA.

What is Memory Management?

Memory management is the process of reserving computer memory for your program's use and then releasing it when it is no longer needed.

Notice from this definition that there are two key requirements:

• Memory in use is reserved (and thus protected from other programs)
• Memory no longer needed is released (and thus available to other programs)

Memory Management Example

Let's review a simple memory management example:

Sub Test()
    Dim x As Integer
    x = 42
    Debug.Print x
End Sub

Here's what actually happens when the above code runs.

Dim x As Integer

First, the line Dim x As Integer tells the program to reserve an area in memory to hold the value of x.  The VBA Integer data type is a 16-bit signed whole number.  Thus, the program will reserve 16 bits of memory to hold the value of x.  The default value of an Integer is 0, so all 16 bits are set to 0.  

(Note: the line Dim x As Integer is not executable [e.g., you can't step into it while debugging].  This leads me to believe that the 16 bits of memory may not get reserved until the x = 42 line is executed.  I don't know for sure.)

The program asks the operating system for 16 available bits of memory.  The operating system checks for a location to store the 16 bits of data.  The OS marks that area as reserved for the program's use and returns a pointer to the location in memory.  

The size of the pointer is based on the bitness of the executing program.  The 32-bit version of Office uses a 32-bit memory pointer, while 64-bit Office uses a 64-bit memory pointer.  (Incidentally, this is why VBA7 introduced the LongPtr type as part of its suite of 64-bit features.)

To make things easier to visualize, I will use Excel.  Instead of 32 or 64-bit memory addresses, we'll just use A1-style Excel cell references.  As you see below, the OS has set aside 16 bits for our program to hold the value of x beginning at cell C4.

x = 42

Next, our program assigns the value 42 to x.

The program maintains an internal lookup of variable locations.  It sees that the value of the local variable x is being stored in memory at C4.  The program overwrites the contents of the bits in C4 with the binary representation of 42 (101010):

Debug.Print x

In the final step, our program loads the value of x and outputs it to the immediate window.

To do this, it looks up the location of x (C4) and grabs the 16 bits of data that are stored in that section of memory.  

End Sub

When we exit the Test() routine, our local variable x goes "out of scope."

In other words, the memory we set aside for x is no longer needed.  At this point, the program releases its hold on the memory.  It tells the OS it no longer needs it.  The OS marks it as available memory to serve up to other programs.  The actual bits remain the same (for now) as there is no benefit to changing them.  They will get set to their new value by the next program that reserves them.

Memory In Use Not Properly Reserved

What happens if the memory we are using is not properly reserved?

Imagine that between line x = 42 and Debug.Print x, the OS allowed some or all of the bits between C4:R4 to be overwritten:

Now, when Debug.Print x runs, the value of x will be output as 13 (1101) instead of 42.

This is bad when storing and retrieving values, but it can be catastrophic when retrieving executable code.  In fact, most of the time when programs try to fetch executable code from unreserved memory locations, the result is a hard crash of the application.  (Years ago, it tended to hard crash the entire computer.  Operating systems are more resilient these days, though.)

Accessing "unsafe memory" results in all kinds of problems, which is why high-level programming languages like VBA make it extremely difficult to make this kind of memory management mistake.

Memory No Longer Used Not Released

Screwing up the second memory management requirement is more commonly referred to as a "memory leak."

If your program reserves memory from the OS but fails to release it when it's no longer used, then your program will use more memory than it needs.  Depending on the severity of the leak:

• Your computer performance could suffer
• Your program performance could suffer
• You could receive an "Out of resources" error
• You could receive an "Out of memory" error

What does a memory leak look like?

Let's continue with our example from above.  Imagine that we call our Test routine four times:

Test
Test
Test
Test

In this scenario, though, the 16 bits of memory reserved to hold the value of x do not get cleaned up when x goes out of scope.  After four calls to the function, here's what our memory looks like:

(This is a contrived example. This kind of memory leak is not even possible in VBA, but it illustrates the concept.)

Memory Management in VBA

Memory management is something you may not have ever thought about in VBA.

Low level programming languages, such as C++, require that you manage memory manually.  One of the great benefits of higher level languages is that they mostly remove the memory management burden from the developer.

Unless you are using certain Windows APIs in VBA (such as the memory management functions), you don't need to worry about the first memory management requirement whatsoever.  VBA makes it nearly impossible to read from unsafe memory.

It's All About the Objects

I used an Integer value type in my examples above to simplify the memory management explanation.  

The truth is you don't have to worry about value types at all when it comes to managing memory (other than to limit their scope).  Instead, your focus should be on managing the lifetime of objects.  And even then, this is relatively easy.

While many sites recommend explicitly setting objects to Nothing, this is generally unnecessary.  Matthew Curland explains in Advanced Visual Basic 6 (p. 109-110)(emphasis mine):

If you keep the rules for object-model teardown as simple as possible for the client (meaning releasing all references in a random order is sufficient), the consumers of the object always know how to use the object model.  Microsoft has botched these simple guidelines several times over the years.

... Data objects, such as DAO, provide [an] example of poor teardown behavior.  DAO has Close methods that must often be called in the correct order, and the objects must be released in the correct order as well (Recordset before Database, for example).  This single poor object model behavior has led to the misconception that VB leaks memory unless you explicitly set all the local variables to Nothing at the end of a function.  This is a completely false notion in a well-designed object model.  VB can clear the variables faster at the End Sub line than you can from code, and it checks the variables even if you explicitly release your references.  Any effort you make is duplicated.

Unfortunately, Matthew does not elaborate on the "correct order" to call DAO Close methods and relase DAO objects, aside from his one example, "Recordset before Database."  I would love to see an in-depth look at that topic.  Please drop a note in the comments if you are aware of such a resource.

Really, when it comes to managing objects in memory, the key is to avoid designing objects that hold references to each other resulting in a "circular reference."  I'll explore that topic in more depth in a different article.

3 Keys to Avoiding Memory Leaks in VBA

With all of that context out of the way, here are the three keys to avoiding memory leaks in VBA:

• 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)

Image by Gerd Altmann from Pixabay