Perfect macros don't go wrong, and don't need error-handling? Sadly, not true - this tutorial explains how error-handling works in VBA, with examples.
Our Example
Let's start with a simple macro. You want to go to a worksheet and put your name in cell A1, but you're not sure if the worksheet exists. So you could set an error trap:
Sub SignTopLeftCell()
On Error GoTo NoWorksheet
Worksheets("TestSheet").Select
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
MsgBox "No such worksheet!"
End Sub
This is the template for virtually every error-handling routine: set an error trap, designate where you should go if it's triggered, and exit the subroutine if if isn't.
Creating Labels
A label in VBA ends with a colon or : and denotes a place you can jump to.
Labels always appear on the left-hand side of the text:
Like most things in VBA, a label can't contain spaces, and should be written in CamelCase (that is, with each word capitalised).
Using ON ERROR to Create an Error Trap
Under normal circumstances, a macro will crash if an error occurs. In the above code, without the error trap this is what you'd see if you tried to go to a non-existent worksheet:
The subscript is out of range because there is no object in the collection of worksheets called TestSheet.
To avoid this happening, you can take control of error messages with one of three statements:
Statement | What it means |
|---|
On Error GoTo SomeLabel | If an error happens, jump to the given label. |
On Error Resume Next | If an error happens, ignore it and go on to the next statement. |
On Error GoTo 0 | Revert to the default error-handling (ie displaying a system error message). |
The second statement is as dangerous as it sounds. We're not in any way fixing the error - just ignoring it. In the above subroutine, it would have the following effect:
The routine would fail to go to the TestSheet worksheet, but not display any error message; and then
The code would sign cell A1 in whatever the current worksheet happened to be.
I would avoid On Error Resume Next - it's just asking for trouble!
Resetting Error Handling to the Default
As soon as an error occurs, you should really reset error handling. Thus our final code would look like this:
Sub SignTopLeftCell()
On Error GoTo NoWorksheet
Worksheets("TestSheet").Select
On Error GoTo 0
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
On Error GoTo 0
MsgBox "No such worksheet!"
End Sub
I've included the statement:
to make sure that if an error occurs which I hadn't foreseen, it doesn't display a misleading error message.
Using Err.Number and Err.Description
To understand how to customise error-handling messages, you first need to know about the Err object! In the example above, without error-trapping you would see this message when you try to go to a worksheet which doesn't exist:
This shows the error number (9), and the error description (Subscript out of range).
The Err object in VBA is created when an error occurs. It has two main properties: the Number of the error, and a Description of it.
You can pick up on the error number to check that you're displaying the right message, and incorporate the error description into your customised error message. Here's an example of both of these things, for our example macro:
Sub SignTopLeftCell()
Dim ErrorNumber As Long
Dim DoubleLineBreak As String
DoubleLineBreak = vbNewLine & vbNewLine
On Error GoTo NoWorksheet
Worksheets("TestSheet").Select
On Error GoTo 0
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
ErrorNumber = Err.Number
Select Case ErrorNumber
Case 9
MsgBox "No such worksheet!" & DoubleLineBreak & _
"The internal error message for this is: " & _
DoubleLineBreak & _
UCase(Err.Description), _
vbOKOnly + vbExclamation, "Wise Owl error"
Case Else
MsgBox "AAAAARH! Error number " & Err.Number & _
" has happened!"
End Select
MsgBox "No such worksheet!"
End Sub
Here we detect the error number, and display a customised message if it's 9 (ie if the worksheet can't be found):
MsgBox "No such worksheet!" & DoubleLineBreak & _
"The internal error message for this is: " & _
DoubleLineBreak & _
UCase(Err.Description), _
vbOKOnly + vbExclamation, "Wise Owl error"
Here's the message youi'll get if you run this macro when the worksheet in question doesn't exist:
The error message includes the built-in description, converted to upper case.
Creating a Standard Error-Handling Function
You really don't want to be including lines and lines of error-handling code in every single routine. Wise Owl tend to call a standard error-handling routine, passing in the name of the error message to be displayed and (optionally) a title:
Sub ShowError(ErrorMessage As String, _
Optional ErrorTitle As String = "Wise Owl error")
MsgBox _
prompt:=ErrorMessage, _
Buttons:=vbOKOnly + vbExclamation, _
Title:=ErrorTitle
Stop
End Sub
I also find it useful to go into break mode using the Stop statement when developing, so I can see what went wrong.
Here is the code which would call this, then:
Sub SignatureWithErrorRoutine()
On Error GoTo NoWorksheet
Worksheets("TestSheet").Select
On Error GoTo 0
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
ShowError "No such worksheet as TESTSHEET"
End Sub
Although it's not for the purist (none of this tutorial is!), it works and is about as simple as you can get while still being effective.
Other Options for What to do in an Error-Handling Routine
For the sake of completeness, in addition to just telling your user that something has gone wrong, there are a couple of other things that you can do. These are:
Statement | What it would do |
|---|
Resume | Try repeating the line which made the routine crash. |
Resume Next | Continue running the subroutine beginning with the line immediately following the one which made it crash. |
Here's an example of the use of Resume in our macro:
Sub SignatureWithErrorCorrection()
Const SheetName As String = "TestSheet"
On Error GoTo NoWorksheet
Worksheets(SheetName).Select
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
Worksheets.Add
ActiveSheet.Name = SheetName
Resume
End Sub
Coding like this is just asking for trouble! If your attempt to create a new worksheet doesn't work for whatever reason (and I can think of a few circumstances when it wouldn't) this macro will loop indefinitely.
A Worked Example of Error-Handling
Having looked at the syntax of error-handling code, it's time now to consider a worked example. A common requirement in VBA programs is to display an input box, and get the user to type in an integer. Here is a routine which will allow you to do just that:
Function GetInteger() As Long
Dim InputString As String
InputString = InputBox("Input a number")
If Len(InputString) = 0 Then
GetInteger = -1
Exit Function
End If
On Error GoTo NotInteger
GetInteger = CLng(InputString)
Exit Function
NotInteger:
GetInteger = -1
End Function
Here is how you could call this routine to get a whole number (integer), then display its square:
Sub SquareNumber()
Dim NumberToSquare As Long
NumberToSquare = GetInteger
If NumberToSquare = -1 Then
MsgBox "You must enter an integer"
Exit Sub
End If
MsgBox "Square of " & NumberToSquare & _
" is " & (NumberToSquare ^ 2)
End Sub
When you run the SquareNumber routine, if you enter text into the input box it will trigger an error and pass control to the NotInteger label. This in turn will set the value of the function to -1.
If you type in text like this, the code will trigger an error when you try to convert it to an integer.
You will then see an error message like this:
The message which will appear if you leave the input box blank or type in something which isn't a whole number.
This is a typical use of error trapping:
We know we are about to do something which sometimes won't work;
We set an error trap giving a label to jump to in the event of an error;
We run the code, secure in the knowledge that it will do the right thing whether or not the user reacts in the correct way.
Raising Errors
If system errors aren't enough for you, why not create your own? Although your first reaction to this proposal might be one of disbelief ("why on earth would I ever want to do that"?), it can be useful. The command in question is as follows:
You can specify an error number, the source of the error and a message.
Since these things always make more sense with examples, here's our code to select a worksheet (again!), but this time raising an error when a problem occurs:
Sub SignatureWithErrorCorrection()
Const SheetName As String = "TestSheet"
On Error GoTo NoWorksheet
Worksheets(SheetName).Select
Range("A1").Value = "Wise Owl"
Exit Sub
NoWorksheet:
Err.Raise _
Number:=vbObjectError + 1, _
Source:="Crashed in " & Err.Source, _
Description:="Tried to go to worksheet " & _
UCase(SheetName) & ", which doesn't exist"
End Sub
Here's the error message you'll see if the TestSheet worksheet doesn't exist when you run this routine:
The error message shows the number of your error and the description.
Microsoft recommend that you add vbObjectError to your own error messages to avoid conflict with system errors.
Error Bubbling
What happens when an unhandled error occurs in a lower-level subroutine? Let's try that question again in English. Suppose that we have two routines created - one to select a worksheet, and one to type something into cell A1:
Sub SelectSheet(SheetName As String)
Worksheets(SheetName).Select
End Sub
Sub SignSheet()
Range("A1").Value = "Wise Owl"
End Sub
Note that neither routine contains any error-handling. Now consider a calling routine which does:
Sub SignSheetOneLastTime()
On Error GoTo NotSelected
SelectSheet "TestSheet"
On Error GoTo NotSigned
SignSheet
MsgBox "Job done!"
Exit Sub
NotSelected:
MsgBox "Can not select this sheet"
Exit Sub
NotSigned:
MsgBox "Can not sign this cell"
Exit Sub
End Sub
When you run the SignSheetOneLastTime macro above, if an error occurs in the SelectSheet macro which is not handled there, it will bubble up into the calling routine.
Thus one approach to error-handling is to incorporate all of your error traps in the top-level calling routine!
Of course, another approach is to write perfect code - but that's where we came in with this tutorial, so it must be time to finish.
