How to return Excel errors from VBA?

  • Thread starter Thread starter curiousgeorge408
  • Start date Start date
C

curiousgeorge408

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.
 
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
 
fun = CVErr(xlErrDiv0)
Click to expand...

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