ThreeWave Returning Errors From User Defined Functions In VBA

This page describes how to return errors from VBA User Defined Functions.
ShortFadeBar

Returning Errors From VBA Functions

If you use VBA or another COM language to create User Defined Functions (functions that are called directly from worksheet cells) in a module or add-in, you likely will need to return an error value under some circumstances. For example, if a function requires a positive number as a parameter and the user passes in a negative number, you should return a #VALUE error. You might be tempted to return a text string that looks like an error value, but this is not a good idea. Excel will not recognize the text string, for example #VALUE, as a real error, so many functions and formulas may misbehave, especially ISERROR, ISERR, and IFERROR, and ISNA. These functions require a real error value.

VBA provides a function called CVErr that takes a numeric input parameter specifying the error and returns a real error value that Excel will recognize as an error. The values of the input parameter to CVErr are in the XLCVError Enum and are as follows:

  • xlErrDiv0 (= 2007) returns a #DIV/0! error.
  • xlErrNA (= 2042) returns a #N/A error.
  • xlErrName (= 2029) returns a #NAME? error.
  • xlErrNull (= 2000) returns a #NULL! error.
  • xlErrNum (= 2036) returns a #NUM! error.
  • xlErrRef (= 2023) returns a #REF! error.
  • xlErrValue (= 2015) returns a #VALUE! error.

The only legal values of the input parameter to CVErr function are those listed above. Any other value causes CVErr to return a #VALUE. This means, unfortunately, that you cannot create your own custom error values. In order to return an error value, the function's return data type must be a Variant. If the return type is any other data type, the CVErr function will terminate VBA execution and Excel will report a #VALUE error in the cell.

Note that these errors are meaningful only to Excel and have nothing at all to do with the Err object used to work with runtime errors in VBA code.

Example Code

The following is a example using CVErr. 

Function Test(D As Double) As Variant
    If D < 0 Then
        Test = CVErr(xlErrValue)
    Else
        Test = D * 10
    End If
End Function

This function will return a #VALUE! error if the input parameter is less than 0. Note that the return type of the function is Variant.

You can also use CVErr to test whether a cell has a specific error value in it. However, you must first test whether the cell contains any sort of error, and then, if it does contain an error, test which type of error. For example, 

Dim R As Range
Set R = Range("A1")
If IsError(R.Value) = True Then
    If R.Value = CVErr(xlErrValue) Then
        Debug.Print "#VALUE error"
    Else
        Debug.Print "Some other error"
    End If
End If

If you attempt to compare a cell's value to a value produced by CVErr, and the cell does not contain an error value, you will get a run-time error 13, Type Mismatch. For example, the following code will fail if A1 does not contain an error value. 

Dim R As Range
Set R = Range("A1")
If R.Value = CVErr(xlErrValue) Then ' error 13 if A1 has no error
    Debug.Print "#VALUE error"
End If

You can use CVErr in a Select Case statement to test the various error types. For example, 

Dim R As Range
Set R = Range("A1")
If IsError(R.Value) = True Then
    Select Case R.Value
        Case CVErr(xlErrValue)
            Debug.Print "#VALUE error"
        Case CVErr(xlErrDiv0)
            Debug.Print "#DIV/0 error"
        Case CVErr(xlErrName)
            Debug.Print "#NAME? error"
        Case Else
            Debug.Print "Some other error"
    End Select
End If
ShortFadeBar
LastUpdate This page last updated: 31-March-2011.

-->