如何使用 COM 服务器和 VSTO-Excel Add-in 托管 MS-Excel 的用户定义函数

• 下载源代码 - 28.4 KB

引言

在使用 Visual Studio 模板 Excel 加载项时，您有机会应用自己的命令栏和用户自定义工作表函数 (UDF)。在网上，我们可以找到几种将使用 .NET 创建的 UDF 添加到 Excel 的方法。我想向您介绍我的变体，它是从文章末尾列出的几篇文章中开发的。（感谢所有这些优秀的开发者！）

如何设置项目？

我使用了 VS 2008 并选择了 Excel 2003 加载项。编辑项目编译选项并选择“注册 COM 互操作”。源代码现在应该如下所示

Public Class ThisAddIn
    Private oExcel as Excel.Application
    Private Sub ThisAddIn_Startup(ByVal sender As Object, _
                  ByVal e As System.EventArgs) Handles Me.Startup
         
            Me.Application = _
    CType(Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
        GetType(Excel.Application), Me.Application), Excel.Application)
    oExcel  = me.application
    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _
                    ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

End Class

您现在应该添加一个像这样的类（用一个新的 GUID 替换它！）。最重要的是设置部分，因为它使类对 COM 互操作可见！在您的类中，您现在可以添加任意数量的函数。我添加了两个函数：HelloWorld，以及一个函数来演示如何在 Excel 中处理数组公式。

<Guid("ad55814e-4cef-4087-aab1-c123be605f8a")> _
<ClassInterface(ClassInterfaceType.AutoDual)> _
<ComVisible(True)> _
Public Class TestFunctions

    Public Function HelloWorld() As String
        Return "HelloWorld"
    End Function
    
      Public Function getSquares(ByVal Value As Excel.Range) As Object(,)
        Dim _return As Object(,) = Nothing
        Try
            Dim iMax As Integer = CInt(CType(Value(1, 1), Excel.Range).Value)

            ReDim _return(iMax, iMax)
            _return(0, 0) = ""
            For i As Integer = 1 To iMax
                _return(i, 0) = i
                For j As Integer = 1 To iMax
                    If i = 1 Then _return(0, j) = j
                    _return(i, j) = i * j
                Next
            Next

        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error GetSquares")
        End Try
        Return _return
    End Function

#Region " Setup "

    Public Sub New()

    End Sub

    <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("", 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 subKeyName As String) As String

        Dim s As New System.Text.StringBuilder()
        
        s.Append("CLSID\{")
        
        s.Append(type.GUID.ToString().ToUpper())
        
        s.Append("}\")        

        s.Append(subKeyName)
        
        Return s.ToString()
    End Function

#End Region

End Class

将此类添加到我们的项目中后，我们现在必须在 MS-Excel 中激活自动化加载项

Dim oExcel As Excel.Application
Private Sub ThisAddIn_Startup(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Startup

    Me.Application = _
    CType(Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
    GetType(Excel.Application), Me.Application), Excel.Application)

    RegisterComserver()

End Sub

Private Sub RegisterComserver()
    Try
        Dim strAddinName As String = "ExcelAddIn1.TestFunctions"
       
        oExcel.AddIns.Add(Filename:=strAddinName)
        oExcel.AddIns(strAddinName).Installed = True

    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

编译并在 MS-Excel 中使用

如果您现在按 F5，程序将被编译，Excel 将被启动。在 Addin-Browser 中，您可以检查您的 COM 互操作类是否已注册。您现在可以通过在任何单元格中添加函数 =HelloWorld 来轻松添加工作簿函数。

这些链接启发我开发了这个

• Govert 的文章，感谢 Govert。你给了我最大的启发！
• Paul Stubbs 的文章