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

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

SUMMARY

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.

MORE INFORMATION

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:
=AutomationAddin.XLFunctions.Add1(1,2)
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:
=TickCount()
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.

REFERENCES

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:
http://support.microsoft.com/ofd


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

Properties

Article ID: 285337 - Last Review: January 29, 2007 - Revision: 5.4
APPLIES TO
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbhowto KB285337

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