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.
By default, a form named frmAddIn is added to the project.For the purposes of this demonstration, that form may be removed from theproject. In the Project Explorer, right-click the form, and then clickRemove frmAddIn on the shortcut menu.
On theProject menu, clickMyAddin Properties. Change theProject Name to "AutomationAddin" and then clickOK.
In the Project Explorer, select the Connect designer.Change itsName property to "XLFunctions".
In the Project Explorer, double-click theXLFunctions designer. On theGeneral tab, make the following changes to the designer settings:
From theApplication list, selectMicrosoft Excel.
From theApplication Version list, selectMicrosoft Excel 10.0. Note: When you are using Microsoft Office Excel 2003, selectMicrosoft Excel 11.0 from theApplication Version list.
Change theInitial Load Behavior setting toLoad on demand.
With the XLFunctions designer still open, selectCode from theView 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
Build the Add-in as AutomationAddin.dll.
Using the Sample Automation Add-in in Microsoft Excel 2002 or Microsoft Office Excel 2003
Start Microsoft Excel 2002 if you have selectedMicrosoft Excel 10.0 in the Application Version list of the Visual Basic Add-in project. Note: Start Microsoft Office Excel 2003 if you have selectedMicrosoft Excel 11.0 in the Application Version list of the Visual Basic Add-in project.
On theTools menu, clickAdd-ins to display theAdd-In Manager dialog box. ClickAutomation, selectAutomationAddin.XLFunctions in the list, and then clickOK. SelectAutomationAddin.XLFunctions, and then clickOK to close theAdd-in Manager dialog box.
In cell A1, type the following formula:
=TickCount()
The formula returns a large number that represents the number ofmilliseconds that have elapsed since your system was started.
In cells B1 and B2 of the new workbook, type the numericvalues 2 and 5, respectively.
In cell B3, type the following formula:
=Add1(B1, B2)
and press ENTER. The formula returns "The sum of 2 and 5 is7".
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."
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 theparameter for theAdd3 function is declared asParamArray, you can use a variable number of arguments for this function.
Note Excel limits a single worksheet function to 29arguments.
In cell B6, type the following formula:
=Add1("x","y")
and press ENTER. The formula returns #NUM! (xlErrNum) because the type conversion with theCDbl function fails converting the strings "x" and "y" to typedouble. The default return value when a function encounters a run-timeerror is #VALUE!. If you wish to return a different error, use error handlingand you can return any of the Excel built-in error values (xlErrDiv0,xlErrNA,xlErrName,xlErrNull,xlErrNum,xlErrRef, orxlErrValue.)
Select cells E1:G5. Type the following formula:
=ReturnArray(5,3)
and press CTRL+SHIFT+ENTER to enter the formula as an arrayformula. The function returns a 5 x 3 array of unique values.
Enter any numbers into cells I1:J3. In cell I4, type thefollowing formula:
=GetArray(I1:J3)
The formula will return a result similar to "The sum isn" (wheren is the sum of the numbers in I1:J3). In I5, type the followingformula:
=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 asServerName.ClassName.FunctionName(...). For example, to call theAdd1 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 theRAND() function. Volatile functions also recalculate when you press F9.
To make a function in an Automation Add-in volatile, call theVolatile method of the ExcelApplication object. As the sample code above demonstrates, a reference to the ExcelApplication object may be retrieved during the OnConnection event of the Add-in. TheTickCount 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.
Back to the top
