Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Automation Add-ins

When initially introduced in Excel 2000, the functions contained in COM Add-ins could not be called directly from a worksheet. In Excel 2002, Microsoft added Automation Add-ins to solve that problem. An automation add-in is nothing more than an ActiveX DLL that contains a public 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 the 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 Listing 21-6.

Listing 21-6. The IFERROR User-Defined Function

Public Function IFERROR(ByRef ToEvaluate As Variant, _
ByRef Default As Variant) As Variant
If IsError(ToEvaluate) Then
IFERROR = Default
Else
IFERROR = ToEvaluate
End If
End Function

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

Using the IfError Automation Add-in

Open Excel 2002, click the Tools > Add-ins menu and click 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:

Option Explicit
'Reference to the Excel application
Dim mxlApp As Excel.Application
'Called when the automation add-in is loaded
Private Sub AddinInstance_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As _
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)
'Store away a reference to the Application object
Set mxlApp = Application
End Sub
'Volatile function to return VB's Timer value
Public Function VBTimer() As Double
mxlApp.Volatile True
VBTimer = Timer
End Function
'Function to count how many items in Source lie between
'Min and Max
Public Function CountBetween(ByRef Source As Range, _
ByVal Min As Double, ByVal Max As Double) As Double
'If we get an error, return zero
On Error GoTo ErrHandler
'Count the items bigger than Min
CountBetween = mxlApp.WorksheetFunction _
.CountIf(Source, ">" & Min)
'Subtract the items bigger than Max, giving the number
'of items in between
CountBetween = CountBetween - mxlApp.WorksheetFunction _
.CountIf(Source, ">=" & Max)
Exit Function
ErrHandler:
CountBetween = 0
End Function

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.