- Sub AppOnOff
- Sub AppEv
- Sub AppCal
- Sub AppSc
You can download the Excel workbook with the sample code at the end of this Article
Sub AppOnOff
AppOnOff true – turns off Events, Automatic Calculations and Screenupdating
AppOnOff false – turns them back on
AppEv, AppCal and AppSc turns Events, Automatic Calculations and Screenupdating on/off individually
When and why
When your code slows down.
AppCal. If you have a lot of calculations and change the source for the calculations, all calculations using that source will be recalculated – over and over again. AppCal false will set the workbook to manual calculation to prevent this. When data transfer is ready, AppCal true will force a full recalculation.
AppEv. If you have SelectionChange or Change events in your sheet or workbook, they will be triggered if you update the sheet. In worst case it results in an endless loop of events. AppEv false disables event handling. AppEv true enables it. Remember to include AppevTrue in your error handling, or event triggers will stay disabled.
AppSc. Excel insists on updating the screen for every change your code does in the visible area. With AppSc false you can turn off the visible screen updating while you update, and just show the result at the end with AppSc true.
AppOnOff. Just turn off all time-consuming stuff while you are running heavy routines with AppOnOff false.
Purpose of the public variable pubMaintenance
The boolean public variable pubMaintenance is pretty handy. In complex routines running through a bunch of subroutines that might execute for instance Appev true at the end, will cause Application.EnableEvents to be turned on unintentionally and significantly slow down program execution.
To prevent this, we can set pubMaintenance to True. If a subroutine has an AppOnOff true at the end, pubMaintenance=true will exit the subroutine before any changes are done.
Remember to set pubMaintenance to false after execution…
I use pubMainenance in routines that include subroutines or functions that also is run separately, and therefore have AppOnOff, AppEv, AppSc or AppCal commands.
Usage example
Sub TestAppOnOff()
Debug.Print "Test 1:" & Application.EnableEvents
AppOnOff False
pubMaintenance = True
OtherApp
Debug.Print "Test 4:" & Application.EnableEvents
pubMaintenance = False
AppOnOff True
Debug.Print "Test 5:" & Application.EnableEvents
End Sub
Sub OtherApp()
AppEv False
'Running lots of other sub's
'Running lots of other sub's
'Running lots of other sub's
'Running lots of other sub's
'Running lots of other sub's
Debug.Print "Test 2:" & Application.EnableEvents
AppEv True
Debug.Print "Test 3:" & Application.EnableEvents
End Sub
Subroutines AppOnOff, AppEv, AppCal, AppSc code
Public pubMaintenance As Boolean
Sub AppOnOff(Modus)
If pubMaintenance = True Then Exit Sub
With Application
If Modus = False Then
.Calculation = xlCalculationManual
Else
.Calculation = xlCalculationAutomatic
End If
.EnableEvents = Modus
.ScreenUpdating = Modus
End With
End Sub
Sub AppCal(Modus As Boolean, Optional CalcMode As Long)
If pubMaintenance = True Then Exit Sub
If Modus = False Then
Application.Calculation = xlCalculationManual
Else
If CalcMode = 0 Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = CalcMode
End If
End If
End Sub
Sub AppEv(Modus As Boolean)
If pubMaintenance = True Then Exit Sub
Application.EnableEvents = Modus
End Sub
Sub AppSc(Modus As Boolean)
If pubMaintenance = True Then Exit Sub
Application.ScreenUpdating = Modus
End Sub
Download the Excel workbook: Common Library Samples (490 downloads )