XL2000: How to Use "On Error" to Handle Errors in a Macro

ID: Q213637

The information in this article applies to:

Microsoft Excel 2000

SUMMARY

In Microsoft Excel, you can use the On Error statement in a Microsoft Visual Basic for Applications macro (Sub procedure) to trap errors and direct procedure flow to the error-handling statements in a procedure. By using error-handling, you make your macros and your application easier to use by intercepting run-time errors before the user sees them.

This article describes how to use error-handling in a macro and provides several examples of error trapping macro code.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The following is an example of the basic structure of a macro that uses error-handling:


   Sub MyMacro()

        On Error GoTo ErrorHandler
        . . .
        Exit Sub

   ErrorHandler:
        . . .
        Resume <or Exit Sub>
        . . .

   End Sub

The example contains the following elements: an On Error statement (On Error GoTo ErrorHandler) and a Resume statement. The error handler might contain an Error statement and/or an Error function. Each of these elements is discussed in greater detail in later sections of this article.

Note that in this example an Exit Sub statement precedes the error handler label. By placing Exit Sub or Exit Function before the error handling routine, you prevent the error-handling code from being run when the macro runs without error.

On Error Statement

The On Error statement enables your application to handle errors that your macro encounters. If you do not use an On Error statement in your procedures, any run-time error that occurs is fatal: that is, Microsoft Excel will generate a run-time error message and the macro will stop running.

The following list outlines the On Error statement syntax and describes each type of statement.


   On Error GoTo <line>

Enables the error-handling routine that starts at <line>, which is any line label or line number. The specified line must be in the same procedure as the On Error statement.


   On Error Resume Next

Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred. In other words, execution continues.


   On Error GoTo 0

Disables any enabled error handler in the current procedure.

Resume Statement

Your error-handling routine will need to determine where macro control should go when an error has occurred. To end the macro when an error has occurred, place the error-handling routine either immediately before the End Sub (or End Function) statement or use the Exit Sub (or Exit Function) statement. To return control to another location within the macro, use the Resume statement.

The following list outlines the Resume statement syntax and describes each type of statement.

   Resume [0]

Resumes macro execution with the line that caused the error. [0] is an optional argument.


   Resume Next

Resumes macro execution with the line following the line that caused the error.


   Resume <line>

Resumes macro execution at the line number or line label specified by <line>.

NOTE: A very common mistake in writing error-handling routines is to use a GoTo statement in the routine to specify where macro control should go. Generally, it is not recommended that you use GoTo in an error-handling routine; you should use the Resume statement to resume macro execution outside of the error handler.

Error Statement and Error Function

You can make your error-handling routine more effective by determining what error has occurred. When a run-time error occurs, you can use the Err function to determine the error number; the Error statement is used to get the text that describes the error. For example, you can use the following statement to display the description of the error that was encountered:


   MsgBox Err & ": " & Error(Err)

If the error handling routine encountered the error 13 (a type mismatch error), the following text would appear in a dialog box:

13: Type Mismatch

Examples of Macros That Use Error Handling

In these examples, the error handler may be called if there is no disk in drive B, if the B:\XLFiles path cannot be located, or if there is no Book1.xls file in the B:\XLFiles folder (directory).

Example 1--Basic Error Handling Macro


Sub MyMacro()

      Dim MyWorkbook As Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      ' If an error occurs, display a message and end the macro.
      MsgBox "An error has occurred. The macro will end."

   End Sub

This example uses the On Error statement to display a message and end a macro when an error occurs. If an error occurs in the macro, the error handler displays the following error message and the macro execution is halted:

An error has occurred. The macro will end.

If the workbook Book1.xls is successfully opened, a message is displayed, showing the destination workbook, and the macro ends because there is an Exit Sub statement before the error handler label "ErrHandler."

Example 2--Error Handling Macro That Displays a Specific Error Message

This next example is similar to the macro in Example 1; however, this macro implements the Error statement and the Err function to show a more descriptive error message when an error is encountered.


   Sub MyMacro()

      Dim MyWorkbook As Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.

      On Error GoTo Errhandler
      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      Select Case Err

         Case 68, 75:    ' Error 68: "Device not available"
                         ' Error 75: "Path/File Access Error"
            MsgBox "There is an error reading drive B."

         Case 76:        ' Error 76: "Path not found"
            MsgBox "The specified path is not found."

         Case Else:      ' An error other than 68, 75 or 76 has occurred.
            ' Display the error number and the error text.
            MsgBox "Error # " & Err & " : " & Error(Err)

      End Select

      ' End the macro.
   End Sub

If an error occurs in the macro, one of the following will occur:

If the error is either 68 or 75, the message "There is an error reading drive B" will be displayed, and the macro will end.

If the error is 76, the message "The specified path is not found" will be displayed, and the macro will end.

If the error is an error other than 68, 75, or 76, the message "Error <error number>: <error text>" will be displayed and the macro will end.

If the Book1.xls workbook is successfully opened, then a message will be displayed showing the destination workbook and the macro will end because there is an Exit Sub statement before the error handler label "ErrHandler."

Example 3--Macro that Uses the Resume Statement

