1

I have in one module a sub that calls a function in another module and that function is publicly defined, after calling the function i store its returned value into a variable.

my question is : does the function retain its value in memory after being called by that sub? if so i want to clean it from the memory based on its type (example: set myFunct = nothing) if it returns an object.

enter image description here

Ali_R4v3n
  • 377
  • 5
  • 15
  • as for my knowledge the function in itself isn't a variable, it just supplies a value or a reference to a variable and then it "dies" just after. you can check it in Locals window – user3598756 Mar 20 '16 at 20:31
  • 1
    The scope of VBA variables is well documented on this site: https://support.microsoft.com/en-us/kb/141693 – Ralph Mar 20 '16 at 20:32

2 Answers2

3

VBA implements Reference counting to clean the memory using a "Garbage Collector"

When you call a function, it doesn't matter whether this function is private or public. What matters is, if inside the function you use a global or a local variable for your object. In your case it looks like it's local.

Your local variable is thus only referenced in your function, so its reference counter = 1.

At the moment your variable becomes out-of-scope, ie when the function ends and returns the value, the reference counter of the object is decremented and becomes 0. Although the object is still physically present in memory, it is not addressable anymore, becomes useless, and is therefore candidate for the garbage collector.

When you code Set theobject_inside_function = nothing you are just explicitly decrementing the reference counter. So it is useless to do it inside your function because VBA will do it for you once the function will end.

You can also read this article, it's old but still breaking a lot of myths regarding variable cleaning in VB

Thomas G
  • 9,886
  • 7
  • 28
  • 41
  • Thanks for the explanation, i have now a better picture about the Garbage collection. this also reminds me to clean my Garbage Too :) – Ali_R4v3n Mar 21 '16 at 04:46
0

from what i noticed as long as there is a variable linked to the function (meaning the value of that variable comes from the function output ) the function stays alive ... i tested that. please leave any comments that help us learn more

Ali_R4v3n
  • 377
  • 5
  • 15
  • well...never studied computer science on university but for my opinion the function never exist on RAM (like variables does - pointed to "stack memory" if ive translated it right) functions executed in CPU on running time and when the function finished running it does not exist anymore (in CPU) and you can see it on task manager when running "heavy" function cores work hard, when they finish cpu decreasing dramatically. interesting question to me but probabley for somone who learned computer science in a dissent university it is a basic question... – Jonathan Applebaum Mar 20 '16 at 21:16