Return error values from user defined functions

 2000-02-04    Other    0    149

With the example function below you can get user defined functions to return error values just like the built in functions in Excel do.

Function DummyFunctionWithErrorCheck(InputValue As Variant) As Variant
    If InputValue < 0 Then ' return an error value
        DummyFunctionWithErrorCheck = CVErr(xlErrNA) ' returns #N/A!
        ' xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue
        Exit Function
    End If
    ' do the calculation
    DummyFunctionWithErrorCheck = InputValue * 2
End Function
The custom function must return a Variant to be able to return the error value.