You are currently offline, waiting for your internet to reconnect

Error trapping with Visual Basic for Applications in Excel for Mac

This article was previously published under Q193247
This article has been archived. It is offered "as is" and will no longer be updated.
When a run-time error occurs in a Microsoft Visual Basic for Applicationsmacro, an error message appears on the screen, and the macro either haltsor behaves unpredictably.

To prevent the application from crashing or behaving unpredictably, you caninclude macro code that intercepts the error and tells the macro how tohandle it. The process of intercepting and handling a run-time error iscalled "error trapping." The set of instructions that tells the applicationhow to handle the error is called the "error-handling routine" or "errorhandler."
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 oferrors that can be trapped. You can take advantage of error trapping inMicrosoft 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 mayencounter 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 arerecognized as ordinary variables and not errors. Your procedures can testfor 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- timeerror. Any Visual Basic for Applications run-time error can be simulatedby 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 ofthe available numbers for built-in errors. In future releases of VisualBasic for Applications, the internal numbers will increase as more built-in errors are added. It is recommended that you start your error numbersat 50,000 and work your way up to 65,535 to avoid possible conflicts inthe 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 belowshows the error number (constant), the literal error value, and theconverted error value.
Error number (Constant)   Literal error value     Converted error valuexlErrDiv0                   [#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 workwith the user-defined errors--as numbers converted to error values usingthe CVErr function. The only difference is that for the worksheet errors,Visual Basic for Applications provides the error numbers as built-inconstants and also provides literal error values. These items are notprovided for user-defined error values. The literal error values must beenclosed 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 Applicationsmacros, you will discover that the same errors are being handled over andover again. You can reduce the size of your code and the effort requiredto write code by writing a few procedures that your error- handling codecan call to handle the common error situations.

The following is an example of a function procedure that displays amessage corresponding to the error that has occurred, and where possible,it allows the user to specify what action to take next by choosing aparticular button. It then returns the code number to the procedure thatcalled 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 bypressing COMMAND+PERIOD. It is possible to disable interrupts forprocedures in your finished applications. However, if you do not disablethe user interrupts in the finished procedure, you can make sure that yourprocedure is notified when an interrupt has occurred so that it can closefiles, disconnect from shared resources, or restore modified variablesbefore returning control of the application to the user.

You can trap user interrupts in your procedures by setting theEnableCancelKey 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 theEnableCancelKey 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 largeperiod of time to complete. If a user interrupts the procedure, an erroris trapped. The user interrupt first confirms that the procedure shouldactually 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, amessage 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 youclick No in the first message box, the macro continues.

Resume Statement

The Resume statement resumes code execution after an error handlingroutine has finished.
XL98 XL2001 XL2004 XLX

Article ID: 193247 - Last Review: 12/05/2015 09:21:13 - Revision: 6.0

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

  • kbnosurvey kbarchive kbdtacode kbhowto KB193247