Preventing Multiple Instances of a Macro

Published on October 7, 2010

It can be easy for a user to start multiple instances of your macro program, especially if it contains non-modal user forms. In some cases this may not be a problem, but usually, having multiple instances of a macro running simultaneously can lead to problems. To prevent this from happening simply declare a Public Boolean variable. Set its value to True while your macro is running; set it to False on exit. At the beginning of the macro test the value of the variable. If it is true that means the macro is already running so exit the macro. For this to work properly you must be very diligent in your error trapping and ensure that the variable is set to False at any possible exit point.


Public MacroIsRunning As Boolean

Sub MyMacro()
If MacroIsRunning Then
Exit Sub
End If

On Error Goto ERROR_HANDLER

MacroIsRunning = True


‘ Do stuff here

MacroIsRunning = False
Exit Sub

ERROR_HANDLER:

‘ Do error handling here

MacroIsRunning = False
End Sub


Tags:
Like this article? Share it!

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