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

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 Explicit
   Private Declare Function GetTickCount Lib "kernel32" () As Long
   
   Dim oApp As Object  'The Excel Application object
   
   Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
      Exit Sub
   End Sub
   
   Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
      Set oApp = Application
   End Sub
   
   Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
      Set oApp = Nothing
   End Sub
   
   Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
      Exit Sub
   End Sub
   
   Public Function TickCount() As Long
      '----------------------------------------------------------------------
      '** A volatile function that is called each time the sheet is calculated.
      '   Call with =TICKCOUNT().
      '----------------------------------------------------------------------
      oApp.Volatile
      TickCount = GetTickCount
   End Function
   
   Public 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 = 2036
   End Function
   
   Public 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 Function
   Handler:
       Add2 = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   Public 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 Function
   Handler:
       Add3 = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   Public 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 Function
   Handler:
       ReturnArray = CVErr(2015)  'xlErrValue = 2015
   End Function
   
   Public 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 Function
   Handler:
       GetArray = CVErr(2036)  'xlErrNum = 2036
   End 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:
   =TickCount()
   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:
   =Add3(1,2,3,4,5,6)
   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:
   =Add1("x","y")
   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:
   =ReturnArray(5,3)
   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:
    =GetArray(I1:J3)
    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:
    =GetArray({1,2,3,4})
    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: For more information, see the following Microsoft Web site:

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