Microsoft KB Archive/193247

= Error trapping with Visual Basic for Applications in Excel for Mac =

Article ID: 193247

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q193247





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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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 for Mac 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.

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          This array is fixed or temporarily locked 11          Division by zero 13          Type mismatch 14          Out of string space 16          Expression too complex 17          Can't perform requested operation 18          User interrupt occurred 20          Resume without error 28          Out of stack space 35          Sub, function, or property not defined 47          Too many DLL application clients 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 or With block variable not set 92          For Loop not initialized 93          Invalid pattern string 94          Invalid use of Null 298         System DLL could not be loaded 320         Can't use character device names in specified file names 321         Invalid file format 322         Can't create necessary temporary file 325         Invalid format in resource file 327         Data value named was not found 328         Illegal parameter; can't write arrays 335         Could not access system registry 336         ActiveX component not correctly registered 337         ActiveX component not found 338         ActiveX component did not correctly run 360         Object already loaded 361         Can't load or unload this object 363         Specified ActiveX control not found 364         Object was unloaded 365         Unable to unload within this context 368         The specified file is out of date. This program requires a newer version 371         The specified object can't be used as an owner form for Show 380         Invalid property value 381         Invalid property-array index 382         Property Set can't be executed at run time 383         Property Set can't be used with a read-only property 385         Need property-array index 387         Property Set not permitted 393         Property Get can't be executed at run time 394         Property Get can't be executed on write-only property 400         Form already displayed; can't show modally 402         Code must close topmost modal form first 419         Permission to use object denied 422         Property not found 423         Property or method not found 424         Object required 425         Invalid object use 429         ActiveX component can't create object or return reference to this object 430         Class doesn't support OLE Automation 430         Class doesn't support Automation 432         File name or class name not found during Automation operation 438         Object doesn't support this property or method 440         OLE Automation error 440         Automation error 442         Connection to type library or object library for remote process has been lost 443         Automation object doesn't have a default value 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 or invalid property assignment 450         Wrong number of arguments or invalid property assignment 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 458         Variable uses a type not supported in Visual Basic 459         This component doesn't support events 460         Invalid Cipboard format 461         Specified format doesn't match format of data 480         Can't create AutoRedraw image 481         Invalid picture 482         Printer error 483         Printer driver does not support specified property 484         Problem getting printer information from the system. Make sure the printer is set up correctly 485         Invalid picture type 486         Can't print form image to this type of printer 735         Can't save file to Temp directory 744         Search text not found 746         Replacements too long 31001       Out of memory 31004       No object 31018       Class is not set 31027       Unable to activate object 31032       Unable to create embedded object 31036       Error saving to file 31037       Error loading from file

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 receive a message box that contains "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 Excel for Mac. 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 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, Excel for Mac 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, Excel for Mac 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.

Additional query words: XL98 XL2001 XL2004 XLX

Keywords: kbdtacode kbhowto KB193247

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.