XL: Error Trapping with Visual Basic for Applications
ID: Q146864
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
-
Microsoft Excel 98 Macintosh Edition
-
Microsoft Excel 97 for Windows
SUMMARY
When a run-time error occurs in a Microsoft Visual Basic for Applications
macro, an error message appears on the screen, and the macro either halts
or behaves unpredictably.
To prevent the application from crashing or behaving unpredictably, you can
include macro code that intercepts the error and tells the macro how to
handle it. The process of intercepting and handling a run-time error is
called "error trapping." The set of instructions that tells the application
how to handle the error is called the "error-handling routine" or "error
handler."
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
the Microsoft fee-based consulting line at (800) 936-5200. 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
While Visual Basic code is running, you may encounter several types of
errors that can be trapped. You can take advantage of error trapping in
Microsoft Excel by using the following functions and statements.
On Error Statement
The On Error statement causes Visual Basic for Applications to start or
stop error trapping. The On Error statement also specifies a set of
statements to execute if an error is encountered.
For additional information, please see the following articles in the
Microsoft Knowledge Base:
Q141571
How to Use "On Error" to Handle Errors in a Macro
Err Function
The Err function returns the number of the error encountered.
Example Using the Err Function:
Msgbox "The most recent error number is " & Err & _
". Its message text is: " & Error(Err)
The following table contains a list of the trappable error codes you may
encounter when you use the Err function.
Error code Error message
---------- -------------
3 Return without GoSub
5 Invalid procedure call
6 Overflow
7 Out of memory
9 Subscript out of range
10 Duplicate definition (versions 5.0 and 7.0)
10 This array is fixed or temporarily locked (version97)
11 Division by zero
13 Type mismatch
14 Out of string space
16 String formula too complex (versions 5.0 and 7.0)
16 Expression too complex (version 97)
17 Can't perform requested operation
18 User interrupt occurred
20 Resume without error
28 Out of stack space
35 Sub or function not defined (versions 5.0 and 7.0)
35 Sub, function, or property not defined (version 97)
47 Too many DLL application clients (version 97)
48 Error in loading DLL
49 Bad DLL calling convention
51 Internal error
52 Bad file name or number
53 File not found
54 Bad file mode
55 File already open
57 Device I/O error
58 File already exists
59 Bad record length
61 Disk full
62 Input past end of line
63 Bad record number
67 Too many files
68 Device unavailable
70 Permission denied
71 Disk not ready
74 Can't rename with different drive
75 Path/File access error
76 Path not found
91 Object variable not set (versions 5.0 and 7.0)
91 Object variable or With block variable not set
(version 97)
92 For Loop not initialized
93 Invalid pattern string
94 Invalid use of Null
95 User-defined error (versions 5.0 and 7.0 only)
298 System DLL could not be loaded (version 97)
320 Can't use character device names in specified file names
(version 97)
321 Invalid file format (version 97)
322 Can't create necessary temporary file (version 97)
323 Can't load module; invalid format (versions 5.0 and 7.0)
325 Invalid format in resource file (version 97)
327 Data value named was not found (version 97)
328 Illegal parameter; can't write arrays (version 97)
335 Could not access system registry (version 97)
336 ActiveX component not correctly registered (version 97)
337 ActiveX component not found (version 97)
338 ActiveX component did not correctly run (version 97)
360 Object already loaded (version 97)
361 Can't load or unload this object (version 97)
363 Specified ActiveX control not found (version 97)
364 Object was unloaded (version 97)
365 Unable to unload within this context (version 97)
368 The specified file is out of date. This program requires
a newer version (version 97)
371 The specified object can't be used as an owner form for
Show (version 97)
380 Invalid property value (version 97)
381 Invalid property-array index (version 97)
382 Property Set can't be executed at run time (version 97)
383 Property Set can't be used with a read-only property
(version 97)
385 Need property-array index (version 97)
387 Property Set not permitted (version 97)
393 Property Get can't be executed at run time (version 97)
394 Property Get can't be executed on write-only property
(version 97)
400 Form already displayed; can't show modally (version 97)
402 Code must close topmost modal form first (version 97)
419 Permission to use object denied (version 97)
422 Property not found (version 97)
423 Property or method not found
424 Object required
425 Invalid object use (version 97)
429 ActiveX component can't create object or return
reference to this object (version 97)
430 Class doesn't support OLE Automation
430 Class doesn't support Automation (version 97)
432 File name or class name not found during Automation
operation (version 97)
438 Object doesn't support this property or method
440 OLE Automation error
440 Automation error (version 97)
442 Connection to type library or object library for remote
process has been lost (version 97)
443 Automation object doesn't have a default value
(version 97)
445 Object doesn't support this action
446 Object doesn't support named arguments
447 Object doesn't support current locale settings
448 Named argument not found
449 Argument not optional
449 Argument not optional or invalid property assignment
(version 97)
450 Wrong number of arguments
450 Wrong number of arguments or invalid property assignment
(version 97)
451 Object not a collection
452 Invalid ordinal
453 Specified DLL function not found
454 Code resource not found
455 Code resource lock error
457 This key is already associated with an element of this
collection (version 97)
458 Variable uses a type not supported in Visual Basic
(version 97)
459 This component doesn't support events (version 97)
460 Invalid clipboard format (version 97)
461 Specified format doesn't match format of data
(version 97)
480 Can't create AutoRedraw image (version 97)
481 Invalid picture (version 97)
482 Printer error (version 97)
483 Printer driver does not support specified property
(version 97)
484 Problem getting printer information from the system.
Make sure the printer is set up correctly (version 97)
485 Invalid picture type (version 97)
486 Can't print form image to this type of printer
(version 97)
735 Can't save file to Temp directory (version 97)
744 Search text not found (version 97)
746 Replacements too long (version 97)
1000 Classname does not have propertyname property
(versions 5.0 and 7.0)
1001 Classname does not have methodname method
(versions 5.0 and 7.0)
1002 Missing required argument argumentname
(versions 5.0 and 7.0)
1003 Invalid number of arguments (versions 5.0 and 7.0)
1004 Methodname method of classname class failed
(versions 5.0 and 7.0)
1005 Unable to set the propertyname property of the classname
class (versions 5.0 and 7.0)
1006 Unable to get the propertyname property of the classname
class (versions 5.0 and 7.0)
31001 Out of memory (version 97)
31004 No object (version 97)
31018 Class is not set (version 97)
31027 Unable to activate object (version 97)
31032 Unable to create embedded object (version 97)
31036 Error saving to file (version 97)
31037 Error loading from file (version 97)
For additional information, please see the following article in the
Microsoft Knowledge Base:
Q142138
An Explanation of Trappable Errors in Visual Basic
for Apps
Error Function
The Error Function returns the error message that corresponds to a given
error number.
Example Using the Error Function:
Msgbox "The message text of the error is: " & Error(Err)
Error Statement
The Error statement simulates the occurrence of an error by allowing you
to assign a custom error number to the Err function. These user- defined
error values are values that you define for your procedures and that are
always stored in variables of the Variant data type. A common use of this
type of error value is in procedures that accept several arguments and
return a value. For example, suppose the return value is valid only if
the arguments fall within a certain range. Your procedure can test the
arguments that the user provides, and if the arguments aren't in the
acceptable range, you can have the procedure return the appropriate error
value.
Error is a subtype of the Variant data type and when the term "error
value" is used, it usually means that a variable is of the Variant type,
and that it contains a value that Visual Basic for Applications
recognizes as a user-defined error. Error values are used in a procedure
to indicate that error conditions have occurred. Unlike normal run-time
errors, these errors do not interrupt your code because they are
recognized as ordinary variables and not errors. Your procedures can test
for these error values and take the appropriate corrective actions.
You can also use the Error statement to simulate run-time errors. This is
especially useful when you are testing your applications, or when you
want to treat a particular condition as being equivalent to a run- time
error. Any Visual Basic for Applications run-time error can be simulated
by supplying the error code for the error in an Error statement. You can
also use the Error statement to create your own user-defined errors by
supplying an error code that does not correspond to a Visual Basic for
Applications run-time error. The table containing a list of built-in
errors appears earlier in this article (under the "Err Function"
section). At this time, Visual Basic for Applications does not use all of
the available numbers for built-in errors. In future releases of Visual
Basic for Applications, the internal numbers will increase as more built-
in errors are added. It is recommended that you start your error numbers
at 50,000 and work your way up to 65,535 to avoid possible conflicts in
the future.
Example Using Error Statement to Simulate Run-time Errors:
Sub Test()
On Error Resume Next
Error 50000 'set the value of Err to 50000
If Err = 50000 Then
MsgBox "my own error occurred"
End If
End Sub
When the Test macro is run, you will get a Message Box with "my own error
occurred" as the message.
CVErr Function
The CVErr function is used to create error values. The CVErr function
takes an argument that must either be an integer or be a variable that
contains an integer.
NoRadius = CVErr(2010)
NotANumber = 2020
InvalidArgument = CVErr(NotANumber)
Example Using the CVErr Function:
Public NoRadius, NotANumber
Sub AreaOfCircle()
Const PI = 3.142
NoRadius = CVErr(2010)
NotANumber = CVErr(2020)
Radius = CheckData(InputBox("Enter the radius: "))
If IsError(Radius) Then
Select Case Radius
Case NoRadius
MsgBox "Error: No radius given."
Case NotANumber
MsgBox "Error: Radius is not a number."
Case Else
MsgBox "Unknown Error."
End Select
Else
MsgBox "The area of the circle is " & (PI * Radius ^ 2)
End If
End Sub
Function CheckData(TheRadius)
If Not IsNumeric(TheRadius) Then
CheckData = NotANumber
ElseIf TheRadius = 0 Then
CheckData = NoRadius
Else
CheckData = TheRadius
End If
End Function
Using Built-In Error Values
There are seven built-in error values in Microsoft Excel. The table below
shows the error number (constant), the literal error value, and the
converted error value.
Error number (Constant) Literal error value Converted error value
-----------------------------------------------------------------------
xlErrDiv0 [#DIV/0!] CVErr(xlErrDiv0)
xlErrNA [#N/A] CVErr(xlErrNA)
xlErrName [#NAME?] CVErr(xlErrName)
xlErrNull [#NULL!] CVErr(xlErrNull)
xlErrNum [#NUM!] CVErr(xlErrNum)
xlErrRef [#REF!] CVErr(xlErrRef)
xlErrValue [#VALUE!]
CVErr(xlErrValue)
You work with these built-in worksheet error values the same way you work
with the user-defined errors--as numbers converted to error values using
the CVErr function. The only difference is that for the worksheet errors,
Visual Basic for Applications provides the error numbers as built-in
constants and also provides literal error values. These items are not
provided for user-defined error values. The literal error values must be
enclosed in square brackets as shown in the table above.
Example Using Built-In Error Values:
Function Commission(SharesSold,PricePerShare)
If Not (IsNumeric(SharesSold) And IsNumeric(PricePerShare)) Then
Commission = CVErr(xlErrNum)
Else
TotalSalePrice = ShareSold * PricePerShare
If TotalSalePrice <= 15000 Then
Commission = 25 + 0.03 * SharesSold
Else
Commission = 25 + 0.03 * (0.9 * SharesSold)
End If
End If
End Function
Centralizing Error Handling Code
When you add error-handling code to your Visual Basic for Applications
macros, you will discover that the same errors are being handled over and
over again. You can reduce the size of your code and the effort required
to write code by writing a few procedures that your error- handling code
can call to handle the common error situations.
The following is an example of a function procedure that displays a
message corresponding to the error that has occurred, and where possible,
it allows the user to specify what action to take next by choosing a
particular button. It then returns the code number to the procedure that
called it.
Public Const RESUME_STATEMENT = 0 'Resume
Public Const RESUME_NEXT = 1 'Resume Next
Public Const UNRECOVERABLE = 2 'Unrecoverable error
Public Const UNRECOGNIZED = 3 'Unrecognized error
Public Const ERR_DEVICEUNAVAILABLE = 68
Public Const ERR_BADFILENAMEORNUMBER = 52
Public Const ERR_PATHDOESNOTEXIST = 76
Public Const ERR_BADFILEMODE = 54
Function FileErrors(errVal As Integer) As Integer
Dim MsgType As Integer, Msg As String, Response As Integer
MsgType = vbExalamation
Select Case errVal
Case ERR_DEVICEUNAVAILABLE 'Error #68
Msg = "That device is unavailable."
MsgType = MsgType + vbAbortRetryIgnore
Case BADFILENAMEORNUMBER 'Errors #64 & 52
Msg = "That filename is not valid."
MsgType = MsgType + vbOKCancel
Case PATHDOESNOTEXIST 'Error #76
Msg = "That path does not exist."
MsgType = MsgType + vbOKCancel
Case BADFILEMODE 'Error #54
Msg = "Can not open the file for that type of access."
MsgType = MsgType + vbOKCancel
Case Else
FileErrors = UNRECOGNIZED
Exit Function
End Select
Response = MsgBox(Msg, MsgType, "Disk Error")
Select Case Response
Case vbOK, vbRetry
FileErrors = RESUME_STATEMENT
Case vbIgnore
FileErrors = RESUME_NEXT
Case vbCancel, vbAbort
FileErrors = UNRECOVERABLE
Case Else
FileErrors = UNRECOGNIZED
End Select
End Function
Handling User Interrupts
A user can interrupt a Visual Basic for Applications procedure by
pressing CTRL+BREAK or ESC (COMMAND+PERIOD on the Macintosh). It is
possible to disable interrupts for procedures in your finished
applications. However, if you do not disable the user interrupts in the
finished procedure, you can make sure that your procedure is notified
when an interrupt has occurred so that it can close files, disconnect
from shared resources, or restore modified variables before returning
control of the application to the user.
You can trap user interrupts in your procedures by setting the
EnableCancelKey property to xlErrorHandler. When this property is set,
all interrupts will generate a run-time error number 18, which can be
trapped using an On Error statement. You can handle the error to halt the
procedure and exit the program. If the Resume statement is used to
continue the procedure after a trapped run-time error, the interrupt is
ignored.
It is also possible to ignore user interrupts completely by setting the
EnableCancelKey property to xlDisabled. In this state, Microsoft Excel
ignores all attempts by the user to interrupt the running procedure. To
restore the default interrupt processing, change the setting of the
EnableCancelKey property to xlInterrupt. To prevent a procedure from
permanently disabling user interrupts, Microsoft Excel always restores
the default setting of the EnableCancelKey property to xlInterrupt
whenever the procedure completes its execution. To ensure that interrupts
are handled correctly within your code, you must explicitly disable or
trap the interrupts every time the procedure is executed. It should be
noted that only one interrupt handler can be used for each procedure, and
that the same handler is used for all run-time errors encountered by that
procedure.
The following example demonstrates a procedure that requires a large
period of time to complete. If a user interrupts the procedure, an error
is trapped. The user interrupt first confirms that the procedure should
actually be halted and then exits the procedure in an orderly manner.
Sub ProcessData()
'Set up a user interrupt trapping as a run-time error
On Error GoTo UserInterrupt
Application.EnableCancelKey = xlErrorHandler
'Start a long duration task
For x = 1 to 1000000
For y = 1 to 10
Next y
Next x
Exit Sub
UserInterrupt:
If Err = 18 Then
If MsgBox ("Stop processing records?", vbYesNo) = vbNo Then
'Continue running at the point procedure was interrupted
Resume
Else
'Handle other errors that occur
MsgBox Error(Err)
End If
End If
End Sub
If you run the ProcessData macro and then quickly press CTRL+BREAK, a
message box that prompts you whether to stop processing records appears.
If you click Yes, another message box with "User interrupt occurred"
appears. If you click OK in this message box, the macro ends. If you
click No in the first message box, the macro continues.
Resume Statement
The Resume statement resumes code execution after an error handling
routine has finished.
REFERENCESExcel 97
For more information about trapping macro errors, click the Index tab in
Microsoft Excel 97 Visual Basic Help, type the following text
trapping errors
and then double-click the selected text to go to the "Trappable Errors"
topic.
Excel 7.0
For more information about trapping macro errors, click the Index tab in
Microsoft Excel 7.0 Help, type the following text
error trapping
and then double-click the selected text to go to the "Error trapping"
topic.
Excel 5.0
In "Visual Basic User's Guide," version 5.0, Chapter 9, "Handling Errors
and Error Values," see the following topics:
- "Preventing Your Code From Halting or Acting Unpredictably"
- "Creating Error Values That Don't Interrupt Your Code"
- "Using the Built-in Error Values of Microsoft Excel"
- "Advanced Error-Handling Techniques"
Additional query words:
5.00a 5.00c 8.00 xl97
Keywords : kbprg kbdta kbdtacode PgmOthr KbVBA
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto
|