How To Create a Visual Basic Automation Add-in for Excel Worksheet Functions

This article was previously published under Q285337
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel 2000, you cannot call a function directly in a Component Object Model (COM) Add-in from a worksheet cell formula. Instead, you must create a Visual Basic for Applications (VBA) wrapper for the COM Add-in function so that the function can be called indirectly.

Integration of COM Add-ins (called Automation Add-ins) in Excel 2002 and later has been enhanced such that COM Add-in functions may now be called directly from a worksheet formula without the need for a VBA wrapper. This article illustrates how to create an Automation Add-in with Visual Basic that exposes functions that can be called from Excel 2002 and later worksheet formulas.

Create the Sample Automation Add-In

  1. In Visual Basic, start a new AddIn project.
  2. By default, a form named frmAddIn is added to the project. For the purposes of this demonstration, that form may be removed from the project. In the Project Explorer, right-click the form, and then click Remove frmAddIn on the shortcut menu.
  3. On the Project menu, click MyAddin Properties. Change the Project Name to "AutomationAddin" and then click OK.
  4. In the Project Explorer, select the Connect designer. Change its Name property to "XLFunctions".
  5. In the Project Explorer, double-click the XLFunctions designer. On the General tab, make the following changes to the designer settings:
    • From the Application list, select Microsoft Excel.
    • From the Application Version list, select Microsoft Excel 10.0.
      Note: When you are using Microsoft Office Excel 2003, select Microsoft Excel 11.0 from the Application Version list.
    • Change the Initial Load Behavior setting to Load on demand.
  6. With the XLFunctions designer still open, select Code from the View menu. Replace the code in the module with the following:
    Option ExplicitPrivate Declare Function GetTickCount Lib "kernel32" () As LongDim oApp As Object  'The Excel Application objectPrivate Sub AddinInstance_OnAddInsUpdate(custom() As Variant)   Exit SubEnd SubPrivate Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)   Set oApp = ApplicationEnd SubPrivate Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)   Set oApp = NothingEnd SubPrivate Sub AddinInstance_OnStartupComplete(custom() As Variant)   Exit SubEnd SubPublic Function TickCount() As Long   '----------------------------------------------------------------------   '** A volatile function that is called each time the sheet is calculated.   '   Call with =TICKCOUNT().   '----------------------------------------------------------------------   oApp.Volatile   TickCount = GetTickCountEnd FunctionPublic Function Add1(Num1 As Variant, Num2 As Variant) As Variant   '----------------------------------------------------------------------    '** A function with two required arguments.    '   Can be called with formulas such as =Add1(1,3) or =Add1(A1,A2).   '----------------------------------------------------------------------    On Error Resume Next    Add1 = "The sum of " & Num1 & " and " & Num2 & " is " & _        CDbl(Num1) + CDbl(Num2)    If Err <> 0 Then Add1 = CVErr(2036)  'xlErrNum = 2036End FunctionPublic Function Add2(Num1 As Variant, Num2 As Variant, Optional Num3 As Variant) As Variant   '----------------------------------------------------------------------   '** A function with two required arguments and a third optional argument.   '   Can be called with formulas such as =Add2(1,2), =Add2(A1,A2,A3).   '----------------------------------------------------------------------    Dim Sum As Double, sMsg As String    On Error GoTo Handler    Sum = CDbl(Num1) + CDbl(Num2)    If IsMissing(Num3) Then        sMsg = "The sum of " & Num1 & " and " & Num2 & " is "    Else        Sum = Sum + CDbl(Num3)        sMsg = "The sum of " & Num1 & ", " & Num2 & " and " & Num3 & " is "    End If    Add2 = sMsg & Sum    Exit FunctionHandler:    Add2 = CVErr(2036)  'xlErrNum = 2036End FunctionPublic Function Add3(ParamArray Nums()) As Variant   '----------------------------------------------------------------------   '** Demonstrates a function with a variable number of arguments.   '   Can be called with formulas like =Add3(1), =Add3(1,2,3,4),   '   or =Add3(A1,A2).   '----------------------------------------------------------------------    Dim Sum As Double, i As Integer    On Error GoTo Handler    For i = 0 To UBound(Nums)        Sum = Sum + CDbl(Nums(i))    Next    Add3 = "The sum is " & Sum    Exit FunctionHandler:    Add3 = CVErr(2036)  'xlErrNum = 2036End FunctionPublic Function ReturnArray(nRows As Long, nCols As Long) As Variant   '----------------------------------------------------------------------   '** Demonstrates how to return an array of values (for use in Excel   '   "array formulas").   '   Can be called with a formula such as =ReturnArray(1,3).   '----------------------------------------------------------------------    On Error GoTo Handler    ReDim a(0 To nRows, 0 To nCols) As Variant    Dim r As Long, c As Long    For r = 0 To nRows - 1        For c = 0 To nCols - 1            a(r, c) = "r" & r + 1 & "c" & c + 1        Next c    Next r    ReturnArray = a    Exit FunctionHandler:    ReturnArray = CVErr(2015)  'xlErrValue = 2015End FunctionPublic Function GetArray(Nums As Variant) As Variant   '----------------------------------------------------------------------   '** Demonstrates how to use an array(or range of multiple cells) as   '   a function argument.   '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),   '   or GetArray({1,2,3;4,5,6}).   '----------------------------------------------------------------------    Dim Sum As Double, v As Variant    On Error GoTo Handler    If IsArray(Nums) Then        For Each v In Nums            Sum = Sum + CDbl(v)        Next    Else        Sum = CDbl(Nums)    End If    GetArray = "The sum is " & Sum    Exit FunctionHandler:    GetArray = CVErr(2036)  'xlErrNum = 2036End Function					
  7. Build the Add-in as AutomationAddin.dll.

