[V8i-SS2 VBA] Releasing Memory

Hello,

When writing utilities to process various information, I have often been remiss in considering 'Set object = Nothing', and notice that many others also forget; where object can be something like oLevel or oModel, oElement, etc.

How important is this requirement, or is there a way to process what memory to free up when a file is being closed; does unload do this....?

Cheers, Colin

  • Objects in VB/VBA are, under the hood, smart pointers that are reference counted.

    A VBA object is allocated dynamically, with its memory address stored in the smart pointer.  Each time your code assigns a variable to an object, the smart pointer increments an internal counter.  Each time your code explicitly stops using an object, the internal counter is decremented.  When the internal counter reaches zero, the memory is deallocated.

    In complex situations, for example when an object is stored in an array or some other collection (e.g. ElementEnumerator or Dictionary), the allocated memory lifetime is hard to discern.  Using Set o = Nothing makes it absolutely clear, both to the programmer and to VBA, that you don't intend to use that object any longer.

     
    Regards, Jon Summers
    LA Solutions

    Answer Verified By: cweber 

  • Hi Colin,

    it's a good question. Frankly, I have never examined the memory allocation in VBA in detail, but what I remember from different Internet discussions, a range of answers is from "It's not required" to "Do it always" :-)

    I am sure it's nothing bad to do it, but to do not required things in the code means the code is harder to read and it's a bad habbit also. VBA takes care about releasing of memory when the variable goes out of scope automatically. So if your code is nice/clean and your methods are short, you allow VBA engine does memory management properly. But if your code is full of methods tens and hundreds lines long (I have seen many such macros), variables don't come out of scope early and memory requirement increases.

    Because VBA is a tool for macros and tiny single focus applications, not complex programs, in my opinion there are only a few situations you should take care about memory management in VBA:

    • You allocate big arrays with many objects. If such thing is really required, you should allow VBA to free memory as soon as possible. 
    • A piece of the code repeats many times.

    My conclusion is it's good to think about memory requirements, but more important topics exist in VBA macros development ;-)

    I am curious about other opinions and thoughts.

    With regards,

      Jan

    Answer Verified By: cweber