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.
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!
Recent Comments