Basic Error Handling with an Extra Resume

One of the problems with basic error handling is that when an error does occur, you have no easy way of knowing the exact line that caused the error. After all, your procedure may have dozens or hundreds of lines of code. When you see the error message, the execution of your code has already jumped to your error handler routine and displayed the message box; you may not be able to tell which line caused the problem. Many programmers rerun the code, using debug mode, to step through the code to try to find the offending line.

But there is a much easier way to find that error-producing line of code: Just add a Resume line after the Resume Exit_Procedure.

You're probably thinking, "Why would you add an extra Resume right after another Resume Exit_Procedure? The extra Resume will never run!" You're right. It will never run under normal circumstances. But it will run if you ask it to. If your application encounters an error, you can override the next line that will run. In debug mode, you can just change the next line to be executed to your extra

Resume. The Resume Exit_Procedure statement is skipped entirely. The following code is identical to the basic code shown previously, but with that one extra Resume.

Public Function MyFunction() On Error GoTo Error_Handler

Dim varReturnVal As Variant

'your function code goes here

Exit_Procedure:

Exit Function 'or Exit Sub if this is a Sub

Error_Handler:

MsgBox "An error has occurred in this application. " _

& "Please contact your technical support and tell them this information:" _ & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _ & Err.Description, _

Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure

Resume

End Function

Under normal operation, the extra Resume never runs because the line before it transfers execution of the code elsewhere. It comes into play only when you manually cause it to run. To do this, you can do something that is rarely done in debug mode: move the execution point in the code to a different statement.

Here's how the extra Resume works. Say your code is supposed to open a report, but there's a problem: the report name you specified doesn't exist. Your code might look like this:

Private Sub cmdPreview_Click() On Error GoTo Error_Handler

If Me!lstReport.Column(3) & "" <> "" Then

DoCmd.OpenReport ReportName:=Me!lstReport.Column(3), View:=acViewPreview End If

'Update the Last Run Date of the report DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE tsysReport " _ & "SET tsysReport.DtLastRan = Date() " _ & "WHERE tsysReport.RptKey = " & Me.lstReport DoCmd.SetWarnings True

Exit_Procedure:

On Error Resume Next DoCmd.SetWarnings True Exit Sub

Error_Handler:

MsgBox "An error has occurred in this application. "

& "Please contact your technical support and " _

& "tell them this information:" _

& vbCrLf & vbCrLf & "Error Number " & Err.Number & "

, " &

Err.Description, _

Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure

Resume

End Sub

When you run your code, an error message appears, as shown in Figure 9-3.

When you run your code, an error message appears, as shown in Figure 9-3.

Figure 9-3

Instead of clicking OK as your user would do, press Ctrl+Break on your keyboard. A Visual Basic dialog box appears, as shown in Figure 9-4.

Microsoft Visual Basic

Code execution has been interrLpted

| Continue | End i Debug i | h&p

This extra Resume technique won't work in an Access runtime application because in runtime mode no design modes are allowed, including the VBA code editor. It also won't work in an Access MDE or ACCDE because all VBA source code is removed from those applications.

Now click the Debug button. The code displays in the Code window, as shown in Figure 9-5.

Beginner Macro Resume
Figure 9-5

The Resume Exit_Procedure statement will be indicated by an arrow and highlighted in yellow. This is the statement that will execute next if you continue normally. But instead of letting it run, you take control, using your mouse to drag the yellow arrow down one line to the extra Resume line. By doing this, you indicate that you want the Resume line to run next.

Instead of using the mouse, you can click or arrow down to the Resume line, and then use Debug..Set Next statement (Ctrl+F9 on your keyboard). As usual in Access, there are several ways to do the same thing.

Now, the yellow arrow will be pointed at the Resume statement, as shown in Figure 9-6.

Now, you want the Resume statement to run, in order to retry the statement that caused the error. Press F8 to run the next line of code (your Resume) and stop. Or, you can choose Debug..Step Into from the menu.

The exact line that caused the error will now be indicated by an arrow and highlighted in yellow, as shown in Figure 9-7. That was easy, wasn't it?

] Microsoft Visual Basic - Chamber Application [break] - [Forni_frmMenu_Reports (Code)]

Project - Chamber Application il LI -

File Edit View Insert Debug Run Took Add-Ins Window Help

I 4t - H I £ 4s ifi ffl I "J Pi ► JJ a I %> S "fir * • L»62.Coii

Type a question for help

Form_frmMenu_Reports

EH Form_frmMenu_Splash E3 FormJrmMenuJJserTips Hl Form_frmReportSelector_r = m F 0 rm _fr m Wor kspa ce EH Form_fsubBusiness_Paymf S Report_rptAll_ByName 9 Report_rptAIILabels_ByZip É-& Modules

Properties

"1

J

Alphabetic j Categorized |

End If

"Update the Last Run Date of the report DoCntd.Hourglass True DoCnid. SetWarnings False

DoCrad.RunSQL "UPDATE nsysReport SET t3ysReport.DtLastRan = Date()

"WHERE tsysReport.RptKey = " & Me.lstReport DoCmd.SetWarnings True DoCmd.Hourglass False

Exit_Procedure:

On Error Resume Next DoCmd.SetWarnings True DoCrad.Hourglass False Exit Sub

Error Handler:

If Err.Number = 2501 Then

Resume Exit_Procedure Else

MsgBox "An error has occurred in this application. " _ £ "Please contact your technical support and tell " _ & "them this information:" _

& vbCrLf & vbCrLf & "Error Number " & Err.Number & " _ & Err.Description, _

Buttons:=vbCritical, title:="My Application" Resume Exit_Procedure Resume End If

Figure 9-6

Figure 9-7

Now, admittedly, this is a simple example. You probably could have determined which line caused the error just by looking at the error description. However, when your procedures contain pages of code, often with coding loops, complex logic, and similar statements, this extra Resume technique comes in handy. It can save you many hours of time while you are debugging your VBA code.

The extra Resume doesn't cause any harm in your code, so you can leave it in every procedure even when you deliver your application. Also, if a technically savvy client encounters an unexpected error and she's running an MDB or ACCDB (not an MDE or ACCDE), you can walk the client through this process to help determine what caused the problem in the client's environment. As you know, what works on your PC doesn't always work when your user is running it.

Was this article helpful?

+1 0

Post a comment