This next example uses the Resume statement to resume macro execution based on choices that the user makes when an error occurs.


   Sub MyMacro()

   Dim Result as Integer
   Dim ErrMsg as String
   Dim MyWorkbook as Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLfiles"

      Workbooks.Open "Book1.xls"

   NewWorkbook:

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      Select Case Err

         Case 68, 75:  ' Error 68: "Device not available"
                       ' Error 75: "Path/File access error
            ErrMsg =  "There is an error reading drive B. Please " & _
               "insert a disk and then press OK to continue or " & _
               "press Cancel to end this operation."

            Result = MsgBox(ErrMsg, vbOKCancel)

            ' Resume at the line where the error occurred if the user
            ' clicks OK; otherwise end the macro.
            If Result = vbOK Then Resume

         Case 76:     ' Error 76: Path not found
            ErrMsg = "The disk in drive B does not have an XLFiles " & _
               "directory. Please insert the correct disk."

            Result = MsgBox(ErrMsg, vbOKCancel)

            ' Resume at the line where the error occurred if the user
            ' clicks OK; otherwise end the macro.
            If Result = vbOK Then Resume

         Case Else:   ' A different error occurred.

            ErrMsg = "An error has occurred opening " & _
                 "B:\XLFiles\Book1.xls. Use the active workbook as " & _
                 "the destination?"

            Result = MsgBox(ErrMsg, vbYesNo)

            ' Resume at the label "NewWorkbook" if the user clicks Yes;
            ' otherwise end the macro.
            If Result = vbYes Then Resume NewWorkbook

      End Select

   ' End the macro.
   End Sub

If the workbook Book1.xls is successfully opened, a message will be displayed showing the destination workbook as Book1.xls and the macro will end because there is an Exit Sub statement before the error handler label "ErrHandler." If an error occurs in the macro, the error handler will do one of the following:

If the error is either 68 or 75, then the following message is displayed:

There is an error reading drive B. Please insert a disk and then press OK to continue or press Cancel to end this operation.

If the user clicks OK in this dialog box, the macro resumes at the line where the error occurred. If the user clicks Cancel , the macro will end.

If the error is 76, then the following message will be displayed

The disk in drive B does not have an XLFiles directory. Please insert the correct disk.

If the user clicks OK in this dialog box, the macro resumes at the line where the error occurred. If the user clicks Cancel, the macro will end.

If the error is an error other than 68, 75 or 76, then the following error message is displayed

An error has occurred opening B:\XLFiles\Book1.xls. Use the active workbook as the destination?

If the user clicks Yes in the dialog box, the macro resumes at the line labeled "NewWorkbook." The currently active workbook is displayed as the destination workbook. If the user clicks No, the macro will end.

Example 4--Centralizing Error Handling

You can reduce the length of overall code in your application by centralizing the error handling. You can centralize error-handling by creating one or more procedures that handle common errors.

The following is a procedure called ErrorHandling that will display a message corresponding to the error number (ErrorValue) that was passed to it and, where possible, allow the user to choose a button to specify which action should be taken following the error. Based on the choice that the user makes, the ErrorHandling procedure will return a value (ReturnValue) for the course of action to the calling procedure. The ReturnValue can be Err_Exit (exit the macro where the error occurred), Err_Resume (resume at the line in the macro where the error occurred), or Err_Resume_Next (resume at the line following the line in the macro where the error occurred).


   Public Const Err_Exit = 0
   Public Const Err_Resume = 1
   Public Const Err_Resume_Next = 2

   Sub ErrorHandling(ErrorValue As Integer, ReturnValue As Integer)

      Dim Result as Integer
      Dim ErrMsg as String
      Dim Choices as Integer

      Select Case ErrorValue

         Case 68:     ' Device  not available.

            ErrMsg = "The device you are trying to access is either " & _
               "not online or does not exist. Retry?"
            Choices = vbOKCancel

         Case 75:     ' Path/File access error.

            ErrMsg = "There is an error accessing the path and/or " & _
                 "file specified. Retry?"
            Choices = vbOKCancel

         Case 76:     ' Path not found.

            ErrMsg = "The path and/or file specified was not found. Retry?"
            Choices = vbOKCancel

         Case Else:   'An error other than 68, 75 or 76 has occurred

            ErrMsg = "An unrecognized error has occurred ( " & _
               Error(Err) & " ). The macro will end."
            MsgBox ErrMsg, vbOKOnly
            ReturnValue = Err_Exit
            Exit Sub

      End Select

      ' Display the error message.
      Result = MsgBox(ErrMsg, Choices)

      ' Determine the ReturnValue based on the user's choice from MsgBox.
      If Result = vbOK Then
         ReturnValue = Err_Resume
      Else
         ReturnValue = Err_Exit
      End If

   End Sub

This next macro demonstrates how you could use the ErrorHandling procedure when an error is encountered:


   Sub MyMacro()

      Dim Action As Integer

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      ' Run the ErrorHandling macro to display the error and to
      ' return a value for Action which will determine the appropriate
      ' action to take (Resume the macro or end the macro)

      ErrorHandling Err, Action

      If Action = Err_Exit Then
         Exit Sub
      ElseIf Action = Err_Resume Then
         Resume
      Else
         Resume Next
      End If

   End Sub

REFERENCES

For more information about handling errors in a macro, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type On Error Statement in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about getting help with Visual Basic for Applications, click the article number below to view the article in the Microsoft Knowledge Base:

Q226118 OFF 2000: Programming Resources for Visual Basic for Applications

Additional query words: XL2000

Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto