Return error values from user defined functions

 2000-02-04    Other    0    72

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.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.