VBA Error Handling
Error handling refers to the way runtime errors are handled. Error handling in VBA is done using the On Error statement and the Err object. Runtime errors can be generated by attempting to execute error-causing code or they can be raised explicitly using the Err.Raise method. There are a number of built-in types of runtime errors, and custom errors can be defined as well. Each type of runtime error has a unique number which can be used to determine at runtime which type of error has occurred and respond accordingly.
On Error
The On Error statement is used to control what happens when a runtime error occurs. The On Error statement sets or removes the current error handling scope. When a runtime error occurs in VBA the error trap is triggered and if an On Error statement has been set, VBA will respond according to which type of On Error statement was used.
| Statement | Description |
|---|---|
| On Error Resume Next | Skips lines of code that cause errors. Use with caution. |
| On Error GoTo Line | When an error occurs execution will jump to a specified label or line number. |
| On Error GoTo 0 | Clears the current error and disables error handling. |
| On Error GoTo -1 | Clears the current error and resets the error trap. |
| Resume | When used after the On Error GoTo statement, Resume will continue execution from the line of code that caused the error. |
| Resume Next | When used after the On Error GoTo statement, Resume Next will continue execution from the line of code directly after the line that caused the error. |
| Resume Line | When used after the On Error GoTo statement, Resume Line will jump to a specified label or line number and continue execution. |
On Error Resume Next
Public Sub Example()
On Error Resume Next
Err.Raise 1004
Debug.Print "Error was skipped"
End SubOn Error GoTo Line
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Exit Sub
HandleError:
Debug.Print "Error Handled"
End SubNote: It is not recommended to use line numbers.
Public Sub Example()
10
20 On Error GoTo 80
30
40 Err.Raise 1004
50
60 Exit Sub
70
80 Debug.Print "Error Handled"
90
End SubOn Error GoTo...Resume
Public Sub Example()
Dim N As Long
N = 0
On Error GoTo HandleError
'Divide by zero error will be fixed by error handler
Debug.Print 1 / N
Debug.Print "Error Handled."
Exit Sub
HandleError:
If Err.Number = 11 Then
Debug.Print "Handling 'Division by zero' Error..."
N = 1
Resume
Else
Err.Raise Err.Number
End If
End SubOn Error GoTo...Resume Next
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Debug.Print "Error Handled. Resuming Next..."
Exit Sub
HandleError:
Debug.Print "Handling Error..."
Resume Next
End SubOn Error GoTo...Resume Line
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Continue:
Debug.Print "Resuming..."
Exit Sub
HandleError:
Debug.Print "Error Handled"
Resume Continue
End SubNote: It is not recommended to use line numbers.
Public Sub Example()
10
20 On Error GoTo 100
30
40 Err.Raise 1004
50
60 Debug.Print "Resuming..."
70
80 Exit Sub
90
100 Debug.Print "Error Handled"
110 Resume 60
120
End SubOn Error GoTo 0
Public Sub Example()
On Error Resume Next
Err.Raise 1004 'Error will be skipped
On Error GoTo 0
Err.Raise 1004 'Error will be raised
End SubOn Error GoTo -1
Public Sub Example()
On Error GoTo HandleError1
Err.Raise 1004
Exit Sub
HandleError1:
Debug.Print "HandleError1"
On Error GoTo -1
On Error GoTo HandleError2
Err.Raise 1004
Exit Sub
HandleError2:
Debug.Print "HandleError2"
Exit Sub
End SubThe Err Object
The Err Object is used to access information about a runtime error that has occurred. When a runtime error occurs, the Err object's properties are filled with information about the error. The Err object can also be used to raise errors explicitly.
| Member | Description |
|---|---|
| Clear | Clears properties of the Err object. Does NOT reset error trap or clear error handling scope. |
| Description | A text description of the error. |
| HelpContext | The context ID for a topic in a help file. |
| HelpFile | The path to help file. |
| LastDllError | Returns a system error code produced by a call to a dynamic-link library (DLL). Read-only. Always returns zero on Mac. |
| Number | The error number, 0 through 65535. |
| Raise | Raises a specified runtime error. |
| Source | The name of the object or application that originated the error. |
The Err Object is a Singleton
The Err object has a single default global instance and cannot be instantiated. The Err object can be accessed anywhere in VBA just by typing Err.
Public Sub Example()
Err.Raise 1004 'No need to instantiate Err object
End SubPublic Sub Example()
Dim E As ErrObject
Set E = New ErrObject 'Causes error
End SubProperties
The Err object's properties are filled with data when a runtime error occurs. The Number property is especially important because it can be used to dynamically respond to different types of errors. If no runtime error has occurred, the Number property will be 0. Therefore, to determine if an error has occurred in a particular line, the Number property can be checked to see if it is 0 or not.
Public Sub Example()
On Error Resume Next
Err.Raise 1004
If Err.Number <> 0 Then
With Err
Debug.Print "Number: " & .Number
Debug.Print "Description: " & .Description
Debug.Print "Source: " & .Source
Debug.Print "HelpFile: " & .HelpFile
Debug.Print "HelpContext: " & .HelpContext
Debug.Print "LastDllError: " & .LastDllError
End With
Err.Raise Err.Number
Else
Debug.Print "No Error"
End If
End SubClearing The Err Object
The Err object can be reset in a few different ways. The Err object is reset when Err.Clear is called, a Resume or Resume Next statement is executed for the error, or another On Error statement is executed. Exiting an error-causing procedure does not reset the Err object.
Note: Exiting an error-causing procedure does not reset the Err object.
Public Sub Example()
Call ErrorCausingProcedure1
Debug.Print Err.Number 'Prints 1004
Call ErrorCausingProcedure2
Debug.Print Err.Number 'Prints 0
Call ErrorCausingProcedure3
Debug.Print Err.Number 'Prints 0
Call ErrorCausingProcedure4
Debug.Print Err.Number 'Prints 0
End Sub
Public Sub ErrorCausingProcedure1()
'Exiting procedure doe NOT reset the Err object
On Error Resume Next
Err.Raise 1004
End Sub
Public Sub ErrorCausingProcedure2()
'Calling Err.Clear resets the Err object
On Error Resume Next
Err.Raise 1004
Err.Clear
End Sub
Public Sub ErrorCausingProcedure3()
'Resume Next resets the Err object
On Error GoTo HandleError
Err.Raise 1004
Exit Sub
HandleError:
Resume Next
End Sub
Public Sub ErrorCausingProcedure4()
'On Error statement resets the Err object
On Error Resume Next
Err.Raise 1004
On Error GoTo 0
End SubCalling Err.Clear only clears the Err object. Err.Clear does not reset the error handling trap or end the error handling scope. On Error GoTo -1 will clear the Err object and reset error trapping, allowing another error to be raised. On Error GoTo 0 will clear the Err object and clear the error handling scope.
Public Sub Example()
On Error Resume Next
'Error is skipped
Err.Raise 1004
'Prints 1004
Debug.Print Err.Number
'Err object is reset
Err.Clear
'Prints 0
Debug.Print Err.Number
'Error is skipped because error handling scope was not reset
Err.Raise 1004
'Prints 1004
Debug.Print Err.Number
'Clears Err object and error handling scope
On Error GoTo 0
'Prints 0
Debug.Print Err.Number
'Raises a runtime error
Err.Raise 1004
End SubError Trapping Options
Error Trapping options can be selected which can override error handling code in VBA. To change error trapping options navigate to Tools → Options → General in the Visual Basic Editor.
| Option | Description |
|---|---|
| Break on All Errors | Will enter break mode when any error is encountered regardless of error handling code. |
| Break in Class Module | Will enter break mode and show errors inside class modules. |
| Break on Unhandled Errors | This is the default setting. Will enter break mode when an error is encountered and it is not handled by code. |
CVErr Function
The CVErr function can be used to return an error from a function. CVErr returns a value of type Variant with subtype Error. Only variables of type Variant can be assigned a value using the CVErr function. CVErr can take any error number as an argument. CVErr can be used to return a cell error from a user-defined function that is intended for use in spreadsheets.
Option Explicit
Public Function ReturnValueError() As Variant
'Shows #VALUE Error in cell
ReturnValueError = CVErr(xlErrValue)
End Function| xlErr Constant | Cell Error |
|---|---|
| xlErrBlocked | #BLOCKED! |
| xlErrCalc | #CALC! |
| xlErrConnect | #CONNECT! |
| xlErrDiv0 | #DIV/0! |
| xlErrField | #FIELD! |
| xlErrGettingData | #GETTING_DATA |
| xlErrNA | #N/A |
| xlErrName | #NAME? |
| xlErrNull | #NULL! |
| xlErrNum | #NUM! |
| xlErrRef | #REF! |
| xlErrSpill | #SPILL! |
| xlErrUnknown | #UNKNOWN! |
| xlErrValue | #VALUE! |
IsError Function
The IsError function returns True if the argument expression evaluates to an error. IsError can be used to test if a cell value contains an error or a user-defined function returns an error. To return an error from a function use the CVErr function.
Option Explicit
Public Sub Example()
If IsError(Range("A1").Value) Then
Debug.Print "Range A1 contains an error."
End If
Dim E As Variant
E = ReturnError()
If IsError(E) Then
Debug.Print "E is Error: " & CStr(E)
End If
End Sub
Public Function ReturnError() As Variant
ReturnError = CVErr(xlErrValue)
End FunctionError Function
The Error/Error$ function is used to return the description text of an error. The Error function can be used to return a specific error description based on an error number or it can return the description text of the last error to occur.
Specific Error Description
Pass the optional ErrorNumber argument to the Error function to return a specific error description. Although the Error function can take a number between -2147483648 and 65535, it should be intended for use with the range 0 through 65535. If the error number is outside the valid range an Overflow runtime error will occur. If the error number is within the valid range but is not defined, the message "Application-defined or object-defined error" will be returned.
Public Sub Example()
Debug.Print Error(5) 'Prints: Invalid procedure call or argument
End SubMost Recent Error Description
Call the Error function with no ErrorNumber argument to return the description text for the last error to occur. If no error has occurred, a zero-length string will be returned. The Err.Description property can be used to get the text description of the most recent runtime error instead of using the Error function.
Public Sub Example()
On Error Resume Next
Err.Raise 5
Debug.Print Error() 'Prints: Invalid procedure call or argument
End SubRaising Errors
Use the Err.Raise method to raise a runtime error. Errors should be raised when an unacceptable state has been reached in a program. Existing VBA error numbers can be used to raise errors or custom error numbers can be created.
Raising Existing Errors
An appropriate VBA error number can be selected which describes the error.
Public Function RandomLong(MinValue As Long, MaxValue As Long) As Long
If MinValue > MaxValue Then
Err.Raise 5
End If
Randomize
RandomLong = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
End FunctionUser-Defined Errors
To raise a user-defined error, create an error number by using the vbObjectError constant and adding a number between 513 and 65535. The range 0 through 512 is reserved for system errors. The vbObjectError constant has the value -2147221504 so user-defined errors will be negative. To derive the positive portion of a user-defined error simply subtract the vbObjectError constant from the error number.
Public Sub Example()
On Error GoTo HandleError
Err.Raise Number:=vbObjectError + 513, Description:="Custom Error"
Exit Sub
HandleError:
Debug.Print Err.Number - vbObjectError, Err.Description 'Prints: 513 Custom Error
Resume Next
End SubError Statement
The Error statement raises a runtime error for a given error number. The Error statement is included for backward compatibility with older versions of VBA and Err.Raise should be used instead for new code.
Public Sub Example()
'Backward compatible
Error 5
'Use this for new code
Err.Raise 5
End SubError Numbers
Runtime errors each have a number used to identify what type of error it is. Error numbers can be used with the Err.Raise method, the Error statement, and the Error function. When a runtime error occurs, the Err.Number property will be set to the number associated with the type of error. Any positive error number not listed in the table below returns "Application-defined or object-defined error".
| Error Number | Error Text |
|---|---|
| 3 | Return without GoSub |
| 5 | Invalid procedure call or argument |
| 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 or Function 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 file |
| 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 |
| 96 | Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports |
| 97 | Can not call friend function on object which is not an instance of defining class |
| 98 | A property or method call cannot include a reference to a private object, either as an argument or as a return value |
| 321 (1 - 2) | Invalid file format |
| 322 | Can't create necessary temporary file |
| 325 | Invalid format in resource file |
| 380 (1 - 2) | Invalid property value |
| 381 | Invalid property array index |
| 382 | Set not supported at runtime |
| 383 | Set not supported (read-only property) |
| 385 | Need property array index |
| 387 | Set not permitted |
| 393 | Get not supported at runtime |
| 394 | Get not supported (write-only property) |
| 422 | Property not found |
| 423 | Property or method not found |
| 424 | Object required |
| 429 | ActiveX component can't create object |
| 430 | Class does not support Automation or does not support expected interface |
| 432 | File name or class name not found during Automation operation |
| 438 | Object doesn't support this property or method |
| 440 | Automation error |
| 442 | Connection to type library or object library for remote process has been lost. Press OK for dialog to remove reference. |
| 443 | Automation object does not 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 setting |
| 448 | Named argument not found |
| 449 | Argument not optional |
| 450 | Wrong number of arguments or invalid property assignment |
| 451 | Property let procedure not defined and property get procedure did not return an object |
| 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 an Automation type not supported in Visual Basic |
| 459 | Object or class does not support the set of events |
| 460 (1 - 2) | Invalid clipboard format |
| 461 | Method or data member not found |
| 462 | The remote server machine does not exist or is unavailable |
| 463 | Class not registered on local machine |
| 481 (1 - 2) | Invalid picture |
| 482 (1 - 2) | Printer error |
| 735 | Can't save file to TEMP |
| 744 | Search text not found |
| 746 | Replacements too long |
| 1004 | Application-defined or object-defined error |
| 31001 | Application-defined or object-defined error. *Out of memory |
| 31004 | Application-defined or object-defined error. *No object |
| 31018 | Application-defined or object-defined error. *Class is not set |
| 31027 | Application-defined or object-defined error. *Unable to activate object |
| 31032 | Application-defined or object-defined error. *Unable to create embedded object |
| 31036 | Application-defined or object-defined error. *Error saving to file |
| 31037 | Application-defined or object-defined error. *Error loading from file |