Returning custom errors from UDFs

[Dave]

I'm trying to return a custom error to Excel from my UDF. The calling
cell always displays #Value! when an error occurs. The Error.Type is 3,
corresponding to #Value!. Why don't I get my custom error number back?

Dave



Public Function Foo(intTest as Integer) As Variant

Dim lngFuncReturn As Long

On Error GoTo Foo_Error

'...Get the description
lngFuncReturn = MyFoo(intTest)
If (lngFuncReturn <> STATUS_SUCCESS) Then

'...Return a custom error number
Foo = CVErr(lngFuncReturn)

Else

'...Make the function value the returned value:
Foo = lngFuncReturn

End If

Foo_Resume:
Exit Function

Foo_Error:

Foo = CVErr(xlErrNA)
Resume Foo_Resume

End Function

 

[Vasant Nanavati]

Try:

Foo = CInt(CVErr(lngFuncReturn))

 

[Dave]

That just returns the integer value, which I can't distiguish from the
normal return values.

Dave

 

[Tom Ogilvy]

hard to tell from what you show, but Excel will show #Value for any error
returned except the defined errors. There is no provision for custom
errors.

 

[Dave]

I'm stuck with #Value!... That's useless.

 

[Chip Pearson]

You can't have custom error values in functions. You are limited
to the values in XLCVError.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Vasant Nanavati]

Sorry; I thought you wanted the error number back. What would you like to
see in the cell?

 

[Dave Peterson]

Return your own string:

#Thisismyownerror!

You won't be able to use =iserror(), but maybe that's not necessary.

 

[Dave]

I think I'll use Dave's suggestion of #Thisismyownerror! I did have all
the error code worked out and a translation macro written. Now I'll
just retrun the translated message surrounded by #...! I thought I
could return the error code number, test it with IsError and then allow
the user to translate it with my other macro.

Thanks for everyone's suggestions & help.

Dave