Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
UDF Raising an error
Hi
How can i get a user defined function to return an error code like #n/a. many thank d |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore error msgs in formula references | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
ERROR | Excel Discussion (Misc queries) |