.net - 不能使用可选Excel.Range调用的实现Excel UDF的VB.NET COM Server

Question

版本号

Excel 2007（12.0.6425.1000）SP2，带有VB.NET的Visual Studio 2008，Windows Vista

结束

我有一个用VB.NET编写的具有以下签名的Excel UDF：

Public Function NonExcelName(ByVal A As Integer, _
                        ByRef B As Range, ByRef C As Range, _
                        Optional ByVal D As Integer = 1, _
                        Optional ByVal E As Double = 0, _
                        Optional ByVal F As Range = Nothing, _
                        Optional ByVal G As Boolean = True, _
                        Optional ByVal H As Integer = 1) As Object

我很容易在Excel中使用如下可选参数调用此方法：

=NonExcelName(99,$D$2:$D$65,$B$2:$B$65,12,,,,0)

持续了几个月，直到昨晚。现在，这是一个问题。不知道为什么。症状是：


仅必需的参数
如果我提供所有必需的参数并省略可选的参数，则该函数是可调用的：我将在VB.NET调试器中遇到一个断点。

必填参数以及一些可选参数
如果我提供所有必需的参数以及可选的参数（直至可选的Range G），则该函数是可调用的：我将在VB.NET调试器中遇到一个断点。

必需的参数以及范围G中的可选参数
如果我使用所有必需的参数以及包括范围G的可选参数调用该函数，则得到#VALUE!。在这种情况下，调试器中第一行代码的断点不会被击中：该函数不会被调用。

所有参数
作品。

除可选参数G以外的所有参数
不起作用。


我无法确定可能导致此问题的任何Windows更新。我以相同的方式实现了其他COM服务器，这些服务器没有可选的范围，也没有出现此问题。

我尝试使用 Optional ByVal F As Range = Nothing无效。

我尚未将源代码回滚到昨晚。我接下来要尝试。



球场

我违反了COM / .NET交互的一些规则吗？ Windows宇宙中是否发生了某些变化？有什么能解释为什么Excel / COM拒绝使用逗号分隔的可选参数来调用此函数的原因？



编辑2009-10-07：将Integer参数更改为Double并没有帮助。将 Optional ByVal F As Range = Nothing更改为 Optional ByRef F As Range = Nothing没有帮助。



编辑2009-10-10：我将UDF从DB更改为NonExcelName，以强调它不与Excel函数名称冲突的事实。真实名称是要在Web上发布的自定义软件，所以我不想透露这个名称。



编辑2009-10-11：我已经运行了Mike的代码，并且得到的结果与我自己的代码几乎相同：当我省略可选的Range参数（带逗号）时，它将失败。

alt text http://www.iwebthereforeiam.com/files/MyDb%20fail.gif

我可以输入必填参数，但我很好，但是一旦我用逗号省略了可选参数（或者不输入其余参数），函数调用就会生成#VALUE!。

编辑2009-10-11：也许像 this：也许我的可选Range作为字符串而不是作为Nothing传递。我将如何进行测试/确认？我似乎没有在代码中遇到断点。



编辑2009-10-12：哇。解决方法分为两部分。

（1）使用VB.NET IIf（）语句

这不会像C / C ++ / C＃中的三元运算符那样短路。您不能编写这样的代码并期望它可以在VB / VBA / VB.NET中运行：

Return IIf(optRange Is Nothing, "<No Range Provided>", optRange.Address)

VB / VBA / VB.NET似乎对两个表达式求值，当参数optRange为Nothing时会导致异常。您必须这样写：

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

（2）使用可选对象代替可选范围

我不太清楚问题是什么，但是我再也无法获得任何函数来接受可选的Ranges。我正在传递可选的对象，它的工作原理。这不起作用：

Function OptionalRange2(Optional ByVal optRange1 As Excel.Range = Nothing, Optional ByVal optRange2 As Excel.Range = Nothing) As Object

并且这样做：

Function OptionalRange3(Optional ByVal optRange1 As Object = Nothing, Optional ByVal optRange2 As Object = Nothing) As Object

编辑2009-10-12：我已经研究了多个范围解析假设。据我所知，这很有趣，但不是原因。此函数签名应该没有问题，因为它的中间有一个整数：

Function OptionalRange4(Optional ByVal optRange1 As Excel.Range = Nothing, _
                        Optional ByVal optInt As Integer = 0, _
                        Optional ByVal optRange2 As Excel.Range = Nothing) As Object _
    Implements IFunctions.OptionalRange4

却产生了#VALUE！对于 =OptionalRange4(,12,)。

我有一个修复程序（声明了可选的Object，而不是Range，并强制转换了参数），但是我不知道为什么会有这个问题。

Answer 1

关于可选范围参数的更新答案

