VBA - Use RAII Design Pattern to tidy your code managing Excel's Status Bar

Summary: Use classes to manage Application state such as Application.StatusBar, Application.DisplayAlerts, Application.ScreenUpdating and Application.EnableEvents.

In this inaugural post we'll start simple with my favourite tip, using RAII design pattern

When working with Excel one will manipulate the Application.StatusBar to convey information to the user.  However, different subroutines may want to convey different information so either they must co-operate with each implementing its own save restore mechanism or alternatively we can use a class.

RAII stands for Resource Acquisition Is Initialization , don't worry it's just a fancy name for a simple technique.  When a class is instantiated its constructor is called and if the reference is stored in a local variable then when the code leaves the scope and is destroyed then the class's destructor is called.

Before we get to the best answer, let's show the less optimal answer.  In the following code, we see that each subroutine implements its own save and restore mechanism to help jointly manage the status bar.  If the status bar is showing the default text, usually "Ready" then capturing this value returns "FALSE".  To reset the status bar we call Application.StatusBar = False.  To set to anything else we just supply the string.  So in the code below the status bar's value is captured upon entering

'Module1.bas
Option Explicit

Sub A()
    Dim vSBSaved As Variant
    vSBSaved = Application.StatusBar

    Application.StatusBar = "Entered A"
    B
    
    
RestoreStatusBar:
    If vSBSaved = "FALSE" Then Application.StatusBar = False Else _
        Application.StatusBar = vSBSaved
    
End Sub

Sub B()
    Dim vSBSaved As Variant
    vSBSaved = Application.StatusBar
    
    Application.StatusBar = "Entered B"
    
RestoreStatusBar:
    If vSBSaved = "FALSE" Then Application.StatusBar = False Else _
        Application.StatusBar = vSBSaved
End Sub

Clearly nobody wants to repeat this code and some of it could be factored out into separate routines.  However, matters get complicated if one starts using Goto such as On Error Goto ErrorHandler and then one will have to jump around to the RestoreStatusBar label.   I'll spare you that bad example, instead let me show you a RAII solution.

'RAIIStatusBar.cls
Option Explicit

Dim vSBSaved As Variant

Sub Class_Initialize()
    vSBSaved = Application.StatusBar
End Sub

Private Sub Class_Terminate()
    If vSBSaved = "FALSE" Then Application.StatusBar = False _
        Else Application.StatusBar = vSBSaved
End Sub

In the above class, we have housed the save and restore code. In Excel VBA instead of constructors and destructors we have the Class_Initialize and Class_Terminate events respectively.  Now it is simple to use this class in your normal code.

'Module2.bas
Option Explicit

Sub A()
    Dim oSB As RAIIStatusBar
    Set oSB = New RAIIStatusBar

    Application.StatusBar = "Entered A"
    B
End Sub '* When oSB goes out of scope status bar is restored to "Ready"

Sub B()
    Dim oSB As RAIIStatusBar
    Set oSB = New RAIIStatusBar
    Application.StatusBar = "Entered B"
End Sub '* When oSB goes out of scope status bar is restored to "Entered A"

Feel free to step through the code so that you understand the sequence of events. This is a powerful technique known to any object orientated programmer working in C++,Java or C#. We can use it in VBA as well.

Can this used for other Application settings? Yes but the Status Bar is a special case because the setting is a string. The other Application states you'd probably handle are simply Booleans.

I give a class now for the Boolean Application.ScreenUpdating and it would be easy to copy across for the other Booleans Application.EnableEvents and Application.DisplayAlerts .

'RAIIScreenUpdating.cls
Option Explicit

Dim bSUSaved As Boolean

Sub Class_Initialize()
    bSUSaved = Application.ScreenUpdating
End Sub

Private Sub Class_Terminate()
    Application.ScreenUpdating = bSUSaved
End Sub

for It is possible to use this pattern to write similar classes for the great many of use cases where a setting has to be saved and restored, such Application.ScreenUpdating, Application.EnableEvents and Application.DisplayAlerts and I give a simple class for ScreenUpdating it would be easy to copy across for the others.