Basic Error Handling

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Effective error-handling code can be quite simple or very sophisticated. It can create an error trap or handle errors in-line. It might display a message to the user or log information about the error to a file. But no matter how an error handler is implemented, the basic components of every error handler are the same. An error handler consists of code that does all of the following:

• Specifies what to do if an error occurs
• Handles the error that has occurred
• Specifies how program execution is to continue

The On Error statement is used to specify the first component. The Resume statement is used to specify the third component. The second component represents the code you write to handle any errors that occur.

The basic format for how an error trap is included in a procedure is as follows (the use of italics indicates the location of placeholders for elements you would specify in a real procedure):

Function ProcedureName(ArgumentList) As DataType
   ' Procedure comments.
   ' Declare local variables and constants.

   On Error GoTo ProcedureName_Err
   ' Procedure code.
   .
   .
   .
   ProcedureName = True (or some other return value)
ProcedureName_End:
   ' Cleanup code that closes open files and sets object variables = Nothing.
   Exit Function
ProcedureName_Err:
   ProcedureName = False
   Select Case Err.Number
      Case AnticipatedError#1
         ' Handle error #1.
      Case AnticipatedError#2
         ' Handle error #2.
      Case UnAnticipatedErrors
         ' Handle unanticipated error.
      Case Else
         ' Handle unforseen circumstances.
   End Select
   Resume ProcedureName_End
End Function

Another common error-handling technique is to attempt an operation that you know will generate a specific error if some condition is not met. In this case you can use in-line error handling to attempt the operation and then test for the presence of the known error. For example, when you are using Automation to access the objects in another Office application, there are times when you will want to use an existing instance of the application. Only if there is no existing instance do you want to create a new instance. The following code fragment shows how to use in-line error handling to create a new instance of Microsoft® Excel only if there is no instance currently open:

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err = ERR_EXCEL_NOTRUNNING Then
   Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo ProcedureName_Err

Note that the On Error Resume Next statement is used to turn on in-line error handling. A test for the anticipated error comes immediately after the code that might cause the error. (In this example, a constant was created that contained the value of the anticipated error.) Finally, the procedure's regular error trap is re-enabled to handle any additional errors.

In some cases, you know an error might occur but you do not intend to handle it at all; instead, you plan to ignore it. For example, if your application tries to delete its custom command bar when it terminates, you will want to ignore errors that occur if the user has already deleted the command bar:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ' Remove the custom command bar created when
   ' this application started. Ignore any error
   ' generated if it has already been deleted.

   On Error Resume Next

   CommandBars("CustomAppCmdbar").Delete
End Sub

See Also

Writing Error-Free Code | Design-Time Tools | Run-Time Tools | Script Debugging Tools | Automating Error Handling | Getting Information About an Error | Error Handling in Class Modules | Handling Script Errors | Logging Errors