To learn the basics start with a really simple example and once you know how to make that work then you can start the more complex implementation you outlined above. There are lots of examples out there, here's once :
Create a new Class Library project
Make the Class COM-Visible:
In the class that you want to expose, add the
ComClass
attribute to make it COM-visible.
Imports System.Runtime.InteropServices
<ComClass(MyClass.ClassId, MyClass.InterfaceId, MyClass.EventsId)>
Public Class MyClass
Public Const ClassId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
Public Const InterfaceId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
Public Const EventsId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
End Class
Note: You need to replace the ClassId, InterfaceId, and EventsId with your own GUIDs. You can generate these GUIDs using Visual Studio's "Create GUID" tool.
Write Methods and Properties:
Add the methods and properties you want to expose through COM in your class.
Public Function AddNumbers(ByVal a As Integer, ByVal b As Integer) As Integer
Return a + b
End Function
Register for COM Interop:
In the project properties, go to the "Compile" tab and check the "Register for COM Interop" option.
Build the Project
Excel Add-In Configuration:
In Excel, go to "Developer" -> "Excel Add-ins" -> "Browse" and select the DLL file generated in the bin folder of your project.
Testing:
Now you can use your class library from Excel as an add-in. In a cell, you can use a formula like
=MyClass.AddNumbers(3, 4)
to get the sum.