Automation Add Ins

Automation Add-Ins are COM DLLs (ActiveX DLLs) that have a creatable class and a public function in the creatable class. For example, when Excel can do the following with your class, you can then call FunctionName from the worksheet:

Dim oAutoAddin As Object

Set oAutoAddin = CreateObject("TheProgID") TheResult = CallByName(oAutoAddin, "FunctionName", _

words, your function must satisfy the following conditions:

The class must be publicly creatable (that is, have an Instancing property of Multi-Use or Global-Multi-Use).

The procedure must be a Function (as opposed to a Sub or Property procedure).

A Simple Add-In — Sequence

For the Excel VBA developer, the easiest way to create Automation Add-Ins is still to use Visual Basic 6. This is because it uses the same syntax as VBA, and most Excel user-defined functions can be converted to Automation Add-Ins by simply copying the code to a VB6 class. Although it is possible to create Automation Add-Ins using .NET, there are a number of hoops to jump through, and creating Add-Ins with .NET is outside the scope of this book.

In this example, you create a simple Automation Add-In using VB6. It will contain a single function to provide a sequence of numbers as an array (which is often used in array formulas).

Start VB6 and create a new ActiveX DLL project. Rename the project Excel2 007ProgRef and rename the class Simple in the Properties window. Set the class's Instancing property to 5-MultiUse (this should be the default setting). By setting this property, you're making the class publicly creatable (that is, Excel can create instances of this class when or if you tell it to).

Type the following code into the Simple class, to calculate and return a sequence of numbers:

***************************************************************************

* FUNCTION NAME: Sequence

* DESCRIPTION: Returns a sequence of numbers, often used in array formulas.

* PARAMETERS: Items The number of elements in the sequence

* Start The starting value for the sequence, default = 1

* Step The step value in the sequence, default = 1

***************************************************************************

Public Function Sequence(Items As Long, Optional Start As Double = 1, _

Optional Step As Double = 1) As Variant

Dim vaResult As Variant

Dim i As Long

Dim dValue As Double

' Validate entries If Items < 1 Then

Sequence = CVErr(2015) '#Value

Exit Function End If

' Create an array for the series ReDim vaResult(1 To Items)

' Get the initial value dValue = Start

' Calculate all the values, populating the array For i = 1 To Items vaResult(i) = dValue

dValue = dValue + Step

Next

' Return the array Sequence = vaResult

End Function

By defining the function to be Public, Excel will be able to see it and you'll be able to call it from the worksheet. Save the project, then use File O Make Excel2007ProgRef.dll to create the DLL—you've just created an Automation Add-In.

Registering Automation Add-Ins with Excel

Before you can use the Sequence function in a worksheet, you need to tell Excel about the DLL. Microsoft has extended the Add-Ins paradigm to include Automation Add-Ins, making their usage extremely similar to normal Excel xla or xlam Add-Ins. The main difference is that instead of a filename, Automation Add-Ins use the class's ProgID, which is the Visual Basic Project name, a period, then the class name. In this example, the ProgID of the Simple class is Excel2007ProgRef.Simple.

Through the Excel User Interface

To load an Automation Add-In through Excel's dialog, do the following:

1. Click Office Menu O Excel Options O Add-Ins to show the Add-Ins options.

2. Select Excel Add-Ins in the Manage drop-down and click the Go button to show the Add-Ins dialog.

3. Click the Automation button to show the Automation Add-Ins dialog.

4. Select the entry for Excel2 007ProgRef.Simple in the list, and click OK to return to the Add-Ins dialog.

You should see that the Automation Add-In is now included in the list of known Add-Ins and you can load or unload it by checking or unchecking the checkbox—just like any other Add-In.

Using VBA

Automation Add-Ins are loaded in the same way as normal xla Add-Ins, but using the ProgID instead of the filename, as in the following code:

Sub InstallAutomationAddIn()

Addlns.Add Filename:="Excel2007ProgRef.Simple" AddIns("Excel2007ProgRef.Simple").Installed = True End Sub

In the Registry

If you are creating an installation routine for your Add-In, you may want to write directly to the registry in order to set the Automation Add-In as installed. To do so, you need to create the following registry entry (which will already exist if you've used the previous technique).

In the registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

Create the string value:

Name =

the

first unused item

in the series: Open, Openl, Open2, Open3, Open4, Open5

etc.

Value

= /A

"Excel2007ProgRef.

.Simple"

If you want to add the Automation Add-In to the list shown in the Add-Ins dialog, but not have it installed, create the following registry key instead:

HKEY_CURRENT_USER\Software\Microsoft\0ffice\12.0\Excel\Add-In Manager

In this registry key, create an empty string value with the Name = Excel2 007ProgRef.Simple.

Using Automation Add-Ins

Like normal xlam Add-Ins, the functions contained in Automation Add-Ins can be used both in the worksheet and within VBA routines.

in the Worksheet

Once installed, you can simply type the name of the function directly into the worksheet. To test it, start Excel 2007, install the Add-In as shown in the previous section, select a horizontal range of 5 cells, type the function =Sequence(5,10,2), and enter the function as an array formula by pressing Shift+Ctrl+ Enter. You should see a sequence of numbers, as shown in Figure 18-1.

Figure 18-1

If the function name in the Automation Add-In conflicts with a built-in Excel function or a function defined in a normal Excel Add-In, Excel will use the first one that it finds in the order of preference:

1. Built-in function

2. Function in xla or xlam Add-In

3. Function in Automation Add-In

To force Excel to use the function from the Automation Add-In, prefix the function name by the Add-In's ProgID when typing it in, as in:

=Excel2007ProgRef.Simple.Sequence(5,10,2)

As soon as you enter the function, Excel will remove the ProgID, but will still use it to reference the function correctly. This will probably cause some confusion if you subsequently edit the function, because you must remember to re-enter the ProgID each time (or Excel will think it is the built-in function).

In VBA

There are a number of alternatives for using the function in VBA. Because the DLL is a simple ActiveX DLL, you can create your own instance of it and use the function directly. This will work regardless of whether it is installed as an Add-In, as long as you have a reference to the Excel2007ProgRef library:

Private Sub CommandButton1_Click()

' Assumes a reference has been created to the Excel2007ProgRef library,

' using Tools | References

Dim oSimple As Excel2007ProgRef.Simple

Dim vaSequence As Variant

' Create our own instance of the class Set oSimple = New Excel2007ProgRef.Simple

' Get the sequence vaSequence = oSimple.Sequence(5, 10, 2)

' Write the sequence to the sheet ActiveCell.Resize(1, 5) = vaSequence End Sub

If you know that the Add-In is installed, you can use the instance that Excel has created, by using Application.Evaluate:

Private Sub CommandButton1_Click()

Dim vaSequence As Variant

'Use Application.Evaluate - which doesn't require a reference to the DLL vaSequence = _

Application.Evaluate("Excel2007ProgRef.Simple.Sequence(5,10,2)") 'Or the shorthand:

'vaSequence = [Excel2007ProgRef.Simple.Sequence(5,10,2)]

'Write the sequence to the sheet ActiveCell.Resize(1, 5) = vaSequence End Sub

When using Application.Evaluate, the full ProgID is only needed if there is a risk that the function name conflicts with a built-in function, or one in a loaded xla or xlam Add-In (or workbook), so the following works equally well:

vaSequence = Application.Evaluate("Sequence(5,10,2)")

It is generally safer and more robust to use the first method — creating and using your own instance of the class.

Introducing the IDTExtensibility2 Interface

The simple Add-In shown in the previous section is simple for one reason—it's self-contained and doesn't need to use Excel at all. In most real-world examples, you will want to use the Excel Application object in a number of ways:

□ Use Application.Caller to identify the range that the function was called from

□ Use Application.Volatile to mark an Automation Add-In function as volatile, and hence that Excel should call the function every time it recalculates the worksheet

□ Use Excel's built-in functions within your Add-In

To use the Excel Application object within your Automation Add-In, you need to get (or be given) a reference to it, which you can store in a private variable within your Add-In class. This is achieved by implementing a specific interface within your Add-In class.

An interface is simply a predefined and fixed set of sub procedures, functions, and properties. Implementing an interface means that you are including all those predefined sub procedures, functions, and properties within your class. By doing this you are providing fixed, known, and predictable entry points through which Excel can call into your class.

When Excel loads an Automation Add-In, it checks to see if the Add-In has implemented an interface called IDTExtensibility2. If that interface has been implemented, Excel calls the OnConnection method defined in the interface, passing a reference to itself (that is, to the Excel Application object). In VBA terms, Excel is doing something like the following (don't type this in):

Dim oIDT2 As IDTExtensibility2 Dim oAutoAddIn As Object

' Create an instance of the Automation Add-In

Set oAutoAddIn = Create0bject("Excel2007ProgRef.Simple")

' Does it implement the special interface? If TypeOf oAutoAddIn Is IDTExtensibility2 Then

' Yes it does, so get a reference to that ' interface within the Add-In class Set oIDT2 = oAutoAddIn

' And call the interface's OnConnection method, ' passing the Application oIDT2.0nConnection Me End If

Within the Add-In's class, you can respond to the call to OnConnection by storing the reference to the Application object in a class-level variable, and using it in the Add-In's functions.

The IDTExtensibility2 interface has five methods, each called at specific points in Excel's lifetime, though only two are used by Automation Add-Ins (the others are used by COM Add-Ins and are discussed later in the chapter). OnConnection has already been mentioned; the other method used here is OnDisconnection. Even though they are not used, you have to include code for every routine defined in the interface, as shown a bit later.

The first task in implementing an interface is to create a reference to the library in which the interface is defined. In this case, the IDTExtensibility2 interface is defined in the Microsoft Add-In Designer library.

Open the Excel2007ProgRef project in Visual Basic, select Project O References, and put a check mark next to the Microsoft Add-In Designer item. Because you're interacting with Excel, you also need a reference to the Excel object library, so find the entry for Microsoft Excel 12.0 Object Library and check that one too. Now select Project O Excel2007ProgRef Properties and select the Component tab. Choose Binary Compatibility and select the file Excel2 007ProgRef.dll that you created in the previous section (which might already be selected). This ensures that VB updates the current DLL registry entries rather than creating new ones each time you recompile.

Then add a new class module to the project, call it Complex, set its Instancing property to 5-MultiUse, and copy in the following code to implement the IDTExtensibility2 interface and respond to Excel calling its entry points:

' Implement the IDTExtensibility2 interface, so Excel can call into the class Implements IDTExtensibility2

' Declare a private reference to the Excel application Private moXL As Excel.Application

' Called by Excel when the class is loaded, passing a reference to the ' Excel object

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _

ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant)

' Set a reference to the Excel application, for use in the functions Set moXL = Application

End Sub

' Called by Excel when the class is unloaded, so destroy the reference ' to Excel

Private Sub IDTExtensibility2_OnDisconnection( _

ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)

Set moXL = Nothing End Sub

' Not used by Automation Add-Ins, but have to be included in the class to ' implement the interface

Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)

' Have a comment to stop VB removing the routine when doing its tidy-up End Sub

' Not used by Automation Add-Ins, but have to be included in the class to ' implement the interface

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)

