Clean Up After Yourself

Published on January 5, 2012

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
.IncludeType msdElementTypeText
End With

Set eEnum = ActiveModelReference.Scan(eScan)

Do While eEnum.MoveNext
' do something with the text elements

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

End Sub

Public Sub ExitMacro()
Unload frmMacro
Set gMyCollection = Nothing
End Sub

Like this article? Share it!

2 Responses to “Clean Up After Yourself”

  1. Frank Says:

    January 6th, 2012 at 1:35 am

    What do you want to say? ExitMacro will still not run like a garbage collector

  2. Rod Wing Says:

    February 9th, 2012 at 1:36 pm


    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.

Leave a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Privacy Policy