Click here to Skip to main content
Click here to Skip to main content

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

, 10 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Auto-register COM-Interop functions and activate this add-in in MS-Excel, ready to use.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

felixLindemann

Germany Germany
No Biography provided

Comments and Discussions

 
QuestionThe self defined how to share the variable Pinmemberfong017-Sep-12 7:23 
GeneralGood Approach but ... Pinmemberjmptrader23-Jun-09 11:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411028.1 | Last Updated 10 Jun 2009
Article Copyright 2009 by felixLindemann
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid