VBA Excel: Local variables stored in memory?

Question

In VBA Excel,

I've heard that it's good to declare variables, that are of no further use, as Nothing in the end of module to save memory.

Example:

Dim myRange As Range

Sub Main

   set myRange = Sheets("Sheet1").range("A1:H20")
   ' Do Something
   ' ...
   set myRange = Nothing

End Sub

Is there any truth to this statement or does VBA Excel handle this issue automatically?

possible duplicate of [Is there a need to set Objects to Nothing inside VBA Functions](http://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions) — David Zemens, Aug 22 '13 at 13:57
Also see here: http://stackoverflow.com/questions/4106153/vba-garbage-collector-details — David Zemens, Aug 22 '13 at 13:57

score 4 · Accepted Answer · answered Aug 22 '13 at 13:57

Variables declared within the scope of your procedure are destroyed correctly afterwards.

So in your example, because myRange is defined within the scope of Main it is destroyed/cleaned up appropriately.

However, if you had a module with this code:

Public testForStackOverflow As Range

Sub main()
    Call mySubSub
    MsgBox testForStackOverflow.Address

    Call mySubDestroyer
    If (testForStackOverflow Is Nothing) Then
        MsgBox "destroyed"
    End If

    Call mySubSub
End Sub

Sub mySubSub()
    Set testForStackOverflow = Range("A1")
End Sub
Sub mySubDestroyer()
    Set testForStackOverflow = Nothing
End Sub

Sub callAfterRunningMain()

    MsgBox testForStackOverflow.Address
End Sub

the global is not automatically cleaned up after mySubSub (as should be expected).

What you might not expect is that if you run callAfterRunningMain after running the main method, your global still has a value. This global is not cleaned up unless you manually do it, close Excel, or use End somewhere in your code.

So if you heavily used global variables and did not clean them up somehow, you will keep them in memory indefinitely. This is the case with userforms and using UserForm.hide vs Unload Me too, by the way.

The related questions don't really address what I describe and it can be quite confusing. Especially when trying to "reset" UserForms.

score 1 · Answer 2 · answered Aug 22 '13 at 13:52

1

Even if you do not do that, the fact that the variable goes out of scope will have the any un-referenced object garbage collected.

answered Aug 22 '13 at 13:52

Tarik

10,810
2
26
40

score 1 · Answer 3 · answered Aug 22 '13 at 13:58

If you are dealing with a large number of object variables, like a `Range', at one time, or in a loop that re-creates them before disposing of old objects, Excel will have a problem with that. Garbage collection occurs when Excel thinks it should; some indeterminate time after a variable goes out of scope.

If you are doing lots of repetitions of object variable creation, without leaving the scope in which they are created, it is a very good idea to dispose of them (..=Nothing) when you are done with them.

VBA Excel: Local variables stored in memory?

3 Answers3