XL: Error Trapping with Visual Basic for Applications

• 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 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 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/refguide/default.asp

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:

   ARTICLE-ID: Q141571
   TITLE     : 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)

   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)

   ARTICLE-ID: Q142138
   TITLE     : 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

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)

   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!]

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

Resume Statement

The Resume statement resumes code execution after an error handling routine has finished.

REFERENCES

For more information about trapping macro errors, click the Index tab in Microsoft Excel 97 Visual Basic Help, type the following text

   trapping errors

For more information about trapping macro errors, click the Index tab in Microsoft Excel 7.0 Help, type the following text

   error trapping

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"