Excel VBA中Global/Public变量内存释放与清除的方法及区别问询

First, let’s anchor this in VBA (since your references to Global/Public variables and End Statement align with that environment)—this topic hinges heavily on how the runtime handles variable scope and lifecycle.

Your core question boils down to: Can we manually release the memory allocated for a Global/Public variable before the program terminates? Let’s break down your sub-questions one by one:

a) Does the End Statement release Global/Public variable memory? #

The End statement does more than just "clear" variables—it immediately terminates the entire VBA runtime environment. When this happens, the operating system reclaims all memory associated with the program, including the space allocated for Global/Public variables.

That said, End is a blunt tool: it skips all cleanup code (like object Terminate events or finalization routines) and exits abruptly. So while it does free the memory, it’s not a targeted way to release just one variable’s space—it ends the entire program’s execution.

b) Setting variables to Nothing or Empty—does this release memory? #

This depends entirely on the variable type:

• Object types: Set myGlobalObj = Nothing breaks the reference between the Global variable and the object it points to. Once no remaining references exist for the object, VBA’s garbage collector will free the memory used by the object itself. However, the Global variable’s own memory (the small space that stores the object reference) stays allocated—it just now points to nothing.
• Value types (e.g., integers, strings, dates): Setting a variable to Empty (or its default value, like 0 for numbers) only resets the variable’s content. The memory space reserved for the Global variable itself remains occupied for the full runtime of the program.

In short: these operations reset the variable’s value, but don’t release the memory allocated for the Global variable’s container itself.

c) Can "deleting" or "destroying" a Global variable release its memory? #

In VBA, you can’t actually "delete" or "destroy" a declared Global/Public variable. Once you declare a variable at the module level with Public or Global, the runtime reserves its memory space for the entire lifecycle of the program or project.

Any "destruction" referenced in forums is just shorthand for resetting the variable (like setting objects to Nothing or values to defaults). The variable’s own memory footprint stays intact until the program terminates (e.g., closing the host application like Excel, or using the End statement).

Key Takeaway #

The only way to fully release the memory allocated for a Global/Public variable is to let the program terminate naturally (closing the host app) or use the End statement to force termination. All other operations only reset the variable’s content, not the underlying memory reserved for the variable itself.

内容的提问来源于stack exchange，提问作者SweDentan