Generating Error for UDF

[Loomah]

Hi
This is something that has intrigued me for some time but I've never really
faced up to it!

When creating a user defined worksheet function how do I generate an error?

For example the following gives me a #VALUE! error where I want one (FTE
must be between 0 & 1 inclusive) but I'm not sure I've used a valid method.

Using this function in cell A1, =SalCost06(A2,A3) with a salary of 20000 in
A2 and FTE = 0.5 in A3 I get the expected result (12500). Change A3 to 5
and I get #VALUE!.

Function SalCost06(Salary As Double, Optional FTE As Single = 1) As Double
Application.Volatile
If FTE < 0 Or FTE > 1 Then
SalCost06 = Error 'this does the job but I don't understand!!
Exit Function
End If
SalCost06 = (Salary * FTE) + (Salary * 0.25 * FTE)
End Function

To expand this, if I needed to divide something by the FTE and FTE = 0 I
would want a #DIV/0! error but I can't get it - or rather I don't how to (or
if I can) get it.

BTW there is much more scope for error in this function as the calculation
is considerably more involved than I've shown in the example, but that's for
me to handle within the function!! This is just about reporting an error in
the worksheet!

TIA & Happy Friday

 

[Ardus Petus]

SalCost06=CvErr(xlerrdiv0)

HTH

 

[Tom Ogilvy]

You get #Value because you set the function to Error which is a uninitialized
variant, but the function is typed to return a double.

if you took you existing function and added

Dim error as Double

at the top, you wouldn't get #Value.





If you want to actually produce #Value

Function SalCost06(Salary As Double, Optional FTE As Double = 1) As Variant
Application.Volatile
If FTE < 0 Or FTE > 1 Then
' SalCost06 = Error 'this does the job but I don't understand!!
SalCost06 = CVErr(xlErrValue)
Exit Function
End If
SalCost06 = (Salary * FTE) + (Salary * 0.25 * FTE)
End Function

If you wanted to produce Divide by Zeror for a zero second argument

Function SalCost06(Salary As Double, Optional FTE As Double = 1) As Variant
Application.Volatile
if FTE = 0 then
SalCost06 = cverr(xlErrDiv0)
Exit Function
End if
If FTE < 0 Or FTE > 1 Then
' SalCost06 = Error 'this does the job but I don't understand!!
SalCost06 = CVErr(xlErrValue)
Exit Function
End If
SalCost06 = (Salary * FTE) + (Salary * 0.25 * FTE)
End Function

 

[Loomah]

Thank you both for your responses.
I think my post was a little misleading as I was just using #DIV/0 as an
example (I could as easily have used name or n/a!). The whole point was how
do I generate an error on the worksheet and I believe the answer (which I'm
off to explore) is the function CVErr()

Thanks again!
;-)