I wrote:
mySqrt = CVErr(xlErrNum)

But I would do that only if you might be passing the error back to Excel --
that is, the caller might a UDF call in an Excel formula.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"deltaquattro" wrote:
Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function
[....]
However, err cannot be passed back to the caller!

I suspect the following is what you want:

Function mySqrt(x As Double)
If x < 0 Then
mySqrt = CVErr(xlErrNum)
Else
mySqrt = Sqr(x)
End If
End Function

The key is: the UDF must be a variant type (implicit).

See Help for "cell error values" for other Excel error constants.


----- original message -----

"deltaquattro" wrote in message
...
Hi,

after some discussions on the ng I decided to keep input data checking
inside my functions. This prompts the problem of how to return an
error code from the function: for example

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function
Else
err=False
x = Application.Worksheetfunction.sqrt(x)
End If
End Function

However, err cannot be passed back to the caller! I've read about
different workarounds, and I would like to know your opinion on them,
or just which is your approach:

1. convert the Function to a Sub (easiest, but maybe slower?)
2. pass err ByRef (best?)
3. declare Function As Variant. Variant variables, however, cause a
slowdown of the code, so would this be any faster than 1. ?
4. use global variables (I'd rather not).

Thanks in advance,

Best Regards,

Sergio Rossi