Generating Error for UDF

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
--
Rgds, Loomah

SalCost06=CvErr(xlerrdiv0)

HTH
--
AP

"Loomah" <loomah@NOSPAM-tiscali.co.uk> a écrit dans le message de news:
449c2ff3$1_3@mk-nntp-2.news.uk.tiscali.com...
> 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
> --
> Rgds, Loomah
>

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

--
Regards,
Tom Ogilvy


"Loomah" wrote:

> 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
> --
> Rgds, 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!
;-)

"Loomah" <loomah@NOSPAM-tiscali.co.uk> wrote in message
news:449c2ff3$1_3@mk-nntp-2.news.uk.tiscali.com...
> 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
> --
> Rgds, Loomah
>