Preventing Errors through Protection


« Back to videos

Additional Information:

As we are now building macros for other users, it is assumed that you can write basic code. This means the tutorial can explore a number of different functions. Even so, we only really scratch the surface of the MsgBox, Undo and Protect functions.

In this video, we use the Protect/Unprotect command to prevent users editing certain cells. We do this by locking/unlocking cells as required. In Excel 2007 and 2010, you can protect sheets by selecting Review->Unprotect.

When protecting a sheet, you will observe there are a number of different options. These can be used to protect cell formats or stop users deleting rows. Within Excel you can change the protection settings by selecting different combinations of checkboxes. When you apply protection using the code ActiveSheet.Protect, the sheet will default to its most recent protected state. Therefore, if you first protected the sheet within regular Excel, the Protect command will restore your previous settings without requiring further qualification. Should you wish to apply a new form of protection using Visual Basic, you can figure out the code by recording a macro.

In this module, we are using sheet protection to prevent our macro getting damaged, but you may be working on a sheet that is already password protected. To add/remove protection, you can use the line ActiveSheet.Protect/Unprotect Password:="password".

The Undo command is used in this macro to validate data. Application.Undo has exactly the same effect as pressing Ctrl+Z within Excel. Unfortunately, you cannot undo the actions of a macro. This means that if your macro has already made changes to a sheet's content e.g. inserted a value, changed a font, Application.Undo does not work. This results in an error, so Undo should always be used near the start of the macro, preferably in conjunction with error handling.

The MsgBox function can also be difficult to use. A simple pop-up message takes the form MsgBox("message") and will not interfere with any other lines of code. An application of this would be to display a click through message on completion.

In the video, we give the user the option of clicking Yes or No. There are several different types of MsgBox and Visual Basic Editor should display them when you type a comma after the message itself. It is worth bearing in mind that there is always an additional outcome for any message box. As well as selecting Yes or No, a user might hit the red cross in the top right hand corner of the box. So when testing your code, you should ensure this possibility has been considered. This can be more of an issue with forms which are covered later in this course.

Our example sub routine uses the IsNumeric code string to validate input data. Visual Basic cannot perform arithmetic with text inputs so this can prevent errors. It is a Visual Basic function so has the VBA. prefix. If you type "VBA.Is" into a module, you will see there are other options enabling you to find formula errors (IsError) and validate dates (IsDate). These may be of use on occasion.

Functions such as IsNumeric return Boolean values. That is to say they are logical tests and can be true or false. If you do not specify True or False when applying a condition, True is implied e.g. If VBA.IsNumeric(5) Then MsgBox("5 is a number") is a perfectly valid line of code.