Microsoft KB Archive/256624
Article ID: 256624
Article Last Modified on 8/23/2005
- Microsoft Excel 2000 Standard Edition
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
This article was previously published under Q256624
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.
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
- Start a new AddIn project in Visual Basic 6.0.
- Add a reference to the Microsoft Office 9.0 and Microsoft Excel 9.0 Object Libraries.
- On the Project Explorer, open the Forms folder, and then remove frmAddin from the project.
- On the Project Explorer, open the Designers folder, and then double-click the Connect Addin Designer.
- On the General tab of the designer, change the Application to Microsoft Excel, and then change the Initial Load Behavior to Startup.
- On the View menu, click Code.
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
- 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
- Start a new workbook in Microsoft Excel.
- Press the ALT+F11 keys to open the Visual Basic Editor.
- 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.
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
- Close the VBA Editor to return to Excel.
- 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.
- Close the workbook.
- On the Tools menu, click Add-ins. Check the Wrap add-in in the list, and then click OK.
- 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)
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:
Additional query words: wrap
Keywords: kbhowto kbautomation KB256624