This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Handling Errors in Visual Basic for Applications Customising Error-Handling Code (this blog) A Worked Example - Error-Handling with InputBox 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.

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.

Handling Errors in Visual Basic for Applications
Customising Error-Handling Code (this blog)
A Worked Example - Error-Handling with InputBox
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

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:

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:

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.

Parts of this blog
Handling Errors in Visual Basic for Applications Customising Error-Handling Code (this blog) A Worked Example - Error-Handling with InputBox Advanced Error Traps - Raising Errors and Error Bubbling

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Customising Error-Handling Code

Using Err.Number and Err.Description

Created a Standard Error-Handling Function

Other Options for What to do in an Error-Handling Routine

Head office

London

Manchester