HOWTO: Use a COM Add-In Function as an Excel Worksheet Function

• Build a COM Add-in for Microsoft Excel that sets the Object property of the Add-in and exposes a public function.
• Build an Excel Add-in (xla) that calls the public function in the COM Add-in.
• Call the COM Add-in function indirectly from a formula in a worksheet cell.

Steps to Create the COM Add-In

1. Start a new AddIn project in Visual Basic 6.0.
2. Add a reference to the Microsoft Office 9.0 and Microsoft Excel 9.0 Object Libraries.
3. On the Project Explorer, open the Forms folder, and then remove frmAddin from the project.
4. On the Project Explorer, open the Designers folder, and then double-click the Connect Addin Designer.
5. On the General tab of the designer, change the Application to Microsoft Excel, and then change the Initial Load Behavior to Startup.
6. On the View menu, click Code.
7. Replace all of the code in the Connect code module with the following:

   Option Explicit
   
   Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
     ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
     ByVal AddInInst As Object, custom() As Variant)
      On Error Resume Next
      ' Set the object property for the instance of the add-in
      AddInInst.object = Me
   End Sub
   
   ' Add your own public function:
   Public Function MyFunction(nNum1 As Double, _
          nNum2 As Integer, nNum3 As Double) As Double
     ' Some Custom Calculation
     MyFunction = (nNum1 * (nNum2 / 12) * nNum3) / 100
   End Function
   
   					

8. On the File menu, click Make MyAddIn.dll to build the COM Add-in.

Steps to Create Microsoft Excel Add-In

1. Start a new workbook in Microsoft Excel.
2. Press the ALT+F11 keys to open the Visual Basic Editor.
3. In the Project Explorer, right-click VBAProject for the new workbook (Book1 by default), click Module, and then select Insert to add a new code module.
4. Paste the following VBA function into the code module:

   Public Function MyFunctionWrapper(nNum1 As Double, _
          nNum2 As Integer, nNum3 As Double) As Double
      Dim oAdd As Object
      Set oAdd = Application.COMAddIns.Item("MyAddin.Connect").Object
      MyFunctionWrapper = oAdd.MyFunction(nNum1, nNum2, nNum3)
   End Function
   					

5. Close the VBA Editor to return to Excel.
6. On the File menu, choose SaveAs. In the Save As Type dropdown, select Microsoft Excel Add-in (*.xla). Type the file name wrap.xla, and then click Save.
7. Close the workbook.
8. On the Tools menu, click Add-ins. Check the Wrap add-in in the list, and then click OK.
9. Quit Microsoft Excel.

=MyFuncWrapper(2000, 18, 7.5)