VB.NET COM Server Implementing Excel UDF Not Callable with Extra Excel.Range

Updated answer regarding additional range options

Okay, after discussing the issue with some MVP Excel applications, there are a few points I can add to this unusual issue regarding extra Range parameters when used in a User Defined Function (UDF) created using VB.NET.

(1) First, this seems to be some kind of subtle issue with the way Excel calls a .NET UDF; the problem cannot be replicated with VBA. For example, the following function, written in VBA, does not show the same problem:

Function OptionalRange4(Optional ByVal optRange1 As Excel.Range = Nothing, _
                        Optional ByVal optInt As Integer = 0, _
                        Optional ByVal optRange2 As Excel.Range = Nothing) _
                        As Variant
    Dim arg1 As String
    Dim arg2 As String
    Dim arg3 As String

    If optRange1 Is Nothing Then
        arg1 = "<Nothing>"
    Else
        arg1 = optRange1.Address
    End If

    arg2 = CStr(optInt)

    If optRange2 Is Nothing Then
        arg3 = "<Nothing>"
    Else
        arg3 = optRange2.Address
    End If

    OptionalRange4 = arg1 + "|" + arg2 + "|" + arg3
End Function

      

        
        
        
      

    

(2) Another piece of information I learned is that you can pass one parameter to a worksheet function in multi-zonal ranges by enclosing the entire address of the range in parentheses. For example, the following formula will pass two range arguments to "MyFunction", the first argument being a multi-zone address:

=MyFunction((A1:C3,D4:E5), G11)

      

        
        
        
      

    

So, there are two solutions available to you:

(a) Change the parameter types from range to object and then move the object to range inside your code. This is seemingly the cleanest and simplest approach.

(b) Create a VBA wrapper as your interface, which then calls your Visual Basic.NET code. I'm sure this is not what you would like, but I thought I should mention this. For what it's worth, this approach is a must if you want to use UDFs from VSTO since at least Visual Studio 2008. How to Create Excel UDFs in VSTO Managed Code Paul Stubbs describes this approach.

What can I add Hugh. This is a subtle bug or flaw in the way the Excel call tries to work through the .NET Interop. I don't know why exactly this is failing, but it does. Fortunately, the solution is not too cumbersome.

- Mike

Previous answer regarding extra range options

I still don't know why I can't get this to work:

Function OptionalRange2(Optional ByVal optRange1 As Excel.Range =

      

        
        
        
      

    

Nothing, _ Optional ByVal optRange2 Like Excel.Range = Nothing) Because the object _ Implements IFunctions.OptionalRange2

Both = OptionalRange2 (E2: E7, F2: F7) First = OptionalRange2 (E2: E7,)
Second = OptionalRange2 (, F2: F7)
None = OptionalRange2 ()
None = OptionalRange2 (,)

[Calls to both and neither succeed.] The comma changes the call. It is as if, without comma, Excel sends two Nothings, but with comma, it does something else -

• send System.Type.Missing / System.Reflection.Missing.Value?

• call Range.Value (), the default function on Range, passing a different type of parameter?

In any case, for example, there is a runtime type mismatch because it just doesn't get into the debugger stack frame.

What's going on here is that Excel can get confused when passing range arguments. The reason is that the range address may include a comma, such as the "A1: C3, D4: E5" multicast address. When you pass such an address as a range argument, Excel must determine whether it is one multi-range range or consists of two range arguments in the same scope.

So the question is: how does Excel interpret it?

The answer is that Excel always interprets the comma as the argument separator, so there is really no way to pass a multirange range in one parameter to Excel directly. To do this, you will need to go to a named range (for example, define a multi-scoped range named "MyRange") and then pass that to your custom worksheet function.

So in these examples, when you pass into MyFunction (A1: C3, D4: E5), you are using both parameters, not just one.

Ok, but where is the problem?

The problem is this: how does Excel interpret the address of the range "A1: C3" when passed to a worksheet function? The answer is that Excel is trying to interpret this as a single range address with invalid syntax because the trailing part of the address after the comma is missing. In short, this is a syntax error, so your function is never called in the first place, and the results are #VALUE.

This is very unfortunate, as I already said that Excel should take precedence over the interpretation of the comma as an argument separator, not as a region separator within a range address. But alas, Excel is incompatible here, and it tries to interpret the leading or trailing comma as part of the range address itself.

You've pushed for a solution yourself: change the datatype for these optional parameters from range to object, then move the object to range inside your code. If you do this, Excel will no longer try to interpret the arguments as a range, and everything will work fine.

You can check the difference between the two approaches with the following custom functions:

Function DualOptionalRanges(Optional ByVal optRange1 As Excel.Range = Nothing, _
                            Optional ByVal optRange2 As Excel.Range = Nothing) _
                            As Object _
    Implements IFunctions.DualOptionalRanges

    Dim arg1 As String = If(optRange1 IsNot Nothing, optRange1.Address, "<Nothing>")
    Dim arg2 As String = If(optRange2 IsNot Nothing, optRange2.Address, "<Nothing>")

    Return arg1 + "|" + arg2
