What are ramifications of object reference not being destroyed in VBA

Hi - vba newbie here...

If I create an object reference and that reference is not destroyed, what are the ramifications?  Is this an example of a "memory leak"?

For example:

Set TrapInspector = New clsInspector

If code aborts without "Set TrapInspector = Nothing", do I still have an object reference somewhere?  Is it still accessible? Is it still taking up memory?  What kinds of problems can that cause?

Or what happens if I execute the same set statement several times in a row: does that replace the reference each time, or does it create multiple references / instances?

Thanks for your help-
Who is Participating?
Bill PrewCommented:
It seems I've read quite a bit about this on and off over the years, and not sure I ever heard a definite answer.  It seems to be good practice, and I've seen it in Microsoft example code, to set an object variable to Nothing when you are done using it.  For example:

Set objFSO = CreateObject("Scripting.FileSystemObject")
. . .
Set objFSO = Nothing

Open in new window

From what I understand, VBS and VBA determine when to destruct an object variable when the reference count for that variable goes to zero.  SO setting the variable to Nothing presumably accomplishes that.

In addition, I've always imagined that when an object variable goes out of scope, it would be released.  That's a pretty big assumption on my part, but just feels like the way it would have to work.  So if you create an object reference variable in a subroutine, and that subroutine ends, I would expect the runtime to be smart enough to release it.  But I have also seen lots of code that sets them to Nothing right at the end of a subroutine, so I guess most people figure better safe than sorry and cover that base.

I'm not sure about reusing an object variable over and over again, it's an interesting question but I haven't seen a firm answer.  I would again expect that once the first object's reference count goes to zero, which should happen when I reuse that variable to reference a new instance of an object, it would be released.

Another interesting case it the With construct, like:

With New SomeClass
' . . .
End With

Open in new window

In this case there isn't even a variable involved, but the object is instantiated.  So I would expect the runtime would have to be smart enough to release the object when the loop is exited, and the object goes out of scope.

Some interesting related reading can be found at:




I am on .NET side so I may be wrong

>what happens if I execute the same set statement several times in a row

It would create a new object everytime but your variable will only hold reference to the latest one created. In .NET, other objects would be garbage collected but not sure about VBA.

>If code aborts without "Set TrapInspector = Nothing", do I still have an object reference somewhere?

I would say yes unless the overall code is dereferenced.
SAbboushiAuthor Commented:

Can anyone else fill in the rest?
SAbboushiAuthor Commented:
Thanks - much appreciated.

Anyone else have anything to add?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.