How to return Excel errors from VBA?
 

How can I return Excel errors such as #NUM from a VBA function?

I cannot seem to call ERROR.TYPE() from VBA (e.g.
Application.WorksheetFunction...).

Right now, I return #VALUE by a kludge: assigning 1e308 to a type
Long variable. I could do something similar to return #DIV and
perhaps #REF.

But I would prefer to do thinks more cleanly and self-documenting; and
I would like the flexibility of returning other errors.

How to return Excel errors from VBA?
 

Hi,

Here is an example for a function that divides 2 numbers.
- if arguments are not numbers, returns #Val
- if second argument is Zero, then return #Div0
- else returns a/b

Function fun(a As Variant, b As Variant) As Variant
If Not (IsNumeric(a) And IsNumeric(b)) Then
fun = CVErr(xlErrValue)
ElseIf b = 0 Then
fun = CVErr(xlErrDiv0)
Else
fun = CDbl(a) / CDbl(b)
End If
End Function
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


" wrote:

How can I return Excel errors such as #NUM from a VBA function?

I cannot seem to call ERROR.TYPE() from VBA (e.g.
Application.WorksheetFunction...).

Right now, I return #VALUE by a kludge: assigning 1e308 to a type
Long variable. I could do something similar to return #DIV and
perhaps #REF.

But I would prefer to do thinks more cleanly and self-documenting; and
I would like the flexibility of returning other errors.

How to return Excel errors from VBA?
 

On Nov 28, 3:20 pm, sebastienm
wrote:
fun = CVErr(xlErrDiv0)

Thanks. And for a list of the Excel error values that I wanted, use
VBA Help to search for "Excel constants", then select xlCVError.