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
>