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

MORE INFORMATION

By setting the Object property of your COM Add-in, you can enable VBA code in Microsoft Office applications to access the public functions of the COM Add-in by way of the Addins collection of the application. The following steps demonstrate how to:

• 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.

The COM Add-in is registered for use with Microsoft Excel when you build it. Since you specified Startup for the Initial Load Behavior, the COM Add-in automatically loads when you start Microsoft Excel. The following steps illustrate how to create a VBA wrapper in an Excel Add-in (xla) for the MyFunction function in 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.

The next time you start Microsoft Excel, both the COM Add-in and the Excel Add-in (Wrap.xla) load. The following formula entered in any cell calls the function in the COM Add-in and returns a value of 255:

=MyFuncWrapper(2000, 18, 7.5)