Controlling When Application Properties Are Toggled and Controlling Events

This article actually covers two topics. The first is on controlling when application properties are toggled, and the second shows how to adapt that methodology into a superior method to manage events and prevent recursive calls. The first example set is purely based on Excel, but the methodology can be easily adpated to other applications. The second example is based on an userform and is not application specific.

Controlling When Application Properties Are Toggled:
I frequently have routines in Excel where I turn off ScreenUpdating, both to stop the flashing and speed up the code. The issue that I ran into is that I like to separate my code into reusable chunks. I didn't want to turn ScreenUpdating off/on in each routine, since the screen would then flash between routines, which ended up forcing me to write a wrapper for every function, in case I wanted to run in on it's own. This method takes care of that problem. I can write the ScreenUpdate into each chunk, and it will turn ScreenUpdates back on only if it was run standalone. This allows me to call as many routines as I want, nest them and more.

Methodology:
The method makes use of a Global variable of the data type Long. Each time I execute a routine where I want to suppress ScreenUpdates, I simply add one to the value of the Global variable. I then run through the routine, subtracting one from the Global variable at the end. If the Global variable's value has returned to zero, then I turn the ScreenUpdates back on. If not, the routine must have been called from another function, so the ScreenUpdating is left off until focus is returned back (eventually) to the initial routine.

While this was designed with ScreenUpdating in mind, it could easily work for other application level properties such as Application.DisplayAlerts. Read on to the next section to understand why I would NOT use it for EnableEvents, however.

Example:
The example is fairly simplistic, but illustrates the point quite nicely. The first two routines are individual code chunks that hold the ScreenUpdating test:

Public lScreenUpdate As Long

Sub CopyOne()
    Application.ScreenUpdating = False
    lScreenUpdate = lScreenUpdate + 1

    With ActiveSheet
        .Range("A1:A5").Copy
        .Range("B1:B5").PasteSpecial Paste:=xlPasteValues
    End With

    lScreenUpdate = lScreenUpdate - 1
    If lScreenUpdate < 1 Then Application.ScreenUpdating = True

End Sub

Sub InsertFormula()
    Application.ScreenUpdating = False
    lScreenUpdate = lScreenUpdate + 1

    ActiveSheet.Range("C1:C5").FormulaR1C1 = "=sum(RC[-2]:RC[-1])"

    lScreenUpdate = lScreenUpdate - 1
    If lScreenUpdate < 1 Then Application.ScreenUpdating = True

End Sub

Each of these routines can be called on their own, and the ScreenUpdating will resume at the end of the routine. On the other hand, in the example below, ScreenUpdates will ONLY resume at the end of the DoBoth routine, despite firing both the routines above!

Sub DoBoth()
    Application.ScreenUpdating = False
    lScreenUpdate = lScreenUpdate + 1

    Call CopyOne
    Call InsertFormula

    lScreenUpdate = lScreenUpdate - 1
    If lScreenUpdate < 1 Then Application.ScreenUpdating = True

End Sub

Now imagine that the DoBoth routine is called from yet another routine. ScreenUpdates would not resume at the end of DoBoth if the wrapper were handled correctly from the calling subroutine.

Adapting To Handle Events:
The basic principle behind this method remains the same; create a Global variable to hold a value. That value is incremented every time you enter an event that you may want toggled, and decremented every time the event's procedure ends. The difference is that you check if the variable is greater than one. If so, then events have been flagged as unrequired from a previous procedure, so just let the code run through the decrementation process as exit. If it is not greater than one (is equal to one), then run your event code and follow the decrementation process on the way out. Why the difference of checking for one versus zero? Mainly because the test happens near the beginning of the procedure, as compared to the end as in the case of the Properties method. If you've nested other routines in there that call or trigger other events, you want the value incremented whether events have been turned on or off. Since that is the first thing that will happen, the very first entry into and event will automatically increase the Global variable value to one.

Some Benefits From This Approach Vs The Standard Application.EnableEvents Method:

• When you have several routines that may require events to be disabled, this helps to easily manage when they are turned back on.
• Because the event trigger is held in a global variable, it always go out of scope when the code quits. This means events are never accidentally disabled when code is run.
• This method will work to turn off events in userforms. (Application.EnableEvents does not work in Userforms.)
• The technique is portable to other applications. Excel's Application.EnableEvents does not work in Word, for example, but this method will work in any application.

Example:
The example that follows is a complete hypothetical, and will be replaced with a real world situation when an appropriate one strikes my fancy.

Public lEvents As Long
Private Sub Procedure_One()

'Flag events to be turned off
lEvents = lEvents + 1

If lEvents > 1 Then
    'Events turned off, so do nothing (unless desired)
Else
    'Events turned on, so run your normal code here
End If

'Turn off events flagging
lEvents = lEvents - 1

End Sub

Assume that you're using a userform for a moment, and that you have several Change events that affect other objects with Change events. You can quickly visualize how this will work. The first event will set lEvents to 1, and will therefore fail the IF test. The "normal code" triggers a change event in another control. When that control is entered, however, lEvents increases to 2, so passes the IF test, which effectively avoids your "normal code" since there is nothing in the if clause. At the end of that procedure, lEvents returns to 1, the focus passes back to the first event which complets, setting lEvents back to 0.

The true power in this routine, like the properties method above, is actually manifested when you nest items that need events controlled within each other. Each time you enter a controlled event, the lEvents keeps having one added to its value, and keeps decrementing as each procedure ends and returns focus to the calling procedure.