PRB: Automation Add-In Function Binds to Excel Built-In Function with the Same Name

When you create an Automation Add-in that is targeted for Microsoft Excel and you insert a function from your add-in into an Excel worksheet by using the Insert Function dialog box, Excel binds to the built-in function instead of your custom function because the name of the function matches the name of an Excel built-in function. This also applies to custom functions in other add-ins.

There are two ways to avoid this problem:

• When you create Automation Add-ins, avoid using a function name that is the same as a built-in Excel function or other add-in function that is loaded.
• Enter the function directly into the cell, including the ProgID of the add-in.

Binding for functions in an Automation Add-in is at the end of the function binding precedence. If you have a function in your Automation Add-in with the same name as an Excel built-in function, the Excel built-in function takes precedence. Visual Basic for Automation (VBA) functions in workbooks and regular add-ins (.xla) also take precedence over Automation Add-in functions. When you create your own functions for use in Excel, Microsoft recommends that you do not name your functions with a name that is already in use by an Excel built-in function.

Steps to Reproduce Behavior

Create the Automation Add-Ins:

1. Start a new AddIn project in Visual Basic. By default, a form named frmAddIn is added to the project.
2. On the Project Explorer, right-click the form, and select Remove frmAddIn from the context menu to remove the form.
3. On the Project menu, select MyAddin Properties. Change the Project Name to aTest1, and then click OK.
4. On the Project Explorer, select the Connect designer. Change its Name property to XLFunctions.
5. Double-click the XLFunctions designer in the Project Explorer. Make the following changes to the designer settings on the General tab:
   
   - From the Application drop-down list, select Microsoft Excel.
   
   - From the Application Version drop-down list, select Microsoft Excel 2002.
   
   - Change the Initial Load Behavior to Load on demand.
6. With the XLFunctions designer still open, select Code on the View menu. Delete all the existing code in the module. Replace the code in the module with the following:

   Option Explicit
   
   Public Function SUM(Num1 As Variant, Num2 As Variant) As String
       On Error Resume Next
       Dim retVal As Double
       retVal = CDbl(Num1) + CDbl(Num2)
       SUM = "My Sum: " & retVal
   End Function
   
   Public Function DLLName() As String
       DLLName = App.EXEName
   End Function
   					

7. Save the project as aTest1.vbp.
8. Build the add-in as aTest1.dll.
9. Repeat steps 1 through 8 except now use aTest2 as the project name.
   
   Now there are two separate projects, aTest1 and aTest2.

Test the Automation Add-ins in Excel:

1. Start Microsoft Excel.
2. On the Tools menu, click Add-Ins to display the Add-Ins dialog box.
3. Click Automation, select aTest1.XLFunctions in the list, and then click OK. Check aTest1.XLFunctions, and then click OK to close the Add-in dialog box.
4. Go to cell A1 and, on the Insert menu, click Function. In the category drop-down list, select aTest1.XLFunctions. Then select the SUM function from the list box and click OK. Enter 10 for Num1 and enter 20 for Num2, and then click OK.RESULT: The Function Wizard used the built-in SUM function instead.
   
   
5. In cell A2, type the following formula: =aTest1.XLFunctions.SUM(10,20)
   
   RESULT: "My Sum: 30" is displayed in the cell. This is the SUM function from your Automation Add-in. However, note that the formula reads:
   
   =SUM(10,20) for cell A2.
   
   
6. Edit the formula in A2 to sum the numbers 10, 40.
   
   RESULT: Cell A2 is now bound to the built-in SUM function as a result of re-entering the formula. In order to use the custom SUM function, you need to include the ProgID of the add-in when you enter the formula: =aTest1.XLFunctions.SUM(10,40).
7. On the Tools menu, click Add-Ins to display the Add-Ins dialog box.
8. Click Automation, select aTest2.XLFunctions in the list, and then click OK. Check aTest2.XLFunctions, and then click OK to close the Add-ins dialog box.
9. Go to cell B1 and, on the Insert menu, click Function. In the category drop-down list, select aTest1.XLFunctions, select the DLLName function from the list box, and then click OK twice.
10. Go to cell B2 and, on the Insert menu, click Function. In the category drop-down list, select aTest2.XLFunctions, select the DLLName function from the list box, and then click OK twice.
    
    RESULT: Cell B2 is using the DLLName function from aTest1.dll, resulting in both cells B1 and B2 displaying aTest1.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: