Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Do I need to release objects created inside functions eg
Public Function Fred(..) As ...
'Create copy of WS
Dim WS As Worksheet
Set WS = Worksheets("Network")
{do stuff here}
End Function
Do I need to release WS on exit eg add Set WS = Nothing - I seem to find conflicting advice on t'interweb.
I'd have thought as it was defined local to the function it will be garbage collected on exit....
I've found in the past that if you're dealing with new application objects (excel etc) then it's better to fully release as they can persist, same goes for creating completely new objects that you don't want hanging about (temporary forms etc). In your example I wouldn't have thought it was necessary as you aren't creating new objects just aliasing existing ones.
ok, so If I use:
Dim fred as cType
Set fred = New cType
then I have to Set fred = Nothing before exiting the function?
I think you'll be ok, 'cos it's local. But I tend to clear them anyway, just to be sure.
Just run some tests, creating millions of new objects by calling the same function in a loop and Excel's memory allocation seems unchanged. Not sure there is anyway to see the actual Stack or Heap size for VBA itself?