You have probably heard some of these simple rules of life:
– If you open something, close it
– If you turn something on, turn it off
– If you spill something, wipe it up
These same rules apply to macro development. Many programmers get sloppy because they assume the computer will clean up for them. In most cases it does, but not always as soon as we would like. It is always good practice to clean up after yourself when exiting a routine or macro. This means closing any files you opened, unloading forms, etc.
Review the following function. You will see that the function level objects eEnum and eScan are set to Nothing prior to exiting.
Public Function ScanText() As Boolean
Dim eEnum As ElementEnumerator
Dim eScan As ElementScanCriteria
Set eScan = New ElementScanCriteria
With eScan
.ExcludeAllTypes
.IncludeType msdElementTypeText
End With
Set eEnum = ActiveModelReference.Scan(eScan)
Do While eEnum.MoveNext
'
' do something with the text elements
'
Loop
Set eEnum = Nothing
Set eScan = Nothing
ScanText = True
End Function
For global (or Public) objects that are used by multiple routines you will need to construct an exit routine to be used as a common exit point for your macro. In the example below the ExitMacro routine unloads any forms and sets all public objects to Nothing.
Public gMyCollection As Collection
Public Sub MyMacro()
Set gMyCollection = New Collection
frmMacro.Show
End Sub
Public Sub ExitMacro()
Unload frmMacro
Set gMyCollection = Nothing
End Sub
What do you want to say? ExitMacro will still not run like a garbage collector
Frank,
I understand the ExitMacro sub will not run like a garbage collector. It is a means to gracefully exit a macro. A macro will not exit if any of the forms are still loaded, so this would be a good place to force the unloading of any forms your macro may have loaded. It is also not good practice to exit a macro with any files left open, so again use the ExitMacro sub to close any files you may have opened. It is also good practice to set any objects to Nothing when you are done with them.