Error-handling and error trapping in Excel Visual Basic macros
Part two of a four-part series of blogs

What happens when your macros go wrong? That depends what error-handling you have in place. Learn how to use ON ERROR and other commands to trap errors in a structured way.

  1. Handling Errors in Visual Basic for Applications
  2. Customising Error-Handling Code (this blog)
  3. A Worked Example - Error-Handling with InputBox
  4. Advanced Error Traps - Raising Errors and Error Bubbling

This is one small part of our free online Excel VBA tutorial.  To find out how to learn in a more structured way, have a look at our training courses in VBA.

Posted by Andy Brown on 29 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Customising Error-Handling Code

To understand how to customise error-handling messages, you first need to know about the Err object!

Using Err.Number and Err.Description

In the example in the previous part of this blog series, without error-trapping you would see this message when you try to go to a worksheet which doesn't exist:

Error message example

In this case:

 

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

'create variable for 2 blank lines

DoubleLineBreak = vbNewLine & vbNewLine

'if an error happens, jump out of the routine

On Error GoTo NoWorksheet

'now try going to worksheet (may trigger error)

Worksheets("TestSheet").Select

'reset error handling to the default - any

'error from now on is genuine!

On Error GoTo 0

'if we get here, the worksheet did exist -

'so sign it and leave

Range("A1").Value = "Wise Owl"

Exit Sub

'it doesn't matter how indented the code is,

'a label will always appear on the left edge

NoWorksheet:

'store the error number in an integer variable

'(just in case we lose it when ANOTHER error

'happens, this one unforeseen!)

ErrorNumber = Err.Number

'find out what the error message was

Select Case ErrorNumber

Case 9

'this is the subscript out of range message

MsgBox "No such worksheet!" & DoubleLineBreak & _

"The internal error message for this is: " & _

DoubleLineBreak & _

UCase(Err.Description), _

vbOKOnly + vbExclamation, "Wise Owl error"

Case Else

'no other message should happen

MsgBox "AAAAARH! Error number " & Err.Number & _

" has happened!"

End Select

'if we get here, the worksheet didn't exist -

'display error message

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):

'this is the subscript out of range message

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:

Custom error message

The error message includes the built-in description, converted to upper case.

 

Created 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")

'display error message

MsgBox _

prompt:=ErrorMessage, _

Buttons:=vbOKOnly + vbExclamation, _

Title:=ErrorTitle

'I often take the macro into break mode, then

'use the call stack to find where I was in the

'calling routine

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()

'if an error happens, go to error-handling routine

On Error GoTo NoWorksheet

'now try going to worksheet (may trigger error)

Worksheets("TestSheet").Select

'reset error handling to the default - any

'error from now on is genuine!

On Error GoTo 0

'if we get here, the worksheet did exist -

'so sign it and leave

Range("A1").Value = "Wise Owl"

Exit Sub

NoWorksheet:

'call standard error-handling routine

ShowError "No such worksheet as TESTSHEET"

End Sub

Although it's not for the purist (none of this blog 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"

'if an error happens, solve it and try again

On Error GoTo NoWorksheet

'now try going to worksheet (may trigger error)

Worksheets(SheetName).Select

'must have solved the error - sign worksheet

Range("A1").Value = "Wise Owl"

'time to leave routine and jump over error code

Exit Sub

NoWorksheet:

'if no such worksheet, silently create it

Worksheets.Add

ActiveSheet.Name = SheetName

'now go back and try to sign it again

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.

Having looked at the syntax of error-handling code, it's time now to consider a worked example.

This blog has 0 threads Add post