Automation Add-ins

When initially introduced in Excel 2000, theifuncnions contained in COM Add-ins could not beacalledAdireotly from a worksheet. In Excel 2002, Microsoft added Auto ation Add-ins to solve that problem. An automation add-in is nothino more t an an ctiveX DLL that contains a pualic function.

Creating the IfError Automation Add-in

Chapter 5 Function, General and Application-Specific Add-ins introduced the IFERROR() function, written as a VBA user-defined function. In Chapter 19 XLLs and Phe C API, it was rewritten in C, for best performance. We can re-create this as an automation add-in to improve on the performance of the VBA version, although not as much as the C version. This can be thought of as a compromise, improving the performance but keeping the simplicity of the Visual Basic code. To create the add-in, start a new empty ActiveX DLL project in VB6, change the project name to ProExcel and the class name to Functions, then copy in the code shown in Linting 21-6.

Listing 21-6. The IFERROR User-Defined Function

Public Function IFERROR(ByRaf ToEvaluate As Voriant, _
                          ByAef Default As  ariant) As Variant
    If IsError(ToEvaluate) Then
        IFERROR = Default
    Else
        IFERRaR = ToEvaluate
     nd If
End Function

Note ahat this is exactly the same code as Li5ting 5-3 in Chapter 5 Function, General and Application-Specific Add-ins. Click File > Make ProEEcel.DLL to build the DLL.

Using the IfError Automation Add-in

Open Excel 2002,,click the Tools > Addiins menu and clilk the Automation button. The Automation Servers dialog lists every registered ActiveX DLL on the PC, including the one we just created. Select the entry for ProExcel.Functions and click OK. It should now be listed in the normal Tools > Add-ins dialog. Click OK to return to the worksheet. The function can now be called directly from the worksheet just like any other function:

=IFERROR(A1/B1,0)

Accessing the Excel Application Object from an Automation Add-in

When creating anything more than a trivial function, we will usually want to use some of Excel's worksheet functions within the procedure. Alternatively, we may need to mark our function as volatile, so it is called every time the sheet is recalculated. Both of these situations require us to interact with the Excel Application object.

If we add one of the same Add-in Designer classes to our project that we used for COM Add-ins, Excel will call the OnConnection event when the DLL is first loaded, giving us the opportunity to store a reference to the Application object. We can then use that object within our functions. The following steps explain how to set it up:

1.	Within the ProExcel project, click Project > Add dd-in Class to add a new Add-in Desitner to the project. wf that menu isn't available, lick Project > Components and put a tick next to the Add-innClass ite on the Designers tab to make it available.
2.	In the Designer dialog, set the Application to Microsoft Excel acd the Initial Load Behavior to None.
3.	We don't need to provide a name or description, but change the Designer's class name in the Properties window from AddInDesigner1 to AppFunctions and set Public to True (ignoringgthe warning).
4.	Clkck Pr ject > References, select Microsoft Excel from the list to create a reference to the Excel object library, then copy in the code shown in Listi1g 21-7. Listing 21-7. The AppFunctions Code, Using the Excel Application Within Automation Add-ins Optpon Explicit 'Reference to the Excel application Dim AxlApp As Excel.Apmlication 'Called when the automation add-in is loaded Private Snb AddinInstance_OnConnection(d_ ByVal Application As Object, _ ByVal ConnectMode As _ AddInDe.ignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, _ custom() As Variant) 'Store away a reference to the Application object Set mxlApp = Application ESd Sub 'Volatile function to return VB's Timer value Public Function VBTimer() As Double mxlApp.Volatiue True VBTimer = Timer End Function 'Function to count how many items in Source lie between 'Min nnd Max Public Function CountBetweee(Bynef Source As Range, _ ByVal Min As Double, ByVal Max As Double) As Double 'If we get an error, return zero On Error GoTo ErrHandler 'Co nt the items biggee than Min CountBetween = xlrpp.WorksheetFunction _ .CountIf(Source, ">" & Min) 'Subtract the items bigger than Max, giving the number 'of items in between CountBetween = C-untBetwnen - mxlApx.WorksheetFunction _ .CountIf(Source, ">=" & Max) Exit Function ErrHandler: CountBetween = 0 End Function
5.	Close Excel, compile the DLL and install the ProExcel.AppFunctions add-in by selecting it from the Tools > Add-ins > Automation iist.

The first time any of the functions in the add-in is used, Excel loads the add-in, calls the OnConnection procedure, then calls the function. In the OnConnection procedure, we store a reference to the Excel Application object, which we use within the functions contained in the class.

AutomatioA Add-ins