Excel Addin in VB.NET 2k3 - how to return #N/A

[.]

Hi,

I just created a UDF in VB.Net say

Function GetTextOnly(TextOnly as object) as object
' =GetTextOnly("AnyString")
If TextOnly.GetType.Equals(Gettype(String)) then Return "This is a String"
' =GetTextOnly(A1) and A1 set to AnyString
Dim RefValue as object = Ctype(TextOnly,excel.range).value
If RefValue.GetType.Equals(Gettype(String)) then Return "This is a range
containing a String"
End Function

I've done some testing on that function in Excel A2 ie

Function return as expected
A2
Function Return
=GetTextOnly("AnyString") This is a String
=GetTextOnly(A1) and A1 set to AnyString This is a range containing a
String

Function return as not expected
=GetTextOnly(A1) and A1 set to nothing #Value
=GetTextOnly(A1) and A1 set to 123 0

In fact, I want my UDF to return #N/A (as a real Excel error) ie not a
string "#N/A" but the correct Excel constant #N/A (-2146826246)

Thanks for your help

 

[Dr. Stephan Kassanke]

Hi,

I just created a UDF in VB.Net say

Function GetTextOnly(TextOnly as object) as object
' =GetTextOnly("AnyString")
If TextOnly.GetType.Equals(Gettype(String)) then Return "This is a String"
' =GetTextOnly(A1) and A1 set to AnyString
Dim RefValue as object = Ctype(TextOnly,excel.range).value
If RefValue.GetType.Equals(Gettype(String)) then Return "This is a range
containing a String"
End Function

I've done some testing on that function in Excel A2 ie

Function return as expected
A2
Function Return
=GetTextOnly("AnyString") This is a String
=GetTextOnly(A1) and A1 set to AnyString This is a range containing
a
String

Function return as not expected
=GetTextOnly(A1) and A1 set to nothing #Value
=GetTextOnly(A1) and A1 set to 123 0

In fact, I want my UDF to return #N/A (as a real Excel error) ie not a
string "#N/A" but the correct Excel constant #N/A (-2146826246)

Thanks for your help

Hi,

use CVErr(xlErrNA). I am not sure wether this is a VBA specific functon
which may not be available in .NET.

cheers,
Stephan

 

[.]

Hi,

use CVErr(xlErrNA). I am not sure wether this is a VBA specific functon
which may not be available in .NET.

Hi,

In fact, CVErr is a VBA specific function and couldn't be use in DotNet as
in VBA. It had try

Return Microsoft.Office.Interop.Excel.XlCVError.xlErrNA

which is ok for DotNet but the result I have, is 2042 ie xlErrNA constant
number (not really an #N/A error).

I also try to directly return "#N/A" but Excel consider it as a string (not
an error!).

Thanks for your help

 

[Dr. Stephan Kassanke]

[snip]

In fact, CVErr is a VBA specific function and couldn't be use in DotNet as
in VBA. It had try

Return Microsoft.Office.Interop.Excel.XlCVError.xlErrNA

which is ok for DotNet but the result I have, is 2042 ie xlErrNA constant
number (not really an #N/A error).

I also try to directly return "#N/A" but Excel consider it as a string
(not
an error!).

Thanks for your help

ok, that's certainly a severe drawback of the .Net Interop implementation.
Another approach might be to use a worksheet function which results in an
error value. I have the german version of Excel, so I cannot validate this
for the english version.

The function NV() (in the german version) yields #NV which corresponds to
the english error value #NA. Unfortunately, the
Application.Worksheetfunction method does not support this particular
function. May there is a way in .Net to access the function.

Additionally I found the following thread:
http://www.pcreview.co.uk/forums/thread-954694.php

Sorry, I am stuck here. Good luck!

Stephan

 

[.]

ok, that's certainly a severe drawback of the .Net Interop implementation.

Another approach might be to use a worksheet function which results in an
error value. I have the german version of Excel, so I cannot validate this
for the english version.

The function NV() (in the german version) yields #NV which corresponds to
the english error value #NA. Unfortunately, the
Application.Worksheetfunction method does not support this particular
function. May there is a way in .Net to access the function.

VLOOKUP, HLOOKUP and MATCH are know function that usually returns #N/A. Each
of them are available in DotNet.

That's a really good news ie VLOOKUP(1,1,1,1) return #N/A in Excel.

Then, I try

Return Ctype(CellRef,
Excel.Range).Application.WorkSheetFunction.Match(1,1,True)

Match function definitely results a #N/A. But DotNet marshal the return to
something else. And, this is the real issue.

Additionally I found the following thread:
http://www.pcreview.co.uk/forums/thread-954694.php

Here, your link is important. To have #N/A return from an UDF, it seems that
the function result have to be a variant (not an available type in DotNet !)
and not an object.

..Net 2.0 Framework introduce a VariantWrapper but I don't know how to use
it. What I learned is that VariantType must be I4 for Error. But how to
initialize a VariantType ??? It seems to me that the VariantWrapper works
only for function parameters, not for the function result.

Then, an additional issue would be to initialize VariantType like String,
like Array ...

Is it the only way to go or is there another turnaround ?

Many Thanks

 

[.]

[RESOLVED]

How to return an Excel error variable from a DotNet UDF

Consider this UDF

' <Code Begin>
Public Sub GetTextOnly(TextOnly as Object) as Object
If TextOnly.GetType.Equals(GetType(String)) Then
' ie =GetTextOnly("SomeText")
Return TextOnly
else
if Ctype(TextOnly,Excel.Range).Value.GetType.Equals(GetType(String))
Then
' ie =GetTextOnly(A1) and A1 set to SomeText
Return Ctype(TextOnly,Excel.Range).Value
else
' ie =GetTextOnly(123) or =GetTextOnly(A1) and A1 set to 123
' Need to return #N/A ie xlConstant = -2146826246
Return New
System.Runtime.InteropServices.ErrorWrapper(-2146826246)
End Sub
' <Code End>

This Excel formula
=IF(ISNA(GetTextOnly("SomeText")),"#N/A error successfull","Text Type")
Returns : Text Type

=IF(ISNA(GetTextOnly(1)),"#N/A error successfull","Text Type")
Return : #N/A error successfull


Additional information
ErrorWrapper is a DotNet Framework version 1.1 Class
http://msdn.microsoft.com/library/d...timeinteropserviceserrorwrapperclasstopic.asp


Thanks to all and especially to Stephan