[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

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

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

Children
No Data