Microsoft KB Archive/285337

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

Article ID: 285337

Article Last Modified on 1/29/2007

-

APPLIES TO


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

-



This article was previously published under Q285337



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.



Create the Sample Automation Add-In
 In Visual Basic, start a new AddIn project. 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. On the Project menu, click MyAddin Properties. Change the Project Name to &quot;AutomationAddin&quot; and then click OK. In the Project Explorer, select the Connect designer. Change its Name property to &quot;XLFunctions&quot;. 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.</li> Change the Initial Load Behavior setting to Load on demand.</li></ul> </li>  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 &quot;kernel32&quot; 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 = &quot;The sum of &quot; & Num1 & &quot; and &quot; & Num2 & &quot; is &quot; & _ 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 = &quot;The sum of &quot; & Num1 & &quot; and &quot; & Num2 & &quot; is &quot; Else Sum = Sum + CDbl(Num3) sMsg = &quot;The sum of &quot; & Num1 & &quot;, &quot; & Num2 & &quot; and &quot; & Num3 & &quot; is &quot; 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 = &quot;The sum is &quot; & 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   '   &quot;array formulas&quot;). '  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) = &quot;r&quot; & r + 1 & &quot;c&quot; & 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 = &quot;The sum is &quot; & Sum Exit Function Handler: GetArray = CVErr(2036) 'xlErrNum = 2036 End Function

</li> Build the Add-in as AutomationAddin.dll.</li></ol>

Using the Sample Automation Add-in in Microsoft Excel 2002 or Microsoft Office Excel 2003
<ol> 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.</li> 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.</li> 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.</li> In cells B1 and B2 of the new workbook, type the numeric values 2 and 5, respectively.</li> In cell B3, type the following formula:

=Add1(B1, B2)

and press ENTER. The formula returns &quot;The sum of 2 and 5 is 7&quot;.</li> In cell B4, type the following formula:

=Add2(B1, B2)

and press ENTER. The formula returns &quot;The sum of 2 and 5 is 7&quot;. Modify the formula to use the third optional argument:

=Add2(B1, B2, 10)

The formula returns &quot;The sum of 2, 5, and 10 is 17.&quot;</li> In cell B5, type the following formula:

=Add3(1,2,3,4,5,6)

and press ENTER. The formula returns &quot;The sum is 21&quot;. 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.</li> In cell B6, type the following formula:

=Add1(&quot;x&quot;,&quot;y&quot;)

and press ENTER. The formula returns #NUM! (xlErrNum) because the type conversion with the CDbl function fails converting the strings &quot;x&quot; and &quot;y&quot; 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.)</li> 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.</li> 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 &quot;The sum is n&quot; (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 &quot;The sum is 10&quot;.</li></ol>

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.

<div class="references_section">