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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


.

Relevant Pages

  • Re: Extracting names and titles
    ... Since this is the programming group, you could also write a UDF or a macro to ... in some cell. ... Function ExtrWord(s As String, ... Dim sTemp As Variant ...
    (microsoft.public.excel.programming)
  • Re: Excel Addin in VB.NET 2k3 - how to return #N/A
    ... > I just created a UDF in VB.Net say ... > If TextOnly.GetType.Equals(Gettype(String)) then Return "This is a String" ... Prev by Date: ...
    (microsoft.public.office.developer.com.add_ins)
  • Re: How to transpose and concatenate long list in one column?
    ... If you are looking for a UDF you can try the below. ... strDel As String) As String ... Enter the formula into a sheet cell. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Append values and remove text
    ... To enter this User Defined Function (UDF), ... Ensure your project is highlighted in the Project Explorer window. ... Function ConcatRange(rg As Range, Optional Separator As String = ",") As String ... Dim vAs String ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Defining a numeric system
    ... I just figured out how your UDF works... ... Function AddOne(Number As String, ... Dim Char() As Byte ... ToBase As Integer, ...
    (microsoft.public.excel)