How to host a User Defined Function for MS-Excel with the VSTO-Excel Add-in using a COM server

• Download source code - 28.4 KB

Introduction

When using the Visual Studio Template Excel add-in, you have the wonderful opportunity to apply your own commandbars and user defined worksheet functions (UDFs). On the web, we can find several choices for how to add UDFs created with .NET to Excel. I would like to introduce to you my variant which I developed from several articles listed in the end of this article. (Thanks to all those great guys!)

How to set up the project?

I used VS 2008 and chose the Excel 2003 add-in. Edit the project compile options and choose "Register for COM-Interop". The source code should now look like this:

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

You should now add a class like this one (replace the GUID with a new one!). The most important is the setup section, because this makes the class COM-Interop visible! In your class, you can now add as many functions as you want to. I added two functions: HelloWorld, and a function to show how to handle array formulas in 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

Having added this class to our project, we now have to activate the automation add-in in 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

Compile and Use in MS-Excel

If you now hit F5, the program will be compiled and Excel will be started. In the Addin-Browser, you can now check if your COM-Interop class has been registered. You can now easily add the workbook functions by adding a function to any cell =HelloWorld.

These links inspired me to develop this

• Govert's article, thanks Govert. You inspired me the most!
• Paul Stubbs article