Using the Sample Automation Add-in in Microsoft Excel 2002 or Microsoft Office Excel 2003

  1. Start Microsoft Excel 2002 if you have selected Microsoft Excel 10.0 in the Application Version list of the Visual Basic Add-in project.
    Note: Start Microsoft Office Excel 2003 if you have selected Microsoft Excel 11.0 in the Application Version list of the Visual Basic Add-in project.
  2. On the Tools menu, click Add-ins to display the Add-In Manager dialog box. Click Automation, select AutomationAddin.XLFunctions in the list, and then click OK. Select AutomationAddin.XLFunctions, and then click OK to close the Add-in Manager dialog box.
  3. In cell A1, type the following formula:
    The formula returns a large number that represents the number of milliseconds that have elapsed since your system was started.
  4. In cells B1 and B2 of the new workbook, type the numeric values 2 and 5, respectively.
  5. In cell B3, type the following formula:
    =Add1(B1, B2)
    and press ENTER. The formula returns "The sum of 2 and 5 is 7".
  6. In cell B4, type the following formula:
    =Add2(B1, B2)
    and press ENTER. The formula returns "The sum of 2 and 5 is 7". Modify the formula to use the third optional argument:
    =Add2(B1, B2, 10)
    The formula returns "The sum of 2, 5, and 10 is 17."
  7. In cell B5, type the following formula:
    and press ENTER. The formula returns "The sum is 21". Because the parameter for the Add3 function is declared as ParamArray, you can use a variable number of arguments for this function.

    Note Excel limits a single worksheet function to 29 arguments.
  8. In cell B6, type the following formula:
    and press ENTER. The formula returns #NUM! (xlErrNum) because the type conversion with the CDbl function fails converting the strings "x" and "y" to type double. The default return value when a function encounters a run-time error is #VALUE!. If you wish to return a different error, use error handling and you can return any of the Excel built-in error values (xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, or xlErrValue.)
  9. Select cells E1:G5. Type the following formula:
    and press CTRL+SHIFT+ENTER to enter the formula as an array formula. The function returns a 5 x 3 array of unique values.
  10. Enter any numbers into cells I1:J3. In cell I4, type the following formula:
    The formula will return a result similar to "The sum is n" (where n is the sum of the numbers in I1:J3). In I5, type the following formula:
    and press ENTER. The formula returns "The sum is 10".

Function Binding

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 will take precedence. 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, it is recommended that you do not give your functions names that are already in use by Excel built-in functions.

To specifically call a function in an Automation Add-in, you can drill down to the function in a formula by using a syntax such as ServerName.ClassName.FunctionName(...). For example, to call the Add1 function in the sample, you could use the following:
You can only call top-level methods and properties of your Automation Add-in; you cannot walk down the object model of your Add-in to call functions that are not at the top level.

Volatile Functions

A volatile function is one that recalculates whenever a change is made to any cell on a worksheet, regardless of whether or not the changed cell is a dependency for the function. One example of a volatile function that is internal to Excel is the RAND() function. Volatile functions also recalculate when you press F9.

To make a function in an Automation Add-in volatile, call the Volatile method of the Excel Application object. As the sample code above demonstrates, a reference to the Excel Application object may be retrieved during the OnConnection event of the Add-in. The TickCount function in the sample Automation Add-in is volatile. Note that if you make changes to any cell on the worksheet or you press F9, cells that contain the following formula:
will recalculate.

Automation Add-ins and the Add-in Manager

In the Add-in Manager, the default value of the HKEY_CLASSES_ROOT\<ProgID> registry key for the Automation Add-in is used for the Add-in's name. Note that there is no property in the Visual Basic user interface (UI) that will set the default value of this key; however, this key can be modified manually in the registry editor or during an installation for the Add-in.

The description for the Automation Add-in in the Add-in Manager will always be the Add-in's ProgID; there is no way to change this behavior.

Automation Add-ins and the Function Wizard

Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
256624 How To Use a COM Add-In Function as an Excel Worksheet Function
For more information, see the following Microsoft Web site:

(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.


Article ID: 285337 - Last Review: 12/05/2015 23:52:44 - Revision: 5.4

Microsoft Visual Basic 6.0 Professional Edition, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition

  • kbnosurvey kbarchive kbhowto KB285337