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
