I've been teaching myself Excel VBA over the last two years, and I have the idea that it is sometimes appropriate to dispose of variables at the end of a code segment. For example, I've seen it done in this bit adapted from Ron de Bruin's code for transferring Excel to HTML:
Function SaveContentToHTML (Rng as Range)
Dim FileForHTMLStorage As Object
Dim TextStreamOfHTML As Object
Dim TemporaryFileLocation As String
Dim TemporaryWorkbook As Workbook
...
TemporaryWorkbook.Close savechanges:=False
Kill TemporaryFileLocation
Set TextStreamOfHTML = Nothing
Set FileForHTMLStorage = Nothing
Set TemporaryWorkbook = Nothing
End Function
I've done some searching on this and found very little beyond how to do it, and in one forum post a statement that no local variables need to be cleared, since they cease to exist at End Sub. I'm guessing, based on the code above, that may not be true at End Function, or in other circumstances I haven't encountered.
So my question boils down to this:
And if not can someone here please explain...
VB6/VBA uses deterministic approach to destoying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.
Object variables are guaranteed to be cleaned (set to Nothing) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required.
There are only two cases when you want an explicit cleanup:
When you want an object to be destroyed before its variable goes out of scope (e.g., your procedure is going to take long time to execute, and the object holds a resource, so you want to destroy the object as soon as possible to release the resource).
When you have a circular reference between two or more objects.
If objectA stores a references to objectB, and objectB stores a reference to objectA, the two objects will never get destroyed unless you brake the chain by explicitly setting objectA.ReferenceToB = Nothing or objectB.ReferenceToA = Nothing.
The code snippet you show is wrong. No manual cleanup is required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code.
If you have a variable at a class level, it will be cleaned/destroyed when the class instance is destructed. You can destroy it earlier if you want (see item 1.).
If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item 1.).
Access level of a variable (public vs. private) does not affect its life time.
I have at least one situation where the data is not automatically cleaned up, which would eventually lead to "Out of Memory" errors. In a UserForm I had:
Public mainPicture As StdPicture
...
mainPicture = LoadPicture(PAGE_FILE)
When UserForm was destroyed (after Unload Me) the memory allocated for the data loaded in the mainPicture was not being de-allocated. I had to add an explicit
mainPicture = Nothing
in the terminate event.
VBA uses a garbage collector which is implemented by reference counting.
There can be multiple references to a given object (for example, Dim aw = ActiveWorkbook creates a new reference to Active Workbook), so the garbage collector only cleans up an object when it is clear that there are no other references. Setting to Nothing is an explicit way of decrementing the reference count. The count is implicitly decremented when you exit scope.
Strictly speaking, in modern Excel versions (2010+) setting to Nothing isn't necessary, but there were issues with older versions of Excel (for which the workaround was to explicitly set)
Source: Stackoverflow.com