End Function

Function DualOptionalVariants(Optional ByVal optVariant1 As Object = Nothing, _
                              Optional ByVal optVariant2 As Object = Nothing) _
                              As Object _
    Implements IFunctions.DualOptionalVariants

    Dim range1 As Excel.Range

    Try
        range1 = CType(optVariant1, Excel.Range)
    Catch ex As Exception
        range1 = Nothing
    End Try

    Dim range2 As Excel.Range

    Try
        range2 = CType(optVariant2, Excel.Range)
    Catch ex As Exception
        range2 = Nothing
    End Try


    Dim arg1 As String
    If range1 IsNot Nothing Then
        arg1 = range1.Address
    Else
        arg1 = If(optVariant1 IsNot Nothing, optVariant1.ToString(), "<Nothing>")
    End If

    Dim arg2 As String
    If range2 IsNot Nothing Then
        arg2 = range2.Address
    Else
        arg2 = If(optVariant2 IsNot Nothing, optVariant2.ToString(), "<Nothing>")
    End If

    Return arg1 + "|" + arg2
End Function

      

        
        
        
      

    

I think this should be the last hurdle. (Famous last words, right?)

- Mike

Answer Regarding IIF

Bummer on IIF ("immediate if") problem. In addition to the CType () and DirectCast () statements and the new functionality provided by the IF keyword, I believe that anything that uses method syntax in VB.NET is actually a method call that is executed at runtime. The exceptions are CType () and DirectCast (); they are casting mechanisms that are evaluated at compile time, and the IF keyword can now be used as a statement using syntax similar to the method, but it uses short-circuit evaluation instead of evaluating all parameters before evaluating the IF statement.

The lack of short circuit capability for IIF was discussed in detail by Paul Wick in 2006 in an article IIF, True Ternary Operator and Backward Compatibility . The article shows a strong bias towards modifying IIF to use short circuit estimation compared to VB.NET 2009. However, due to backward compatibility issues, they decided not to change the IIF behavior and just extend how the IF keyword could be used, allowing use it as an operator. This is described in IIF becomes If and a true ternary operator , also by Paul Vick.

Sorry for getting caught up in this.

- Mike

Name conflict response>

Hi Hugh,

I think you have a name conflict with the built-in "DB" function in Excel. If you rename your custom function from "DB" to "MyDb" (or pretty much everything else) I think you will be fine.

It was difficult to find out what could be wrong with your add-in because your code looks clean. So I created an automation add-in using VB.NET and experimented with your custom function (UDF) using the same exact parameter signature you described. I found no problem.

The added automation system was defined as follows:

Imports System
Imports System.Collections.Generic
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel

<ComVisible(True)> _
<Guid("7F1A3650-BEE4-4751-B790-3A527195C7EF")> _
Public Interface IFunctions
    ' * User-Defined Worksheet Functions Definitions *
    Function MyDb(ByVal A As Integer, _
                ByRef B As Excel.Range, _
                ByRef C As Excel.Range, _
                Optional ByVal D As Integer = 1, _
                Optional ByVal E As Double = 0, _
                Optional ByVal F As Excel.Range = Nothing, _
                Optional ByVal G As Boolean = True, _
                Optional ByVal H As Integer = 1) As Object

    Function OptionalBoolean(Optional ByVal optBoolean As Boolean = False) As Object

    Function OptionalDouble(Optional ByVal optDouble As Double = 0.0) As Object

    Function OptionalInteger(Optional ByVal optInteger As Integer = 0) As Object

    Function OptionalRange(Optional ByVal optRange As Excel.Range = Nothing) As Object

    Function OptionalVariant(Optional ByVal optVariant As Object = Nothing) As Object
End Interface

