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 Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners -
https://partner.microsoft.com/global/30000104Microsoft Advisory Services -
http://support.microsoft.com/gp/advisoryserviceFor more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
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.
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.
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.
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.
Resumes macro execution with the line that caused the error. [0] is
an optional argument.
Resumes macro execution with the line following the line that caused
the error.
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:
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 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 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?
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