Hi

How can i get a user defined function to return an error code like #n/a.

many thank

d

Option Explicit
Function myFunc(myVal As Variant) As Variant

If IsNumeric(myVal) Then
myFunc = myVal * 2
Else
myFunc = CVErr(xlErrNA)
End If

End Function



DMc2005 wrote:

Hi

How can i get a user defined function to return an error code like #n/a.

many thank

d

--

Dave Peterson

Just a thought. Using an error like #N/A is not good user interfacing IMO.
Far better to give a meanigful message (yeah, I know Excel does, but that is
a throwback from years ago).

Using Dave's UDF as an example

Option Explicit

Function myFunc(myVal As Variant) As Variant

If IsNumeric(myVal) Then
myFunc = myVal * 2
Else
myFunc = "#Value must be numeric"
End If

End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DMc2005" wrote in message
...
Hi

How can i get a user defined function to return an error code like #n/a.

many thank

d

Bob Phillips wrote...
Just a thought. Using an error like #N/A is not good user interfacing IMO.
Far better to give a meanigful message (yeah, I know Excel does, but that is
a throwback from years ago).

Using Dave's UDF as an example

Option Explicit

Function myFunc(myVal As Variant) As Variant

If IsNumeric(myVal) Then
myFunc = myVal * 2
Else
myFunc = "#Value must be numeric"
End If

End Function
....

This philosophical point begs for discussion. The advantage of
returning a true error value is that there are functions available to
trap error values. In this case, the expected result is numeric, so
returning text would signal an error. However, in general udfs could
return text, and in those cases returning textual error messages could
make subsequent processing more complex. Also, it's generally good to
stick with a single, consistent error and exception handling approach,
and that's means conforming to how Excel's built-in functions work even
if you don't like how they work.