Learn Access 2003 VBA with The Smart Method

104

www.LearnAccessVBA.com

Lesson 5-8: Implement error

handling

The code that has been written so far has no error handling. This means

that when something goes wrong the program “crashes" causing the

default error handler to display.

Professional code always includes custom error handling in every sub

and function (see sidebar) so that you are in control when something

goes wrong. We’re now going to add error handling to bring the code to

a level that a professional programmer would be proud of.

1

If it is not already open, open the VBACode.mdb

spreadsheet and open frmTest in Design View.

2

Enter some text into one of the text boxes instead of a

number and then click the Add Two Numbers command

button.

A run-time error dialog appears.

The error appears because Error Handling has not been

implemented resulting in the program code crashing.

3

Click the End button, Open the VBA Editor and navigate to

the cmdAddTwoNumbers command button’s Click event

handler.

4

Add error handling code to the cmdAddTwoNumbers

command button’s Click event handler.

Modify the code as follows (new error handling code is shown

boldfaced):

Private Sub cmdAddTwoNumbers_Click()

On Error GoTo ErrorHandler

Dim dblFirstNumber As Double

Dim dblSecondNumber As Double

Dim dblSumOfNumbers As Double

mlngCounter = mlngCounter + 1

(Central section of code not shown)

Me.txtResult.Value = dblSumOfNumbers

CleanUpAndExit:

Exit Sub

tip

Always use the labels

ErrorHandler: and

CleanUpAndExit: in every

function and sub routine.

There’s no reason to give a

custom name to every label

even though you’ll see later

that wizard-generated code

does this.

Using the same error handler

labels keeps your code

consistent and improves

productivity as you can cut and

paste error handling code from

one sub to another.

Session5e

note

There’s nothing more

unprofessional (from a client’s

perspective) than an

application that frequently

crashes.

One of our most important

quality standards (stated in

Appendix A – The Rules) states

that:

“Error Handling must be

implemented in every sub and

function without exception".

Programmers often argue that

some code is so simple that it

can never fail so does not need

error handling. While this may

be true in some cases there’s

nothing wrong with a catch-all

approach. If absolutely every

sub has error handling code

you cannot possibly confront

your user with a confidence-

sapping runtime error.

When you take this approach it

is comforting to find that those

bullet-proof subs that couldn’t

possible fail often do, but when

they do the error is always

elegantly handled.

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

105

ErrorHandler:

Call MsgBox("Please ensure that input values are

numeric")

Resume CleanUpAndExit

End Sub

Let’s discuss the error handling code.

On Error GoTo ErrorHandler

The very first line of the code tells VBA that if an error occurs it

must execute the code that begins after the label

ErrorHandler:

CleanUpAndExit:

Exit Sub

The

CleanUpAndExit

label identifies the beginning of code that

must execute before exiting the sub. In a future session we’ll

discuss the concept of clean up code. Even though no clean up

code is required in this simple sub, having a single exit point in

absolutely every sub is a great habit to get into. Multiple exit

points are a major cause of bugs in novice code.

The

Exit Sub

statement usually immediately precedes the

ErrorHandler:

label and is the single exit point of the sub. Note

that after the error message is displayed code execution continues

at the

CleanUpAndExit:

label (branched to with the

Resume

CleanUpAndExit

statement) to observe the single exit rule.

5

Test your code.

Switch to Form View. Enter some invalid (non numeric) text into

one of the boxes and then click the Add Two Numbers command

button. This time the error is gracefully handled and a user-

friendly message box is displayed.

6

Improve the error handling code by giving the user more

information.

In this simple function there’s very little that can go wrong. If

there’s an error we’re pretty certain that the user has entered

invalid values. Many functions aren’t quite as simple and have

potential for many different errors.

To provide better feedback we use the error object (Err). The Error

object has Number and Description properties containing more

information about the last encountered error.

Modify the error handler code as follows:

ErrorHandler:

Call MsgBox("Please ensure that input values are

numeric" & vbCrLf & _

vbCrLf & _

"Error Description: " & Err.Description & vbCrLf & _

"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit

7

Test your code.

This time an even better error dialog appears:

note

vbCrLf is one of VBA’s built-in

constants that contains a

concatenated carriage return

and line feed character to move

to the next line.

note

The logic behind using a

Resume statement to exit an

error handler is that a block of

cleanup code can be inserted in

one place within the sub. In

later lessons we’ll explore the

concept of cleanup code in more

detail.

A very old and very wise

programming standard insists

that there should be only one

exit from a sub and this rule is

included in Appendix A – The

Rules.

In this simple sub, and in all

code encountered so far, no

cleanup code is required.

Later when we work with

object variables (that must be

destroyed at the end of the sub)

you will see the logic in putting

cleanup code in one place.