' Have a comment to stop VB removing the routine when doing its tidy-up

End Sub

' Not used by Automation Add-Ins, but have to be included in the class to ' implement the interface

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)

' Have a comment to stop VB removing the routine when doing its tidy-up End Sub

A Complex Add-In — RandUnique

Now that you have a reference to the Excel Application object, you can use it in a more complex function. The RandUnique function shown next returns a random set of integers between two limits, without any duplicates in the set. It uses the Excel Application object in two ways:

□ It uses Application.Caller to identify the range containing the function, and hence the size and shape of the array to create and return.

□ It uses Application.Volatile to ensure the function is recalculated each time Excel calculates the sheet.

The routine works by doing the following:

□ It creates an array of all the integers between the given limits, with a random number associated with each item.

□ It sorts the array by the random number, effectively putting the array into a random order.

□ It reads the first n items from the jumbled-up array to fill the required range.

The function has also been written to take an optional Items parameter, enabling it to be called from VBA as well as from the worksheet. If the Items parameter is provided, the function returns a 2D array (1, n) of unique integers. If the Items parameter is not provided, the function uses Application.Caller to identify the size of array to create. Because you're using VB's random number generator, it's a good idea to add a Randomize statement in the initial OnConnection call to ensure that you get different numbers each time (type in the shaded rows):

' Called by Excel when the class is loaded, passing a reference to the ' Excel object

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _

ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant)

' Set a reference to the Excel application, for use in the functions Set moXL = Application

' Initialize the VB random number generator Randomize

'* FUNCTION NAME: RandUnique <*

'* DESCRIPTION: Returns an array of random integers between two limits,

'* without duplication

'* PARAMETERS: Min The lower limit for the random numbers

'* Max The upper limit for the random numbers

'***************************************************************************

Public Function RandUnique(Min As Long, Max As Long, _

Optional Items As Long) As Variant

Dim oRng As Range

Dim vaValues() As Double, vaResult() As Double

Dim iltems As Long, i As Long, iValue As Long

Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long

' Tell Excel that this function is volatile, and should be called ' every time the sheet is recalculated moXL.Volatile

' If we've been given the number of items required, use it... If Items > 0 Then iRows = 1 iCols = Items

Else

'... Otherwise get the range of cells that this function is in ' (as an array formula) Set oRng = moXL.Caller iRows = oRng.Rows.Count iCols = oRng.Columns.Count End If

' How many cells in the range iItems = iRows * iCols

' We can't generate a unique set of numbers if there are more ' cells to fill than there are numbers to choose from, ' so return an error value in that case If iItems > (Max - Min + 1) Then RandUnique = CVErr(xlErrValue) Exit Function End If

