UDF Returning an Error Value: How to?
 

Is there a method of having a UDF return a "real" Excel error value such as
#NUM! ? In other words, the value returned should give a TRUE result from the
ISERR and/or ISERROR and/or ISNA worksheet functions (depending on the error)
and also give the correct response to the ERROR.TYPE worksheet function?

Thanks.


--ron

UDF Returning an Error Value: How to?
 

Hi Ron,

By way of example:

'=========================
Function TotalCost(price As Range, Quantity As Range)
Dim N As Integer
Dim total As Double
If price.Count < Quantity.Count Then
TotalCost = CVErr(xlErrValue)
Else
total = 0
For N = 1 To UBound(price())
If Not IsNumeric(price(N)) _
Or Not IsNumeric(Quantity(N)) Then
TotalCost = CVErr(xlErrNum)
Exit Function
End If
total = total + price(N) * Quantity(N)
Next N
TotalCost = total
End If
End Function
'<<=========================

---
Regards,
Norman



"Ron Rosenfeld" wrote in message
...
Is there a method of having a UDF return a "real" Excel error value such
as
#NUM! ? In other words, the value returned should give a TRUE result from
the
ISERR and/or ISERROR and/or ISNA worksheet functions (depending on the
error)
and also give the correct response to the ERROR.TYPE worksheet function?

Thanks.


--ron

UDF Returning an Error Value: How to?
 

Thank you Norman,

Had it not been for you, I would not have been able to find that list of
constants. It's not always easy for me to search through HELP for this sort of
information. But I found them using the Object Browser. I never would have
tumbled to it myself.

Thanks.

--Ron

On Sat, 17 Sep 2005 12:42:59 +0100, "Norman Jones"
wrote:

Hi Ron,

By way of example:

'=========================
Function TotalCost(price As Range, Quantity As Range)
Dim N As Integer
Dim total As Double
If price.Count < Quantity.Count Then
TotalCost = CVErr(xlErrValue)
Else
total = 0
For N = 1 To UBound(price())
If Not IsNumeric(price(N)) _
Or Not IsNumeric(Quantity(N)) Then
TotalCost = CVErr(xlErrNum)
Exit Function
End If
total = total + price(N) * Quantity(N)
Next N
TotalCost = total
End If
End Function
'<<=========================

---
Regards,
Norman



"Ron Rosenfeld" wrote in message
.. .
Is there a method of having a UDF return a "real" Excel error value such
as
#NUM! ? In other words, the value returned should give a TRUE result from
the
ISERR and/or ISERROR and/or ISNA worksheet functions (depending on the
error)
and also give the correct response to the ERROR.TYPE worksheet function?

Thanks.


--ron


--ron