User defined functions, or UDFs, are functions that you write in VBA which can be called from worksheet cells or from other VBA procedures.  Excel’s built-in worksheet functions return a slew of error messages when you use them incorrectly.  You might see #NAME if you make a typo entering the function name, or #DIV/0! if you try to divide by zero in a formula.

You can, and should, check for errors in your custom functions.  Whether you should report those errors the way the built-in functions do is a matter of personal preference.

VBA provides the CVErr function to allow you to raise errors in your custom function.

Function DivideTwo(dNum As Double, dDenom As Double) As Variant

    If dDenom = 0 Then
        DivideTwo = CVErr(xlErrDiv0)
        Exit Function
    End If
   
    DivideTwo = dNum / dDenom
   
End Function

Excel’s object library provides built-in constants for raising certain errors.  xlErrDiv0 is shown above.  From the Object Browser (F2 from the VBE), the following constants are available to use:

What about that “personal preference” comment?  There is a cost to being able to report specific errors from your UDF and that is that the data type of the UDF must be a Variant.  Any problems that occur in a UDF return the #VALUE error, such as code logic problems.  When you try to return a CVErr and the function is not typed as a Variant, you will get a #VALUE error regardless of the specific error you tried to return.  You’ll lose a little performance having all your UDFs typed as Variant, and that’s the decision you’ll have to make.  For me, being able to return a meaningful error message outweighs any performance loss.

Another personal preference of mine is having the error checking at the top of the function.  I’m a big believer in one exit point for every function, that is, no Exit Function statements.  However, there are a few exceptions and this, for me, is one of them.  I prefer to have the error checking at the top and all by itself – not integrated into the logic of the function.  That may not be possible or practical if the error doesn’t present itself until half way through the calculation, but for validating inputs, it is possible and it’s my preference.