tobal99z
asked on
VBA- speed- releasing objects
Dear Experts:
I write code in VBA for Excel and Access. I object have simple objects which refer to the Active Worksheet or Active Database, that type of thing. I'm trying to optimize my code for speed.
I saw somewhere on the web that it helps at the end of each Sub to "release" each object. Do you agree? I just don't want to start doing this if it doesn't really help anything.
Example:
**
Dim AA
Set AA = ActiveSheet
...
...
Set AA = Nothing 'this it the "release"
**
What does everyone think?
Thanks!
quanttrade
I write code in VBA for Excel and Access. I object have simple objects which refer to the Active Worksheet or Active Database, that type of thing. I'm trying to optimize my code for speed.
I saw somewhere on the web that it helps at the end of each Sub to "release" each object. Do you agree? I just don't want to start doing this if it doesn't really help anything.
Example:
**
Dim AA
Set AA = ActiveSheet
...
...
Set AA = Nothing 'this it the "release"
**
What does everyone think?
Thanks!
quanttrade
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
"I think you're focussing on VBA in Excel a little to much"
I think you'll find this is because the previous question was concerning MS-Excel VBA code.
The "Optimizing Code" link is VB-biased, not Excel/VBA centred.
I also set objects to Nothing as a matter of course, whether or not the compiler/interpreter is going to recover/collect anyway, and I am still with you that it is necessary within VB to do this (but I haven't written pure VB code for at least 10 years, so can only comment on the fact that certainly up to VB 5.0, objects needed to be set to Nothing).
I'm not sure what you were trying to prove with your "MySub" sample, though.
BFN,,
fp.
I think you'll find this is because the previous question was concerning MS-Excel VBA code.
The "Optimizing Code" link is VB-biased, not Excel/VBA centred.
I also set objects to Nothing as a matter of course, whether or not the compiler/interpreter is going to recover/collect anyway, and I am still with you that it is necessary within VB to do this (but I haven't written pure VB code for at least 10 years, so can only comment on the fact that certainly up to VB 5.0, objects needed to be set to Nothing).
I'm not sure what you were trying to prove with your "MySub" sample, though.
BFN,,
fp.
ASKER
Thank you guys! was very helpful!
Thanks for closing the question.
I think you're focussing on VBA in Excel a little to much. If you have a reference to an Excel object, then you cant free it of course. I do think this is a good practise in general. I even do it in c#, even when I know there's a garbage collector who does it all for me.
As for your example in your previous post:
Sub MySub
Dim ObRef as Object
Set ObRef = MyObject
MsgBox ObRef.Name
Set ObRef = Nothing
End Sub
It *is*neccessary in vb to do this. If you don't do this your memory will never be freed. It *is not* neccessary in vb.net because the garbage collector will automatically disgard memory if it's not in use.