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