- Sub AppOnOff
- Sub AppEv
- Sub AppCal
- Sub AppSc
You can download the Excel workbook with the sample code at the end of this Article
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.
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 (44 downloads)