Run Time Error Handling

When you are designing an application, you should try to anticipate any problems that could occur when the application is used in the real world. You can remove all the bugs in your code and have flawless logic that works with all permutations of conditions, but a simple operational problem could still bring your code crashing down with a less than helpful message displayed to the user.

For example, if you try to save a workbook file to the floppy disk in the A: drive, and there is no disk in the A: drive, your code will grind to a halt and display a message that will probably not mean anything to the average user.

If you anticipate this particular problem, you can set up your code to gracefully deal with the situation. VBA allows you to trap error conditions using the following statement:

On Error GoTo LineLabel

LineLabel is a marker that you insert at the end of your normal code, as shown in the following code with the line label errTrap. Note that a colon follows the line label. The line label marks the start of your error recovery code and should be preceded by an Exit statement to prevent execution of the error recovery code when no error occurs:

Sub ErrorTrap1()

Dim lAnswer As Long, sMyFile As String

Dim sMessage As String, sCurrentPath As String

On Error GoTo errTrap

sCurrentPath = CurDir$

ChDrive "A"

ChDrive sCurrentPath

ChDir sCurrentPath

sMyFile = "A:\Data.xls


Application.DisplayAlerts = False




ChDrive sCurrentPath

ChDir sCurrentPath

Exit Sub


sMessage = "Error No:

= " & Err.Number & vbCr

sMessage = sMessage &

Err.Description & vbCr & vbCr

sMessage = sMessage &

"Please place a disk in the A: drive" & vbCr

sMessage = sMessage &

"and press OK" & vbCr & vbCr

sMessage = sMessage &

"Or press Cancel to abort File Save"

lAnswer = MsgBox(sMessage, vbQuestion + vbOKCancel, "Error")

If lAnswer = vbCancel

Then Resume TidyUp


End Sub

Once the On Error statement is executed, error trapping is enabled. If an error occurs, no message is displayed and the code following the line label is executed. You can use the Err object to obtain information about the error. The Number property of the Err object returns the error number, and the Description property returns the error message associated with the error. You can use Err.Number to determine the error when it is possible that any of a number of errors could occur. You can incorporate Err.Description into your own error message, if appropriate.

In Excel 5 and 95, Err was not an object, but a function that returned the error number. Because Number is the default property of the Err object, using Err by itself is equivalent to using Err.Number, and the code from the older versions of Excel still works in Excel 97 and later versions.

The code in ErrorTrapl, after executing the On Error statement, saves the current directory drive and path into the variable sCurrentPath. It then executes the ChDrive statement to try to activate the A: drive. If there is no disk in the A: drive, error 68 — (Device unavailable) occurs and the error recovery code executes. For illustration purposes, the error number and description are displayed and the user is given the opportunity to either place a disk in the A: drive and continue, or abort the save.

If the user wishes to stop, you branch back to TidyUp and restore the original drive and directory settings. Otherwise the Resume statement is executed. This means that execution returns to the statement that caused the error. If there is still no disk in the A: drive, the error recovery code is executed again. Otherwise the code continues normally.

The only reason for the ChDrive "A" statement is to test the readiness of the A: drive, so the code restores the stored drive and directory path. The code sets the DisplayAlerts property of the Application object to False, before saving the active workbook. This prevents a warning if an old file called Data.xls is being replaced by the new Data.xls. (See Chapter 3 for more on DisplayAlerts.)

The Resume statement comes in three forms:

□ Resume causes execution of the statement that caused the error.

□ Resume Next returns execution to the statement following the statement that caused the error, so the problem statement is skipped.

□ Resume LineLabel jumps back to any designated line label in the code, so you can decide to resume where you want.

The following code uses Resume Next to skip the Kill statement, if necessary. The charmingly named Kill statement removes a file from disk. The following code removes any file with the same name as the one you are about to save, so there will be no need to answer the warning message about overwriting the existing file.

The problem is that Kill will cause a fatal error if the file does not exist. If Kill does cause a problem, the error recovery code executes and you use Resume Next to skip Kill and continue with SaveAs. The MsgBox is there for educational purposes only. You would not normally include it:

Sub ErrorTrap2()

Dim sMyFile As String, sMessage As String Dim sAnswer As String

On Error GoTo errTrap

Workbooks.Add sMyFile = "C:\Data.xls" Kill sMyFile

ActiveWorkbook.SaveAs Filename:=sMyFile


Exit Sub



= "Error No: = " & Err.Number & vbCr


= sMessage & Err.Description & vbCr & vbCr


= sMessage & "File does not exist"

sAnswer =

MsgBox(sMessage, vblnformation, "Error")

Resume Next

End Sub

As an alternative to On Error GoTo, you can use: On Error Resume Next

This statement causes errors to be ignored, so it should be used with caution. However, it has many uses. The following code is a rework of ErrorTrap2:

Sub ErrorTrap3()

Dim sMyFile As String

Workbooks.Add sMyFile = "C:\Data.xls" On Error Resume Next Kill sMyFile On Error GoTo 0

ActiveWorkbook.SaveAs Filename:=sMyFile ActiveWorkbook.Close End Sub

Use On Error Resume Next just before the Kill statement. If C:\Data.xls does not exist, the error caused by Kill is ignored and execution continues on the next line. After all, you don't care if the file does not exist. That's the situation you are trying to achieve.

On Error GoTo 0 is used to turn on normal VBA error-handling again. Otherwise, any further errors would be ignored. It is best not to try to interpret this statement, which appears to be directing error-handling to line 0. Just accept that it works.

You can use On Error Resume Next to write code that would otherwise be less efficient. The following sub procedure determines whether a name exists in the active workbook:

Sub TestForName()

If bNameExists("SalesData") Then

MsgBox "Name Exists"


MsgBox "Name does not exist"

End If

End Sub

Function bNameExists(sMyName As String) As Boolean


Dim sName As String

On Error Resume


sName = Names(sMyName).RefersTo

If Err.Number <> 0 Then

bNameExists =




bNameExists =


End If

End Function

TestForName calls the bNameExists function, which uses On Error Resume Next to prevent a fatal error when it tries to assign the name's RefersTo property to a variable. There is no need for On Error GoTo 0 here, because error-handling in a procedure is disabled when a procedure exits, although Err.Number is not cleared.

If no error occurred, the Number property of the Err object is 0. If Err.Number has a non-0 value, an error occurred, presumably because the name did not exist, so bNameExists is assigned a value of False and the error is cleared. The alternative to this single pass procedure is to loop through all the names in the workbook, looking for a match. If there are lots of names, this can be a slow process.

0 0

Post a comment