Menu
I've noticed that some members of the Stack Overflow community will use Set Object = Nothing in closing procedures. I was able to find why this is useful for instances of Access, but no answer has been satisfying when it comes to doing this for Excel, so my question is
What are the benefits of setting objects to Nothing in VBA?
In the sample code below, is setting my objects ws and Test equal to Nothing a waste of space? Else, if doing so is in fact good practice, why?
Dim ws as Worksheet
Dim Test as Range
Set ws = Sheets(“Sheet1”)
Set Test = ws.Range(“A1”)
'Utilize Test variable (copy, paste, etc)
Set Test = Nothing
Set ws = Nothing
Exit Sub
880
An object represents an element of an application, such as a worksheet, a cell, a chart, a form, or a report.
Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created. More than one object variable can refer to the same object.
Assign an object variable to an objectUse the Set statement to assign an object to an object variable. You can assign an object expression or Nothing. For example, the following object variable assignments are valid. Set MyObject = YourObject ' Assign object reference.
To free an object variable so that it no longer points to anything, assign the "Nothing" keyword. (eg Set rgeRange = Nothing). It is good programming practice to free object variables when they are no longer needed, since this can save resources.
If this was managed .NET code (which is garbage-collected), you'd have to Release every single COM object you ever accessed, lest the host process (EXCEL.EXE) would likely remain running in the background, consuming memory and unable to completely tear down.
But this is VBA code (which is reference-counted), moreover VBA code that uses objects that the host application controls - these objects will die when the host application shuts down, and when that happens the VBA execution context is long gone.
In other words, all these Set ... = Nothing instructions are completely redundant.
In some specific cases, when you're dealing with a 3rd-party API / type library, it's possible that objects don't entirely clean up. For example you might be creating an Access.Application instance, and find that a "ghost" ACCESS.EXE process remains open in Task Manager well after Excel exited: that's a sign that you're leaking an object reference somehow, somewhere, and Set ... = Nothing can help prevent that.
However I wouldn't recommend systematically nulling all object references like that. Only when not doing it causes a problem. And even then, it's going to be one or two objects dragging everything down, not all of them. If ACCESS.EXE shuts down properly, there's no reason to clutter up your code with such instructions.
Avoiding storing object references in global state helps, too. If everything is local, in theory all objects involved are destroyed as soon as the local scope exits.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
