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

Article translations Article translations
Article ID: 256624 - View products that this article applies to.
This article was previously published under Q256624
Expand all | Collapse all

SUMMARY

Microsoft Excel 2000 cannot call a function directly in a COM Add-in from a worksheet cell formula. However, you can create a Visual Basic for Applications (VBA) wrapper for the COM Add-in function so that the function may be called indirectly. This article provides more details on this technique and illustrates by example.

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)
				

REFERENCES

238228 HOWTO: Build an Office 2000 COM Add-In in Visual Basic
For more information on Microsoft Office Integration and Extensibility, please see the Product Support Services (PSS) page on the following Microsoft Web site:
http://support.microsoft.com/ofd

Properties

Article ID: 256624 - Last Review: August 23, 2005 - Revision: 3.3
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
Keywords: 
kbautomation kbhowto KB256624

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com