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

Question

I have been studying Excel VBA for the past two years, and I have the idea that it is sometimes appropriate to manage variables at the end of a code segment. For example, I saw how this was done in this bit, adapted from Ron de Bruin 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 worked a bit on this and found very little how to do this, and in one forum post that local variables do not need to be cleared , since they cease to exist with End Sub . I assume that based on the code above, this may not be true in the End Function or in other circumstances that I have not encountered.

So my question comes down to the following:

• Is there somewhere on the Internet that explain when and why to clear variables, and I just didn't find it?

And if someone cannot explain here, please ...

• When is a variable required for Excel VBA, and when not?
• And more specifically ... Are certain variables used (public variables? Functional variables?), Which are longer stored in memory than submarines, and therefore there may be problems if I do not clean after myself?

Answer 1

VB6 / VBA uses a deterministic approach to destroy objects. Each object stores the number of references to itself. When the number reaches zero, the object is destroyed.

Object variables are guaranteed to be cleared (set to Nothing ) when they go out of scope, this reduces the reference counts in their respective objects. No manual action required.

There are only two cases where explicit cleanup is required:

• If you want the object to be destroyed before its variable goes out of scope (for example, your procedure will take a long time and the object stores the resource, so you want to destroy the object to free the resource as soon as possible).

• If you have a circular link between two or more objects.

  If objectA stores references to objectB , and objectB stores references to objectA , these two objects will never be destroyed unless you lock the chain by explicitly setting objectA.ReferenceToB = Nothing or objectB.ReferenceToA = Nothing .

The code snippet shown is incorrect. No manual cleaning required. Even manual cleanup is even harmful, as it gives you a false sense of a more correct code .

If you have a variable at the class level, it will be cleared / destroyed when the class instance is destroyed. You can destroy it earlier if you want (see Clause 1. ).

If you have a variable at the module level, it will be cleared / destroyed when your program exits (or, in the case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see Clause 1. ).

The access level of a variable (public vs. private) does not affect its lifetime.

Answer 2

VBA uses a garbage collector that implements reference counting .

There may be several references to this object (for example, Dim aw = ActiveWorkbook creates a new link to the Active Workbook), so the garbage collector cleans the object only when it becomes clear that there are no other links. Installing to Nothing is an explicit way to reduce the number of links. The graph decreases implicitly when leaving the area.

Strictly speaking, in modern versions of Excel (2010+), the Nothing parameter is not needed, but there were problems with older versions of Excel (for which the workaround was explicitly set)