Very often on Stack Overflow, and even on Code Review, I've seen questions and answers that have code that begins by persisting Excel properties like
ScreenUpdating to public or private variables, and the restores them at the end of the procedure.
Public bScreenUpdating As Boolean Public bEnableEvents As Boolean Public xlCalc As XlCalculation Public Sub PersistAppSettings() bScreenUpdating = Application.ScreenUpdating bEnableEvents = Application.EnableEvents xlCalc = Application.Calculation End Sub Public Sub DisableAppSettings() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Public Sub RestoreAppSettings() With Application .ScreenUpdating = bScreenUpdating .EnableEvents = bEnableEvents .Calculation = xlCalc End With End Sub
That works well enough when the procedure is viewed in isolation, but it mightn't work very well once the call stack gets deeper. That's potentially a lot of private variables to declare and assign, and it can quickly get confusing about when and where properties are set and unset.
Also, there are at least 4 properties that are regularly persisted, but usually only 3 of the 4 are persisted. Namely:
Calculation, DisplayAlerts, EnabledEvents and ScreenUpdating
Furthermore, if the restore procedure is never called, due to an error or code branching, then the settings are never restored.
The solution? Use a class. I've written a class, and a test harness that shows how it can be used in a stack, and without explicitly restoring the properties. The class exposes properties that allow you to disable certain Excel properties from being restored, and a series of constants allow you to define the default behaviour of the class.
I'd appreciate a review of the class and the test harness. Are there other Excel Properties that could be added?
'These constants define the default restoration behaviors for the class Private Const DEFAULT_RESTORE_CALCULATION = True Private Const DEFAULT_RESTORE_DISPLAY_ALERTS = True Private Const DEFAULT_RESTORE_ENABLE_EVENTS = True Private Const DEFAULT_RESTORE_SCREEN_UPDATING = True 'Set this to true to ensure a persisted state is restored, even if the consumer forgets to restore Private Const RESTORE_ON_TERMINATE = True 'Private members stored in a Type Private this As TMembers Private Type TMembers Calculation As XlCalculationState DisplayAlerts As Boolean EnableEvents As Boolean ScreenUpdating As Boolean RestoreCA As Boolean RestoreDA As Boolean RestoreEE As Boolean RestoreSU As Boolean IsPersisted As Boolean IsRestored As Boolean End Type 'Set the default restoration behaviours on intialize Private Sub Class_Initialize() this.RestoreCA = DEFAULT_RESTORE_CALCULATION this.RestoreDA = DEFAULT_RESTORE_DISPLAY_ALERTS this.RestoreEE = DEFAULT_RESTORE_ENABLE_EVENTS this.RestoreSU = DEFAULT_RESTORE_SCREEN_UPDATING End Sub 'By default, restore the settings if we didn't do it explicitly Private Sub Class_Terminate() If this.IsPersisted And Not this.IsRestored And RESTORE_ON_TERMINATE Then Me.Restore End If End Sub Public Property Get RestoreCalculation() As Boolean RestoreCalculation = this.RestoreCA End Property Public Property Let RestoreCalculation(Value As Boolean) this.RestoreCA = Value End Property Public Property Get RestoreDisplayAlerts() As Boolean RestoreDisplayAlerts = this.RestoreDA End Property Public Property Let RestoreDisplayAlerts(Value As Boolean) this.RestoreDA = Value End Property Public Property Get RestoreEnableEvents() As Boolean RestoreEnableEvents = this.RestoreEE End Property Public Property Let RestoreEnableEvents(Value As Boolean) this.RestoreEE = Value End Property Public Property Get RestoreScreenUpdating() As Boolean RestoreScreenUpdating = this.RestoreSU End Property Public Property Let RestoreScreenUpdating(Value As Boolean) this.RestoreSU = Value End Property Public Sub Save() If Not this.IsPersisted Then 'Save all of the settings With Application this.Calculation = .Calculation this.DisplayAlerts = .DisplayAlerts this.EnableEvents = .EnableEvents this.ScreenUpdating = .ScreenUpdating End With this.IsPersisted = True Else Err.Raise -1000, "CExcelProperties", "Properties have already been persisted." End If End Sub Public Sub Restore() 'Only restore the settings that we want restored '(which by default is all of them) With Application If this.RestoreCA Then .Calculation = this.Calculation End If If this.RestoreDA Then .DisplayAlerts = this.DisplayAlerts End If If this.RestoreEE Then .EnableEvents = this.EnableEvents End If If this.RestoreSU Then .ScreenUpdating = this.ScreenUpdating End If End With this.IsRestored = True End Sub
Sub TestHarness() Dim cProps As CExcelProperties Set cProps = New CExcelProperties 'Save the current properties cProps.Save 'Adjust the Application properties Application.DisplayAlerts = False 'Run something that further adjusts the Application properties Call SubProcess cProps.Restore End Sub Sub SubProcess() Dim cProps As CExcelProperties Set cProps = New CExcelProperties 'Save the current properties cProps.Save Application.DisplayAlerts = True 'Limit the restoration to exclude calculation cProps.RestoreCalculation = False 'Deliberately don't restore, and the terminate event should restore the properties for us End Sub