' Fill an array with all the possible numbers to choose from, ' and a column of random numbers to sort on ReDim vaValues(Min To Max, 1 To 2) For i = Min To Max vaValues(i, 1) = i vaValues(i, 2) = Rnd()

Next

' Sort by the array by the column of random numbers, ' jumbling up the array

Sort2DVert vaValues, 2, "A"

' Dimension an array to be the same size as the range we're called from ReDim vaResult(1 To iRows, 1 To iCols)

' Start the counter at the beginning of the jumbled array iValue = Min

' Fill the result array from the jumbled array of all values For iRow = 1 To iRows

For iCol = 1 To iCols vaResult(iRow, iCol) = vaValues(iValue, 1) iValue = iValue + 1

Next

Next

' Return the result RandUnique = vaResult

End Function

A Quicksort Routine

The RandUnique function uses a standard Quicksort algorithm to sort the array, reproduced next. This is one of the fastest sorting algorithms and uses a recursive divide-and-conquer approach to sorting:

□ Choose one of the numbers in the array (usually the middle one).

□ Group all the numbers less than it at the top of the array, and all the numbers greater than it at the bottom.

□ Repeat for the top half of the array, then for the bottom half.

<* <* <* <* <* <* <* <* <* <* <*

Private Sub Sort2DVert(avArray As Variant, iKey As Integer, _

sOrder As String, Optional iLow1, Optional iHigh1)

Dim iLow2 As Integer, iHigh2 As Integer, i As Integer Dim vItem1, vItem2 As Variant

On Error GoTo PtrExit

If IsMissing(iLow1) Then iLow1 = LBound(avArray)

FUNCTION NAME: SORT ARRAY - 2D Vertically

DESCRIPTION: Sorts the passed array into required order, using the given key. The array must be a 2D array of any size.

PARAMETERS: avArray The 2D array of values to sort iKey The column to sort by sOrder A-Ascending, D-Descending iLowl The first item to sort between iHighl The last item to sort between

If IsMissing(iHighl) Then iHighl = UBound(avArray)

' Set new extremes to old extremes iLow2 = iLowl iHigh2 = iHighl

' Get value of array item in middle of new extremes vlteml = avArray((iLow1 + iHighl) \ 2, iKey)

' Loop for all the items in the array between the extremes Do While iLow2 < iHigh2

If sOrder = "A" Then

' Find the first item that is greater than the mid-point item Do While avArray(iLow2, iKey) < vlteml And iLow2 < iHighl iLow2 = iLow2 + l

Loop

' Find the last item that is less than the mid-point item Do While avArray(iHigh2, iKey) > vlteml And iHigh2 > iLowl iHigh2 = iHigh2 - l

Loop

Else

' Find the first item that is less than the mid-point item Do While avArray(iLow2, iKey) > vlteml And iLow2 < iHighl iLow2 = iLow2 + l

Loop

' Find the last item that is greater than the mid-point item Do While avArray(iHigh2, iKey) < vlteml And iHigh2 > iLowl iHigh2 = iHigh2 - l

Loop End If

' If the two items are in the wrong order, swap the rows If iLow2 < iHigh2 Then

For i = LBound(avArray, 2) To UBound(avArray, 2) vItem2 = avArray(iLow2, i) avArray(iLow2, i) = avArray(iHigh2, i) avArray(iHigh2, i) = vItem2

Next End If

' If the pointers are not together, advance to the next item If iLow2 <= iHigh2 Then iLow2 = iLow2 + l iHigh2 = iHigh2 - l End If

Loop

' Recurse to sort the lower half of the extremes

If iHigh2 > iLowl Then Sort2DVert avArray, iKey, sOrder, iLowl, iHigh2 ' Recurse to sort the upper half of the extremes

If iLow2 < iHigh1 Then Sort2DVert avArray, iKey, sOrder, iLow2, iHigh1 PtrExit: End Sub

You must now save and recompile your project by using File O Make Excel2007ProgRef.dll to create the updated DLL.

Due to a bug in some versions of VB, you may be presented with an error message stating that there is a sharing violation with the file you are trying to replace. If this occurs, try closing VB (making sure you have saved your project) and then reopening it. You will also get an error if the Add-In is currently loaded in an Excel session, so you'll need to close your Excel session(s) before rebuilding the Add-In.

The complex Add-In is used in the same way as the simple Sequence function shown previously. The only difference is that you have to tell Excel to load the Excel2007ProgRef.Complex Add-In, by clicking Office Menu O Excel Options O Add-Ins O Manage: Excel Add-Ins O Automation Add-Ins and selecting it from the list. When entered as an array formula, the RandUnique function looks something like Figure 18-2.

82

37

97

53

65

53

76

77

31

30

100

67

89

32

48

87

60

49

94

0 0

Post a comment