CODEWITHSUNDEEP
excelvbamemoryglobal-variablesallocation
SweDentan
SweDentan

Reputation: 67

Release/free vs Purge Global/Public Variables from memory - Excel VBA

I do use Global/Public Variables sparingly and only if it’s rewarding enough in many ways.

From what I can understand, the moment a variable is declared then the computer reserves a specific memory space for it for later use.

And if I got it right, a variable which is only declared to have a scope within a specific procedure, when the procedure ends, so is also the life of that variable. In other words the memory space which was allocated to that variable gets released/freed.

To my main question:

  1. Are there any ways to release/free the memory space which was allocated to a Global (Public) variable when it was declared?

Some research, reflections and sub-questions:

a) I have done some googling and seen people suggest placing an “End Statement” at a desired location within the code. Does this really release/free the memory space which was allocated to the Globally/Publicly declared variable? Or does this only “purge” or “reset” the variable to carry no assigned value or object at all (in other words the memory space which was allocated to the variable when the variable was declared is still allocated to it)?

b) I have also seen people set a Globally/Publicly declared variable equal to Nothing or Empty. But same question here. Does this really release/free the memory space which was allocated to the Globally/Publicly declared variable? Or does this only “purge” or “reset” the variable to carry no assigned value or object at all (in other words the memory space which was allocated to the variable when the variable was declared is still allocated to it)?

c) Does “Deleting” or “Killing” a Global (Public) variable release or free the memory space which was allocated to the variable when it was declared? Or does this only “purge” or “reset” the variable to carry no assigned value or object at all (in other words the memory space which was allocated to the variable when the variable was declared is still allocated to it)?

Upvotes: 1

Views: 1583

Answers (1)

GSerg
GSerg

Reputation: 78185

VB6/VBA uses deterministic approach to destroying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.

The way to destroy an object is to make sure that no object variables refer to it.

  • If an object is referenced by just one global variable, setting that variable to Nothing will both clear the variable's contents (it's no longer pointing to an object) and destroy the object, releasing its memory.
  • If the object is referenced by other variables too, setting the global variable to Nothing will only clear the contents of the variable (it's no longer pointing to an object), but the object will remain until all other references to it are set to Nothing.

An object variable itself occupies the size of a pointer (LongPtr, 4 or 8 bytes). By setting that variable to Nothing you fill these 4 or 8 bytes with zeroes, but you do not stop the variable from occupying these 4 or 8 bytes - nor can you.

Similarly, with non-object variables, setting them to anything at all only changes the content of the fixed amount of bytes that they occupy, but never deletes these bytes.

The only exception are dynamically sized arrays that you can Erase and reclaim the memory allocated for the array (but the pointer-sized array variable will still remain and will point to no array, that is, all its 4 or 8 bytes will be filled with zeroes).

To reiterate, in no case can you reclaim the space allocated for the variables themselves. That only happens when you exit the scope where they are declared - which, in case of global variables, is when the program terminates. You can only reclaim space taken by things that the variables point to.

Upvotes: 3

Related Questions