VBA Class to persist and restore Excel Application properties

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 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?

Class Module: CExcelProperties

```
'These cons

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 + 42
End Enum

And 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 Sub

You'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 SubProcess

-
The 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.

StackExchange Code Review Q#117037, answer score: 10