“Any sufficiently advanced technology is indistinguishable from magic.” [Arthur C. Clarke]
My friend and former colleague Jon T. created the smallest reasonable class module I have seen so far: SystemState provides an easy way to save and to restore system state variables like Calculation, ScreenUpdating and others.
To speed up your code you normally write at the beginning of a VBA macro
Application.Calculation = xlCalculationManual Application.ScreenUpdating = False
and at the end of a macro
Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True
With the class module SystemState you just write at the start
Dim state As SystemState Set state = New SystemState 'Remember: You cannot abbreviate this to "Dim state as New SystemState"!
and at the end
Set state = Nothing 'Not even necessary - will be done automatically
You can control the following system state variables:
|Variable||States||Comment / Speed up with …|
|Calculation||xlCalculationAutomatic, xlCalculationManual, xlCalculationSemiautomatic||Decides whether a recalculation is done after each change to any cell. Set to xlCalculationManual|
|Cursor||xlDefault, xlBeam, xlNorthwestArrow, xlWait||This is just Information. Set to xlWait (hourglass) if you like - but only after having tested your code thoroughly - it is quite annoying to start debugging with an hourglass cursor|
|DisplayAlerts||True, False||If your macro knows what to do it is quite annoying if the system asks you whether you want to overwrite an existing file, for example. Set to False to switch alerts off|
|EnableAnimations||True, False||From Excel version 2016 onwards you can choose whether to use Excel’s screen animations or not|
|EnableEvents||True, False||Set to False to prevent event procedures from being run|
|Interactive||True, False||If set to False all keyboard inputs will be blocked. Dangerous|
|PrintCommunication||True, False||Choose whether or not to alter page setup settings without needing to wait for the printer to respond|
|ScreenUpdating||True, False||Set to False to prevent screen from updating during run|
|StatusBar||False, “Any user information you like”||Text will show up at status bar (bottom line). Cleared if set to False|
Appendix – Class SystemState Code
Please put below code not into a normal module but into a class module:
Please read my Disclaimer.
Option Explicit ' 'This class has been developed by my friend & former colleague Jon T. 'I adapted it to newer Excel versions. Any errors are mine for sure. 'Source (EN): http://www.sulprobil.com/systemstate_en/ 'Source (DE): http://www.bplumhoff.de/systemstate_de/ '(C) (P) by Jon T., Bernd Plumhoff 30-Aug-2022 PB V1.3 ' 'The class is called SystemState. 'It can of course be used in nested subroutines. ' 'This module provides a simple way to save and restore key excel 'system state variables that are commonly changed to speed up VBA code 'during long execution sequences. ' ' 'Usage: ' Save() is called automatically on creation and Restore() on destruction ' To create a new instance: ' Dim state as SystemState ' Set state = New SystemState ' Warning: ' "Dim state as New SystemState" does NOT create a new instance ' ' ' Those wanting to do complicated things can use extended API: ' ' To save state: ' Call state.Save() ' ' To restore state and in cleanup code: (can be safely called multiple times) ' Call state.Restore() ' ' To restore Excel to its default state (may upset other applications) ' Call state.SetDefaults() ' Call state.Restore() ' ' To clear a saved state (stops it being restored) ' Call state.Clear() ' Private Type m_SystemState Calculation As XlCalculation Cursor As XlMousePointer DisplayAlerts As Boolean EnableAnimations As Boolean 'From Excel 2016 onwards EnableEvents As Boolean Interactive As Boolean PrintCommunication As Boolean 'From Excel 2010 onwards ScreenUpdating As Boolean StatusBar As Variant m_saved As Boolean End Type ' 'Instance local copy of m_State? ' Private m_State As m_SystemState ' 'Reset a saved system state to application defaults 'Warning: restoring a reset state may upset other applications ' Public Sub SetDefaults() m_State.Calculation = xlCalculationAutomatic m_State.Cursor = xlDefault m_State.DisplayAlerts = True m_State.EnableAnimations = True m_State.EnableEvents = True m_State.Interactive = True m_State.PrintCommunication = True m_State.ScreenUpdating = True m_State.StatusBar = False m_State.m_saved = True 'Effectively we saved a default state End Sub ' 'Clear a saved system state (stop restore) ' Public Sub Clear() m_State.m_saved = False End Sub ' 'Save system state ' Public Sub Save(Optional SetFavouriteParams As Boolean = False) If Not m_State.m_saved Then m_State.Calculation = Application.Calculation m_State.Cursor = Application.Cursor m_State.DisplayAlerts = Application.DisplayAlerts m_State.EnableAnimations = Application.EnableAnimations m_State.EnableEvents = Application.EnableEvents m_State.Interactive = Application.Interactive m_State.PrintCommunication = Application.PrintCommunication m_State.ScreenUpdating = Application.ScreenUpdating m_State.StatusBar = Application.StatusBar m_State.m_saved = True End If If SetFavouriteParams Then Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Application.EnableAnimations = False Application.EnableEvents = False Application.PrintCommunication = False Application.ScreenUpdating = False Application.StatusBar = False End If End Sub ' 'Restore system state ' Public Sub Restore() If m_State.m_saved Then 'We check now before setting Calculation because setting 'Calculation will clear cut/copy buffer If Application.Calculation <> m_State.Calculation Then Application.Calculation = m_State.Calculation End If Application.Cursor = m_State.Cursor Application.DisplayAlerts = m_State.DisplayAlerts Application.EnableAnimations = m_State.EnableAnimations Application.EnableEvents = m_State.EnableEvents Application.Interactive = m_State.Interactive Application.PrintCommunication = m_State.PrintCommunication Application.ScreenUpdating = m_State.ScreenUpdating If m_State.StatusBar = "FALSE" Then Application.StatusBar = False Else Application.StatusBar = m_State.StatusBar End If End If End Sub ' 'By default save when we are created ' Private Sub Class_Initialize() Call Save(SetFavouriteParams:=True) End Sub ' 'By default restore when we are destroyed ' Private Sub Class_Terminate() Call Restore End Sub
Please read my Disclaimer.
systemstate.xlsm [26 KB Excel file, open and use at your own risk]