Excel VBA: Subroutines for speed optimization

• 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 (477 downloads )