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 DisplayAlerts and ScreenUpdating to public or private variables, and the restores them at the end of the procedure.Public bScreenUpdating As BooleanPublic bEnableEvents   As BooleanPublic xlCalc          As XlCalculationPublic Sub PersistAppSettings()  bScreenUpdating = Application.ScreenUpdating  bEnableEvents = Application.EnableEvents  xlCalc = Application.CalculationEnd SubPublic Sub DisableAppSettings()  With Application    .ScreenUpdating = False    .EnableEvents = False    .Calculation = xlCalculationManual  End WithEnd SubPublic Sub RestoreAppSettings()      With Application    .ScreenUpdating = bScreenUpdating    .EnableEvents = bEnableEvents    .Calculation = xlCalc  End WithEnd SubThat 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 ScreenUpdatingFurthermore, 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?Class Module: CExcelProperties'These constants define the default restoration behaviors for the classPrivate Const DEFAULT_RESTORE_CALCULATION = TruePrivate Const DEFAULT_RESTORE_DISPLAY_ALERTS = TruePrivate Const DEFAULT_RESTORE_ENABLE_EVENTS = TruePrivate Const DEFAULT_RESTORE_SCREEN_UPDATING = True'Set this to true to ensure a persisted state is restored, even if the consumer forgets to restorePrivate Const RESTORE_ON_TERMINATE = True'Private members stored in a TypePrivate this As TMembersPrivate 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 BooleanEnd Type'Set the default restoration behaviours on intializePrivate 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_UPDATINGEnd Sub'By default, restore the settings if we didn't do it explicitlyPrivate Sub Class_Terminate()  If this.IsPersisted And Not this.IsRestored And RESTORE_ON_TERMINATE Then    Me.Restore  End IfEnd SubPublic Property Get RestoreCalculation() As Boolean  RestoreCalculation = this.RestoreCAEnd PropertyPublic Property Let RestoreCalculation(Value As Boolean)  this.RestoreCA = ValueEnd PropertyPublic Property Get RestoreDisplayAlerts() As Boolean  RestoreDisplayAlerts = this.RestoreDAEnd PropertyPublic Property Let RestoreDisplayAlerts(Value As Boolean)  this.RestoreDA = ValueEnd PropertyPublic Property Get RestoreEnableEvents() As Boolean  RestoreEnableEvents = this.RestoreEEEnd PropertyPublic Property Let RestoreEnableEvents(Value As Boolean)  this.RestoreEE = ValueEnd PropertyPublic Property Get RestoreScreenUpdating() As Boolean  RestoreScreenUpdating = this.RestoreSUEnd PropertyPublic Property Let RestoreScreenUpdating(Value As Boolean)  this.RestoreSU = ValueEnd PropertyPublic 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 IfEnd SubPublic 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 = TrueEnd SubStandard Module: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.RestoreEnd SubSub 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 usEnd Sub
VBA Class to persist and restore Excel Application properties
object oriented;vba;excel
I like the idea very much - encapsulating this concern in its own class is very OOP and certainly helps writing cleaner VBA.I like that you're wrapping all fields in a private type, leaving only this as a private field, which makes implementing (and more importantly, reading!) the properties a pleasure. I would have made the private type's member names match the properties identically though: RestoreCA and the other abbreviated members feel a bit sloppy, there's not really a reason to not spell them out completely. Doing so would have been perfectly consistent.I'm not sure why RESTORE_ON_TERMINATE is a constant rather than a configurable property though; code shouldn't need to change, especially not for changing a constant value. Rename it to DEFAULT_RESTORE_ON_TERMINATE and add the corresponding members, and you're never going to need to change it.I think I would nest the condition in Class_Terminate, to check for this.RestoreOnTerminate and skip the other checks entirely when it's false.I don't think any of the comments provide any value - your code is clear and readable, and speaks for itself already; the comments are somewhat redundant, I'd remove them.The name Save isn't consistent with the terminology you're using, I'd rename it to Persist... but either is somewhat confusing, since you're not physically persisting anything, merely remembering values for the lifetime of the object - but Remember would be even more awkward, so you're forgiven ;-)The With blocks are fine, but I wouldn't have them... but this might only be because I dislike additional nesting levels more than repetitive/explicit calls to the same object. Or it could be my C# eyes being used to such explicit calls. Or it could be just me not liking the VB With keyword at all - in that case don't mind me, your code is fine.I don't like the hard-coded -1000 error number there. A smart little ducky once showed me a neat little trick that stuck with me:Public Enum ExcelPropertiesError    Error_AlreadyInitialized = vbObjectError + 42End EnumAnd now you can do this:Err.Raise Error_AlreadyInitialized '...And the client code that's handling it can use the enum instead of hard-coding error numbers everywhere, and if you have more errors to raise, you just add an enum member and VBA automatically makes it vbObjectError + 43 :-)Your tests are insufficient. Get Rubberduck and write actual unit tests that cover all execution paths: you want one test to cover one thing, or rather, you want each test to have one reason to fail - and more importantly you want your tests to fail when something changes in the class under test, that changes the behavior that the test is documenting.For example, you'll want a test that fails when Error_AlreadyInitialized isn't raised when you call Save twice.You'll want a test for each property you're persisting, that fails when the getter doesn't return the value that was persisted; you'll want a test for each getter again, that fails when it doesn't return the expected value after it was saved and then modified.Also, you'll want a test that covers this nasty gotcha:Sub DoSomething()    Dim props As New CExcelProperties    props.Save    Application.DisplayAlerts = False    'does it restore?End SubYou'll also want to test what happens when more than once instance is alive at different depths in the call stack; you might want to consider making the class have a PredeclaredId and living inside a separate project than the client code that uses it, so that is can't be instantiated and every call is made on the same, default instance - how would the Terminate handler deal with that?Nitpicks:Call is obsolete, forget it even exists. Use the modern implicit call syntax for calling functions and procedures instead:SubProcess 'instead of Call SubProcessThe value parameter of each getter is implicitly passed ByRef, and should be passed ByVal. That's something Rubberduck lets you fix with a single click.