Excel VBA: Restoring the State of the Application Before Returning Control to the User

When your VBA code finished its task and control is returned to the user – be sure to restore protection, hide Worksheets, enable events, reset the mouse cursor and turn on screen updating * here’s a Sub that will take care of that

A controlled environment for the user

The flexibility, diversity and power of Excel makes it a generic tool for almost any task. However, this richness of options and openness can put your data and business at risk.

Consider a large table you maintain in Excel. Mistakenly, you mis-typed a date to be: 05/10/20020. You didn’t notice. That row is lost somewhere in the table and one day, you realize a chart is not displaying right, or a formula result is not accurate. You may not even notice anything wrong at all.

In another situation, you clicked a button to run a process implemented in VBA. The process takes a while, but you have no clue. After 45 second you believe something is stuck. You break the program. The process was about to finish in 3 more seconds. How could you guess?

In another scenario, a helper Worksheet was left unprotected and you were tempted to play around with it, to make improvements, of course. You broke something that only the developer of this program knows about. You’re now in trouble.

Of course, when writing robust business applications with Excel, none of the above should be allowed. You can go back to the second post in my Blog in which I explained how Excel can be used for developing business information systems for more details on what it takes to make a controlled environment for the user.

In a controlled environment, we (the programmers) restrict the user to do only what he has to do, at the right time he is required to do it, keeping him informed of what’s happening in the background and keeping our data and program intact.

What are we controlling for?

There are some basic concepts we need to consider when developing a controlled VBA program, the most common would be:

• Data entry and updates with User Forms – not directly in tables.
• Data validation in User Forms with proper messages for correcting errors. Only validated records are saved in the table.
• Helper Worksheets are hidden to the user.
• Workbook and all Worksheets are password-protected (read more about protection strategies in Excel here).
• (sometimes) Set full screen with no access to the Excel menus and formula bar.
• Proper indication to the user of running processes: with status-bar alerts, mouse cursor or on-screen notifications.

Taking control in our VBA program

While we keep a tight controlled environment for the user, our VBA code needs the freedom to manipulate everything.

When we save a record into a table, that Worksheet holding the table cannot be protected.

When we prepare a PDF report using a hidden Worksheet serving as the template for the report – that Worksheet cannot be hidden.

In addition, we may control the behavior of Excel to prevent events from being raised, or for better performance / human experience.

Bottom line, we need a robust mechanism to switch between the “open” environment we need for our VBA program and the controlled environment we need for the user.

The Basic Function Structure for Taking Control

In the following example, I’m releasing control of almost everything, just to make the point. In reality, you only release control of what you need for the specific function in question.

Sub HighInterventionTask()

    On Error GoTo FailedSub

    Notify "Finishing calculations. Please wait..."     'Will display in the Status-Bar

    Application.Cursor = xlWait                         'Mouse cursor indicates system is busy

    Application.EnableEvents = False                    'Worksheet events will not be raised

    Application.ScreenUpdating = False                  'Screen will not flicker to the user

    Application.Calculation = xlCalculationManual  'Pausing cells calculations to speed-up

    UnprotectSheet "Sales"                             'Will unprotect the Sales Worksheet with password

    UnhideSheet "Sales"                                  'Will unhide the Sales Worksheet

    'Do your VBA magic here - what this Sub is supposed to be doing

CloseSub:

    'RESTORE STATE

    HideSheet "Sales"

    ProtectSheet "Sales"

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    Application.Cursor = xlDefault

    Application.ScreenUpdating = True

    NotifyOff

    Exit Sub

FailedSub:

    'Handle the error

    GoTo CloseSub

End Sub

By the way, if you’re new to the “On Error GoTo FiledSub” concept – read about Error Handling here.

Simplifying Restore State

As you can see, in the above example, restoring the state in the CloseSub section is pretty elaborate. What’s more, we need to repeat it in many Subs and Functions.

Releasing control at the beginning of the function may be different from Sub to Sub. We may only need to turn off ScreenUpdating in one function, but also unprotect a Worksheet in another. That’s why I explicitly do only what I need in every function.

However, when restoring the state, I don’t mind running all commands every time as the performance toll is not that significant, while I make sure I don’t forget anything.

Therefore, a dedicated Sub for restoring the state is begging itself. Here’s how it looks like:

Sub RestoreState(Optional SheetToProtect As String = vbNullString)

    Notify "Finishing calculations..."

    If InProduction Then

        Call HideFlaggedSheets

    End If

    If (SheetToProtect <> vbNullString) Then

        ProtectSheet SheetToProtect

    End If

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    Application.Cursor = xlDefault

    On Error GoTo 0

    Call NotifyOff

    Application.ScreenUpdating = True

End Sub

As you can see, I’m only protecting a Worksheet if it is needed. If I know I released protection in the calling function, I will add this Worksheet’s name when calling RestoreState:

RestoreState “Sales”

This greatly simplifies our CloseSub section while making sure we don’t forget anything:

CloseSub:

    'RESTORE STATE

    RestoreState “Sales”   

    Exit Sub

If you’re asking yourself what is the “InProduction” all about. This is checking my Production flag. If I’m in development mode I don’t want to hide any sheets.

The HideFlaggedSheets hides all Worksheets flagged to be hidden.

I elaborate on both of these techniques in my Udemy course: 105 Excel VBA Functions Explained (that includes the RestoreState and HideFlaggedSheets functions).

Please share this Blog post with others and leave any comments or questions you have below.