When should an Excel VBA variable be killed or set to Nothing?

Question

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:

• Is there somewhere on the web that explains the when and why for variable cleanup, and I just have not found it?

And if not can someone here please explain...

• When is variable cleanup for Excel VBA necessary and when it is not?
• And more specifically... Are there specific variable uses (public variables? Function-defined variables?) that remain loaded in memory for longer than subs do, and therefor could cause trouble if I don't clean up after myself?

Answer 1

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:

1. 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).

2. 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.

Answer 2

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)