<ComVisible(True)> _
<Guid("FCC8DC2F-4B44-4fb6-93B5-769E57A908A1")> _
<ProgId("VbOptionalParameters.Functions")> _
<ComDefaultInterface(GetType(IFunctions))> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class Functions
    Implements IFunctions

    ' * User-Defined Worksheet Functions */
    Function MyDb(ByVal A As Integer, _
                ByRef B As Excel.Range, _
                ByRef C As Excel.Range, _
                Optional ByVal D As Integer = 1, _
                Optional ByVal E As Double = 0, _
                Optional ByVal F As Excel.Range = Nothing, _
                Optional ByVal G As Boolean = True, _
                Optional ByVal H As Integer = 1) As Object _
            Implements IFunctions.MyDb

        Return "MyDb Successfully called"
    End Function

    Function OptionalBoolean(Optional ByVal optBoolean As Boolean = False) As Object _
        Implements IFunctions.OptionalBoolean

        Return optBoolean.ToString()
    End Function

    Function OptionalDouble(Optional ByVal optDouble As Double = 0.0) As Object _
        Implements IFunctions.OptionalDouble

        Return optDouble.ToString()
    End Function

    Function OptionalInteger(Optional ByVal optInteger As Integer = 0) As Object _
        Implements IFunctions.OptionalInteger

        Return optInteger.ToString()
    End Function

    Function OptionalRange(Optional ByVal optRange As Excel.Range = Nothing) As Object _
        Implements IFunctions.OptionalRange

        If optRange Is Nothing Then
            Return "<No Range Provided>"
        Else
            Return optRange.Address
        End If
    End Function

    Function OptionalVariant(Optional ByVal optVariant As Object = Nothing) As Object _
        Implements IFunctions.OptionalVariant

        If optVariant Is Nothing Then
            Return "<No Argument Provided>"
        Else
            Return optVariant.ToString()
        End If
    End Function

    ' * automation add-in Registration *
    <ComRegisterFunctionAttribute()> _
    Public Shared Sub RegisterFunction(ByVal type As Type)
        Registry.ClassesRoot.CreateSubKey( _
          GetSubKeyName(type, "Programmable"))

        Dim key As RegistryKey = _
            Registry.ClassesRoot.OpenSubKey( _
              GetSubKeyName(type, "InprocServer32"), _
              True)

        key.SetValue( _
          String.Empty, _
          System.Environment.SystemDirectory + "\mscoree.dll", _
          RegistryValueKind.String)
    End Sub

    <ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
        Registry.ClassesRoot.DeleteSubKey( _
          GetSubKeyName(type, "Programmable"), _
          False)
    End Sub

    Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyCategory As String) As String
        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
        sb.Append("CLSID\{")
        sb.Append(type.GUID.ToString().ToUpper())
        sb.Append("}\")
        sb.Append(subKeyCategory)
        Return sb.ToString()
    End Function

End Class

      

        
        
        
      

    

Using the above, individual parameters such as Booleans, Doubles, Integer, Ranges or any Variant can be checked with OptionBoolean, OptionDouble, OptionInteger, OptionFange and OptionVariant functions. And the complete function signature can be checked with the "MyDb" function. None of the above succeeded in my testing unless you pass an explicitly incompatible argument type, such as passing a string in an integer or double parameter, passing a numeric value in a range parameter, or the like.

However, there are problems when assigning the main UDF "DB" as it conflicts with the built-in "DB" function in Excel which means "Dropping Balance" and has existed since at least Excel "97. Basically, since the UDF in the Automation add-in has same name as built-in Excel version, your UDF is ignored.

In my testing using Excel 2007, I couldn't get Excel to recognize the "DB" UDF at all. I'm not sure why you seem to be wrong with your call sometimes going through and sometimes not. It is possible that earlier versions of Excel handle this name conflict differently.

The bottom line is that you should be able to rename your UDF and then have no problem. I don't see anything else that could cause this.

I hope Hugh does it for you, fingers crossed ....

Mike

Answering additional data types

With so much information, I can only guess, but I don't think you are violating any of the COM interop rules.

I am guessing that the problem is with what kind of arguments you provide to your function. If the arguments passed to a User Defined Function (UDF) do not match the expected parameter types, then Excel will generate #VALUE! error without even calling code at all.

So in your third scenario, which doesn't work, you are passing one of the parameters incorrectly. For example, the signature for the "F" parameter is:

Optional ByVal F As Range = Nothing

      

        
        
        
      

    

With this signature, if you pass it a value directly, such as the string "Hello", then #VALUE! an error will be received if your code is not called. However, if you passed in a cell reference that contained the value "Hello", the "F" parameter would accept it without issue.

What you need to do is try to name your UDF, taking very close look at the parameter types being passed. Change the parameters one at a time and see if you force the UDF to be called at least. You can either set a breakpoint or return a UDF "Success" or the like just for debugging purposes. The key is to simply figure out what the correct parameter types are. I have a feeling that if you do this, you will quickly figure out what is wrong.

In fact, I think I could see the problem:

I'm a little suspicious of your Integer parameters, which are the "A", "D" and "H" parameters. If you pass an integer value to the function directly, then these parameters should work fine. But if you pass in a cell reference, then Excel will automatically go to Range.Value for that cell, automatically is what you want ...

... but the problem is that Excel cells can never store Integer data type! They can of course contain Integer values ​​like 0, 1, 2, -1, etc., but they are actually typed as "Double" if they are held by the cell. To prove it, you can assign Integer to Cell.Value, but it will be stored as Double - if you check the data type stored in Cell.Value with TypeName () or .GetType (). ToString (), it will return "Double".

Cells can contain booleans, strings, doubles, dates, currencies (which corresponds to decimal in .NET) and CVErr values, and what they are. In standard use, they cannot return "Integer". (If you are accessing XLOPER directly via C ++ XLL, then you can technically access integer values, but this is very non-standard and you cannot do that from VB.NET or C #.)

So I would focus on these Integer parameters first, especially with regard to passing an Integer value directly versus passing in a cell reference that contains a numeric value. I'm guessing that the optional "A" parameter can accept a cell reference, but maybe not, I'm not sure, but I'm not even sure if the optional parameters can accept a cell reference while holding a double datatype.

So, in general, I'm guessing here, but I think you should start with a working set of parameters and then change each parameter one by one to see which call works or doesn't work for each parameter.

Good luck Hugh, let us know how it goes ...

Mike