Memory leak in Excel

Question

I have a macro in excel which calls a few other functions in vba. The problem is that there seem to be a lot of circular references and memory is not freed up. I am trying to explicitly free up memory by setting objects to nothing, but still cannot plug it. Morever, even after the macro stops running, the memory is not freed up. I have to close the instance of excel to reclaim memory. How can i get rid of this leak?

Answer 1

If you have defined any variables or arrays or collections or any other object at the top of the code module (ie not inside a sub or function), then they will retain their values when the macro stops running, unless you explicitly clear them.

Only the variables declared within a sub or function are cleared automatically, on exiting the sub or function.