好的，在与一些Excel MVP讨论了该问题之后，对于在使用VB.NET创建的用户定义函数（UDF）中使用可选的Range参数的问题，我可以补充两点。

（1）首先，这似乎是关于Excel如何调用.NET用户定义函数的某种微妙问题；无法使用VBA复制该问题。例如，用VBA编写的以下函数不会出现相同的问题：

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）我了解到的其他信息是，您可以通过将整个范围地址括在方括号中，从而将多区域范围传递到工作表函数的单个参数中。例如，以下公式会将两个Range参数传递给“ MyFunction”，第一个参数是一个多区域地址：

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

因此，您可以使用的两种解决方案是：

（a）将参数类型从Range更改为Object，然后将Object强制转换为代码中的Range。这似乎是最干净，最简单的方法。

（b）创建一个VBA包装器作为您的前端，然后调用您的Visual Basic.NET代码。我确定这不是您想要的，但是我认为我应该提一下。值得一提的是，如果希望使用VSTO中的UDF（至少从Visual Studio 2008开始），则必须使用此方法。Paul Stubbs的文章 How to create Excel UDFs in VSTO managed code描述了这种方法。

这就是我能加的休。在Excel调用尝试通过.NET Interop进行工作时，这是一个细微的错误或缺陷。我不知道为什么它会失败，但是确实会失败。幸运的是，解决方案并不繁琐。

-迈克

关于可选范围参数的事先解答


我仍然不知道为什么我不能得到
这个工作：

