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?

Jedlicka

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.

Hypnoanalysis

Thanks GSerg, this explanation is clear and concise and gives me a much better sense for variable management. One additional question: you say "access level of variable does not affect its lifetime". Do you know if that lifetime is bounded by when a value is first set by the active code (start) and when the code that code reaches the End tag (finish)? Or does it end when the project resets? I tried testing this theory by setting the value of a public variable that was dimmed in another module, but the variable never appeared in the locals window, so I couldn't observe when it dropped. – Jedlicka

@KarlRookey Public vs private is different from class level vs procedure level. Public vs private does not affect the life time. Class level means variable lives as long as the class lives. Module level means variable lives as long as the project is not reset. Note that "lives" and "contains something" are not synonims. Variable can live and contain Nothing. – Hypnoanalysis

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)

Haldi

Answer 3

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.

Wellknown

Answer 4

When you are using ASP classic (server-side scripting), it is important to set all objects to nothing when you are through with them, because they do not go out of scope until the [virtual] server is shut down.

For this reason, all MS VB scripting examples always showed objects being closed and set to nothing. So that the script excerpts could be used in environments like ASP classic where the objects did not go out of scope.

There are, rarely, other situations where you wish to code long-running processes where the objects do not go out of scope, and you find yourself running out of physical memory if you do not explicitly release objects.

If you find yourself coding ASP classic, or running processes in global scope for some other reason, then yes, you should explicitly release objects.

Paling