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.
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
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
