Nothing is a keyword in VBA. It describes the state of an object variable that doesn’t point to an object. Object variables can be set to Nothing or they can be Nothing because the code hasn’t successfully assigned an object to it..
Sub ObjectTest()
Dim oObj As Object
MsgBox oObj Is Nothing
‘Will return true. An object variable has been dimensioned,
‘but it doesn’t point to any object because the code hasn’t
‘”set” it to any object.
Set oObj = ThisWorkbook
MsgBox oObj Is Nothing
‘Will return false. oObj points to an object and therefore
‘is not nothing.
Set oObj = Nothing
MsgBox oObj Is Nothing
‘Will return true. It is possible to set an object variable
‘to nothing to release memory.
End Sub
You should set all your object variables to Nothing at the end of your procedure. Sooner than the end if you know you don’t need them anymore. VBA has its own internal clean-up procedure that releases memory allocated to object variables when code ends, but it’s not perfect. You should release object variables for any non-trivial procedure or any procedure that uses automation.
I used to think the same way about always explicitly setting my object variables to Nothing. Then I read this:
http://groups.google.com/groups?selm=ek3bQmPdAHA.1808%40tkmsftngp03
In a nutshell, VB *does* always clean up. However, there are circumstances where you need to write explicit teardown code e.g. to resolve a circular reference to an object in memory (e.g. a child object that holds a reference to its parent) or where not explicitly releasing an object causes problems (e.g. I’ve had trouble when a ‘WithEvents’ reference to an ActiveX control on a worksheet goes out of scope on workbook close, which was resolved by explicitly setting the object to nothing). If you do it every time, even when it is not needed, then it obscures the instances when you *must* do it.
Jamie: I’ve read that, it’s a good post. He makes a good case, I only have one problem: He works for Microsoft. If I owned the software then I would say it works like it’s supposed to, just like he says it does. I’m sure he’s right in almost every case, but it’s that one time that you ship a program to your best customer that blows up that will count. To be fair, I almost never destroy variables because most of my work is in-house. But for production work, I would always do it, redundant or not.
Dick, I agree with everything in your last post, especially the implications of the OP being from Microsoft . I can’t argue with a ‘better safe than sorry’ approach. I have recently been ‘trusting’ VB by letting things go out of scope and it’s been a happy experience. I have only had a problem once (the aforementioned issue with controls on a worksheet) and was probably the fault of Excel rather than VB. It may come down to how much you trust your component. For example, ADO seems to be excellently written and even letting an *open* connection go out of scope results in impeccable clean up.
This short example is very clear and useful. Thank you very much!