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

Try:

Foo = CInt(CVErr(lngFuncReturn))

--

Vasant


"Dave" wrote in message
oups.com...
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

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

Dave

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

--

Vasant


"Dave" wrote in message
oups.com...
That just returns the integer value, which I can't distiguish from the
normal return values.

Dave

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.

--
Regards,
Tom Ogilvy

"Dave" wrote in message
oups.com...
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

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

Return your own string:

#Thisismyownerror!

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

Dave wrote:

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

--

Dave Peterson

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

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


"Dave" wrote in message
oups.com...
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