Function OptionalRange2(Optional ByVal optRange1 As Excel.Range =

没有， _
可选ByVal optRange2作为Excel.Range =
无）作为对象_
实现IFunctions.OptionalRange2

两者= OptionalRange2（E2：E7，F2：F7）
第一个= OptionalRange2（E2：E7，）
秒= OptionalRange2（，F2：F7）
都不= OptionalRange2（）
都不用逗号= OptionalRange2（，）

[对双方的呼叫均成功。]放置
在逗号中更改呼叫。就像
如果没有逗号，Excel将发送两个
没什么，但用逗号
其他的东西

•发送
System.Type.Missing / System.Reflection.Missing.Value？

•调用Range.Value（），默认
范围上的函数，传递参数
不同类型的？

无论哪种方式
像是在类型不匹配
运行时，因为它没有得到
进入调试器的堆栈框架。


这里发生的是，当传入范围参数时，Excel可能会感到困惑。原因是范围地址中可以包含逗号，例如多区域地址“ A​​1：C3，D4：E5”。当您将这样的地址作为范围参数传递时，Excel必须确定这是单个多区域范围还是由两个单区域范围参数组成。

所以问题是：Excel用哪种方式解释它？

答案是Excel总是将逗号解释为参数分隔符，因此实际上没有办法直接在Excel中将多区域范围传递给单个参数。为此，您必须传递一个命名范围（例如，定义一个名为“ MyRange”的多区域范围），然后将其传递到用户定义的工作表函数中。

因此，在这些示例中，当您传入MyFunction（A1：C3，D4：E5）时，您将用完两个参数，而不仅仅是一个。

好的，但是出什么问题了？

问题是：当传递给工作表函数时，Excel如何解释范围地址“ A​​1：C3”？答案是Excel会尝试使用无效的语法将其解释为单个范围的地址，因为缺少逗号后面的地址的最后一部分。简而言之，这是一个语法错误，因此永远不会首先调用您的函数，并且会产生#VALUE的结果。

这是非常不幸的，因为我已经说过Excel应该优先将逗号解释为参数分隔符，而不是范围地址内的区域分隔符。但是，可惜，Excel在这里是不一致的，它试图将前导或尾随逗号解释为范围地址本身的一部分。

您自己之前就已经找到了解决方案：将这些可选参数的数据类型从Range更改为Object，然后在代码内将Object转换为Range。如果执行此操作，则Excel将不再尝试将参数解释为范围，并且一切正常。

您可以使用以下用户定义的函数来测试两种方法之间的差异：

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

我认为这应该是最后的障碍。 （著名的遗言吧？）

-迈克

关于IIF的答案

关于IIF（“立即”）问题。我相信，除了CType（）和DirectCast（）运算符以及IF关键字赋予的新功能之外，在VB.NET中利用方法语法的所有内容实际上都是在运行时执行的方法调用。 CType（）和DirectCast（）是例外；它们是在编译时评估的强制转换机制，现在可以使用类似于方法的语法将IF关键字用作运算符，但是它使用短路评估而不是在评估IF运算符之前评估所有参数。

Paul Vic在2006年的 IIF, a True Ternary Operator and Backwards Compatibility文章中详细讨论了IIF缺乏短路能力的问题。本文显示了从VB.NET 2009开始强烈倾向于将IIF更改为使用短路评估的方法。但是，由于存在向后兼容性问题，他们决定不更改IIF的行为，而只是扩展IF关键字的用法。使用，使其可用作操作员。这在Paul Vick的 IIF becomes If, and a true ternary operator中也有描述。

对不起，您对此一无所知。

-迈克

关于命名冲突的答案

嗨休，

我认为您与Excel的内置“ DB”工作表功能存在命名冲突。如果您将用户定义的函数从“ DB”重命名为“ MyDb”（或几乎其他任何东西），我认为您会好的。

很难知道外接程序可能出了什么问题，因为您的代码看起来很干净。因此，我使用VB.NET创建了一个自动化加载项，并使用与您描述的相同的参数签名对您的用户定义函数（UDF）进行了试验。我没发现问题。

我使用的自动化加载项定义如下：

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

使用上面的代码，可以通过“ OptionalBoolean”，“ OptionalDouble”，“ OptionalInteger”，“ OptionalRange”和“ OptionalVariant”函数测试各个可选参数，例如布尔，双精度，整数，范围或任何变量。完整的功能签名可以通过“ MyDb”功能进行测试。除非传入明显不兼容的参数类型，例如将字符串传入整数或双精度参数，将数值传入范围参数等，否则上述所有方法都不会在我的测试中失败。

但是，在命名主要的用户定义函数“ DB”时会出现问题，因为这与Excel的内置“ DB”工作表函数冲突，该函数代表“递减余额”，并且至少从Excel '97开始就存在。基本上，由于自动化外接程序中的用户定义函数与Excel的内置版本具有相同的名称，因此将忽略UDF。

在使用Excel 2007进行测试时，我根本无法使Excel识别您的“ DB”用户定义函数。我不确定为什么您似乎会出现不稳定的行为，有时您的电话打来，有时却没有。 Excel的早期版本可能会以不同方式处理此命名冲突。

但最重要的是，您应该能够重命名UDF，然后没有问题。我没有发现其他可能导致这种情况的原因。

希望这对您有帮助，休，手指交叉。

麦克风

关于可选数据类型的答案

有了这么多的信息，我只能猜测，但我认为您没有违反任何COM Interop规则。

我的猜测是，问题与您向函数提供的参数有关。如果传递给用户定义函数（UDF）的参数与预期的参数类型不匹配，则Excel将抛出#VALUE！错误，根本不会调用您的代码。

因此，在第三种失败的方案中，您错误地传递了其中一个参数。例如，“ F”参数的签名为：

Optional ByVal F As Range = Nothing

使用此签名，如果您直接传递一个值，例如字符串“ Hello”，则为#VALUE！不会调用您的代码将导致错误。但是，如果传递的单元格引用中包含值“ Hello”，则“ F”参数将毫无问题地接受该引用。

您需要做的是尝试在非常仔细地考虑要传入的参数类型的同时调用UDF。一次更改一个参数，然后看看至少迫使您调用UDF的参数。您可以设置一个断点，或者让您的UDF返回“成功”之类，仅用于调试目的。现在的关键只是弄清楚什么是正确的参数类型。我有一种感觉，如果您这样做，将很快找出问题所在。

实际上，我认为我可能会看到问题：

我对您的“整数”参数有些怀疑，它们是参数的“ A”，“ D”和“ H”。如果直接将整数值传递给函数，则这些参数应该可以正常工作。但是，如果您传递单元格引用，则Excel将自动自动传递该单元格的Range.Value-这就是您想要的...

...但是问题是Excel单元格永远不能保存'Integer'数据类型！它们当然可以保存“整数”值，例如0、1、2，-1等，但是当由单元格保存时，它们实际上被键入为“ Double”。为了证明这一点，您可以将一个Integer分配给Cell.Value，但是它将存储为Double-如果您通过TypeName（）或.GetType（）。ToString（）检查Cell.Value持有的数据类型，它将返回“ Double”。

单元格可以保存布尔值，字符串，双精度型，日期，货币（在.NET中转换为十进制）和CVErr值，仅此而已。在标准用法下，它们不能返回“整数”。 （如果通过C ++ XLL直接访问XLOPER，那么从技术上讲，您可以访问整数值，但这是非常不标准的，并且不能从VB.NET或C＃中执行。）

因此，我将首先关注这些Integer参数，尤其是在直接传递Integer值与传递具有数字值的单元格引用方面。我猜测非可选的'A'参数可能能够接受单元格引用-但也许不确定，但我不确定-但我什至更不确定可选参数是否可以接受单元格引用持有Double数据类型。

因此，总的来说，我在这里猜测，但是我认为您应该能够从一组有效的参数开始，然后一个个地更改每个参数，以查看哪个调用对每个参数有效或无效。

祝你好运，休，让我们知道这是怎么回事...

麦克风