Knowledge Base Articles By Category
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:
Code:
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
Code:
Sub DoBoth()
Application.ScreenUpdating = False
lScreenUpdate = lScreenUpdate + 1
Call CopyOne
Call InsertFormula
lScreenUpdate = lScreenUpdate - 1
If lScreenUpdate < 1 Then Application.ScreenUpdating = True
End Sub
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.
Code:
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
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.
Recent Forum Posts
