The information in this article applies to:
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.
MORE INFORMATIONMicrosoft 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.aspWhile 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 StatementThe 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.Err FunctionThe Err function returns the number of the error encountered.Example Using the Err Function: Msgbox "The most recent error number is " & Err & _The following table contains a list of the trappable error codes you may encounter when you use the Err function.
Error FunctionThe 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 StatementThe 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:
When the Test macro is run, you will get a Message Box with "my own error
occurred" as the message.
CVErr FunctionThe 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.
Example Using the CVErr Function:
Using Built-In Error ValuesThere 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.
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:
Centralizing Error Handling CodeWhen 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.
Handling User InterruptsA user can interrupt a Visual Basic for Applications procedure by pressing COMMAND+PERIOD. 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.
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 StatementThe Resume statement resumes code execution after an error handling routine has finished.Additional query words: XL98
Keywords : kbdta kbdtacode xlvbahowto xlvbainfo |
Last Reviewed: November 13, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |