COM Add Ins

Whereas Automation Add-Ins enable you to create your own worksheet functions, COM Add-Ins provide a way to extend the user interface of Excel and all the other Office applications. They have a number of advantages over normal xla or xlam Add-Ins, including:

□ They're less obtrusive (not showing up in the VBE Project Explorer).

□ They're more secure (being compiled DLLs).

□ They're not specific to a single application — the same mechanism works with all the Office applications and the VBE itself (and any other application that uses VBA 6), allowing you to create a single Add-In that can extend all the Office applications.

The IDTExtensibility2 Interface (Continued)

The previous section introduced the IDTExtensibility2 interface, where you used the OnConnection and OnDisconnection methods to obtain a reference to the Excel Application. The remaining methods defined in the interface can be used by COM Add-Ins to respond to specific events in Excel's lifetime. The methods are outlined in the following table.

Method

Occurs

Typical Usage

OnConnection

When the COM Add-In is loaded by Excel.

Store a reference to the Excel application, add menu items to Excel's CommandBars, and set up event hooks.

OnStartupComplete

After Excel has finished loading all Add-Ins and initial files.

Show a startup dialog (such as those in Access and PowerPoint) or change behavior depending on whether other Add-Ins are loaded.

OnAddInsUpdate

Whenever any other COM Add-Ins are loaded or unloaded.

If the COM Add-In depends on another Add-In being loaded, this Add-In can unload itself.

OnBeginShutdown

When Excel starts its shutdown process.

Stop the shutdown in certain circumstances or perform any pre-shutdown tidy-up routines.

OnDisconnection

When the COM Add-In is unloaded, either by the user or by Excel shutting down.

Save settings. If unloaded by the user, delete any CommandBar items that were created at connection.

Most COM Add-Ins use only the OnConnection method (to add their menu items) and OnDisconnection method (to remove them), though code has to exist in the class module for all five methods to correctly implement the interface.

Registering a COM Add-In with Excel

For Automation Add-Ins, you told Excel that the Add-In exists by selecting it in the Automation Add-Ins dialog (resulting in some entries being written to the registry). You tell Excel that a COM Add-In exists by writing specific keys and values to specific places in the registry. When Excel starts, it looks in those keys to see which COM Add-Ins exist, then checks the values in those keys to see how to display them in the COM Add-Ins list, whether or not to load them, and so on. The keys for COM Add-Ins targeted to Excel are:

□ Registered for the current user: HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\AddInProgID

□ Registered for all users: HKEY_USERS\.DEFAULT\Software\Microsoft\Office\Excel\Addins\AddInProgID

□ Registered for the machine:

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\Excel\Addins\AddInProgID

The values are as follows.

Name

Type

Use

FriendlyName

String

The name shown in the COM Add-Ins list.

Description

String

The description shown in the COM Add-Ins dialog.

LoadBehavior

Number

Whether it is unloaded, loaded at startup, or demand-loaded.

SatelliteDllName

Number

The name of a resource DLL that contains localized names and descriptions. If used, the name and description will be #Num, where Num is the numeric resource ID in the Satellite DLL. Most of the standard Office Add-Ins use this technique for their localization.

CommandLineSafe

String

Whether the DLL could be called from the command line (not applicable to Office COM Add-Ins).

Once registered correctly, the COM Add-In will show up in Excel's COM Add-Ins dialog, where it can be loaded and unloaded like any other Add-In. You can find the COM Add-Ins dialog by clicking Office Menu O Excel Options O Add-Ins O Manage: COM Add-Ins O Go.

The COM Add-In Designer

Microsoft has provided a COM Add-In Designer class to assist in the creation and registration of COM Add-Ins. It provides the following benefits:

□ Implements the IDTExtensibility2 interface, exposing the methods as events that you can either hook or ignore. You don't, therefore, have to include code for unused interface methods in your class module.

□ Provides a form to fill in to provide the values for the registry entries used to register the COM Add-In, and to select which application to target.

□ When compiled, it adds code to the standard DllRegisterServer entry point in the DLL that writes all the registry entries for you when the DLL is registered on the system (though only for the Current User key). This greatly simplifies installation, because you can install the Add-In by running the following command: RegSvr32°c:\MyPath\MyComAddIn.DLL.

By way of an example, you'll create a COM Add-In that provides a Wizard for entering the RandUnique Automation Add-In function created in the previous section. You will continue to use Visual Basic, building on the Excel2 007ProgRef DLL from the previous section.

Open the Excel2007ProgRef project in Visual Basic. Add a new Add-In class to the project by clicking Project O Add Addin Class (if that menu item doesn't exist, click Project O Components O Designers and check the Addin Class entry). This adds a new Designer class and gives it the name AddlnDesignerl. Using the Properties window, change the name to COMAddln and set the Public property to True (ignoring any warnings). Fill in the Designer form as follows:

Add-In Display Name

Excel 2007 Prog Ref Wizards

Addin Description

Displays a Wizard dialog for entering the Sequence and RandUnique Automation Addin functions, documented in the Excel 2007 VBA Programmers Reference

Application

Microsoft Excel

Application Version

Microsoft Excel 12.0

Initial Load Behavior

Startup

The Designer only creates registry entries for the current user. If you wish to install the Add-In for all users on the machine, you will need to add your own registry entries in the Advanced tab of the Designer form, as documented in Microsoft KnowledgeBase article Q290868 at http://support.microsoft.com/kb/q2 90868/.

Linking to Excel

Click View O Code to get to the Designer's code module, and copy in the following code to hook into the lDTExtensibility2 interface and link the COM Add-In to Excel by storing a reference to the Excel Application object, passed to the Add-In in the OnConnection method:

Dim WithEvents moXL As Excel.Application

' The IDTExtensibility2_OnConnection method is handled by the Designer, ' and exposed to us through the AddInInstance_OnConnection method Private Sub AddInInstance_OnConnection( _

ByVal Application As Object, _

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

Set moXL = Application MsgBox "Connected"

End Sub

' The IDTExtensibility2_OnDisconnection method is handled by the Designer, ' and exposed to us through the AddInInstance_OnDisconnection method Private Sub AddInInstance_OnDisconnection( _

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

Set moXL = Nothing MsgBox "Disconnected"

End Sub

Save the project and make the Add-In DLL by clicking File O Make Excel2007ProgRef.dll, and then open Excel 2007 (note that you will not be able to subsequently rebuild the DLL if it is being accessed by Excel at the time). As Excel opens, you'll see a Connected message pop up as the Add-In is connected, and a Disconnected message when Excel is closed. You will also get these messages if you load or unload the Add-In using the COM Add-Ins dialog.

Responding to Excel's Events

The Designer code module is a type of class module that allows you to declare a variable WithEvents, to hook into their events. In the previous code, you hooked into the Excel Application events, enabling the COM Add-In to respond to the users opening or closing workbooks, changing data in cells, and so on, in the same way you can in a normal Excel Add-In. See Chapters 8 and 16 for more information about these events.

Adding CommandBar Controls

Prior to Excel 2007, you used the CommandBars objects to create all menus and toolbars. In Excel 2007, the Ribbon replaced the top-level menus and toolbars, but you still use the CommandBars objects for the popup menus. This example COM Add-In adds two menu items to the cell's right-click popup menu to show Wizard forms to assist in the entry of Automation Add-In formulas. Using the Ribbon with COM Add-Ins is explained later in the chapter.

Once you have a reference to the Excel Application object, you can add buttons to command bars in the same way as described in Chapter 15. The only difference is how the code responds to a button being clicked.

When adding a CommandBarButton from within Excel, set its OnAction property to be the name of the VBA procedure to run when the button is clicked.

When adding a CommandBarButton from outside Excel (from within a COM Add-In), hook the button's Click event using a variable declared WithEvents inside the Add-In.

To use CommandBarButtons, you need a reference to the Office object library, so click Project O References and check the Microsoft Office 12.0 Object Library.

Delete any code that may already exist in the Designer's code module (such as the example code added in the previous section), and replace it with the following. This defines the class-level variables you'll be using to store the reference to the Excel Application object, and to hook the CommandBarButton's events:

Dim WithEvents moXL As Excel.Application

Dim WithEvents moBtn As Office.CommandBarButton

Const msAddInTag As String = "Excel2007ProgRefTag"

When you hook a command bar button's events using the WithEvents keyword, the variable (moBtn) is associated with the Tag property of the button it's set to reference. All buttons that share the same Tag will cause the Click event to fire. In this way, you can handle the click events for all your buttons using a single WithEvents variable, by ensuring they all have the same Tag. You can distinguish between buttons by giving them each a unique Parameter property as you create them in the OnConnection method, which should be copied into the Designer's code module:

' The IDTExtensibility2_OnConnection method is handled by the Designer, ' and exposed to us through the Add-InInstance_OnConnection method Private Sub AddInInstance_OnConnection( _

ByVal Application As Object, _

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

Dim oToolsBar As CommandBar, oBtn As CommandBarButton

Set moXL = Application

' Get a reference to the cell right-click menus Set oToolsBar = moXL.CommandBars("Cell")

' If our controls don't exist on the menu bar, add them

' Handle errors in-line (such as the button not existing) On Error Resume Next

' Check for, and add, the 'Sequence Wizard' button Set oBtn = oToolsBar.Controls("Sequence Wizard") If oBtn Is Nothing Then

Set oBtn = oToolsBar.Controls.Add( _

msoControlButton, ,"SequenceWiz", , True)

With oBtn

.Caption = "Sequence Wizard" .Style = msoButtonCaption .Tag = msAddInTag End With End If

' Check for, and add, the 'RandUnique Wizard' button Set oBtn = Nothing

Set oBtn = oToolsBar.Controls("RandUnique Wizard") If oBtn Is Nothing Then

Set oBtn = oToolsBar.Controls.Add( _

msoControlButton, , "RandUniqueWiz", , True)

With oBtn

.Caption = "RandUnique Wizard" .Style = msoButtonCaption .Tag = msAddInTag End With End If

' Set the WithEvents object to hook these buttons. All buttons ' that share the same Tag property will fire the moBtn_Click event Set moBtn = oBtn

End Sub

Although you set the Temporary parameter to True when adding the controls, it is good practice to delete them when the Add-In is unloaded, using the OnDisconnection event:

' The IDTExtensibility2_OnDisconnection method is handled by the Designer, ' and exposed to us through the AddInInstance_OnDisconnection method Private Sub AddInInstance_OnDisconnection( _

ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _ custom() As Variant) Dim oCtl As CommandBarControl

' Find and remove the buttons

For Each oCtl In moXL.CommandBars.FindControls(Tag:=msAddInTag) oCtl.Delete

Next

Set moBtn = Nothing Set moXL = Nothing

End Sub

In the Click event, you check the Parameter property of the button that was clicked and show the appropriate form. For this example, just add two blank forms to the project, giving them the names frmSequenceWiz and frmRandUniqueWiz:

' The moBtn_Click event is fired when any of our commandbar buttons are ' clicked. This is because the event handler is associated with the Tag ' property of the button, not the button itself. Hence, all buttons that ' have the same Tag will fire this event.

Private Sub moBtn_Click(ByVal Ctrl As Office.CommandBarButton, _

CancelDefault As Boolean)

' Check that a cell range is selected If TypeOf moXL.Selection Is Range Then

' Run the appropriate form, depending on the control's Parameter Select Case Ctrl.Parameter Case "SequenceWiz"

frmSequenceWiz.Show vbModal

Case "RandUniqueWiz"

frmRandUniqueWiz.Show vbModal End Select

Else

' Display an error message if a range is not selected MsgBox "A range must be selected to run the Wizard.", vbOKOnly, _ "Excel 2007 Prog Ref Wizards"

End If End Sub

Save the project and use File O Make Excel2007ProgRef.dll to create the DLL, which also adds the registry entries for Excel to see it. Start Excel 2007, right-click a cell, and click the Sequence Wizard menu to show the Wizard form.

Using a COM Add-In from VBA

It is possible (though unfortunately quite rare) for the creator of a COM Add-In to provide programmatic access to the Add-In from VBA. This would be done either to:

□ Expose the Add-In's functionality for use through code

□ Provide a mechanism for controlling or customizing the Add-In

It is achieved by setting the Add-In instance's Object property to reference the COM Add-In class (or a separate class within the Add-In), and then exposing the required functionality using Public properties and methods, just like any other class. This example provides yet another way of getting to the Sequence and RandUnique functions.

Add the following lines to the bottom of the AddInInstance_OnConnection routine, to provide a reference to the Add-In class using the Add-In's Object property:

' Set the Add-In instance's Object property to be this class, providing ' access to the Com Add-In's object model from within VBA. Note that we ' don't use Set here! AddInInst.Object = Me

And add the following code to the bottom of the Designer's class module, to create and return new instances of our Simple and Complexclasses:

' Property to return a reference to our Simple class, providing access ' from VBA:

'vaSeq = Application.ComAddIns("Excel2007ProgRef.ComAddIn").Object _

Public Property Get SimpleFuncs() As Simple

Set SimpleFuncs = New Simple End Property

' Property to return a reference to our Complex class, providing access ' from VBA:

'vaRU = Application.ComAddIns("Excel2007ProgRef.ComAddIn").Object _

' .ComplexFuncs.RandUnique(...)

Public Property Get ComplexFuncs() As Complex

Set ComplexFuncs = New Complex End Property

From within Excel, you can then use the following code to access the Sequence function, going through the COM Add-In and its Object property:

Private Sub CommandButton1_Click()

Dim vaSequence As Variant

' Get the sequence using the COM Add-In

vaSequence = Application.ComAddIns("Excel2007ProgRef.ComAddIn")

.Object.SimpleFuncs.Sequence(5, 10, 2)

' Write the sequence to the sheet

ActiveCell.Resize(1, 5) = vaSequence

End Sub

The key point about using this method is that you are accessing the same instance of the class that Excel is using for the Add-In, allowing you to manipulate, query, or control that Add-In from VBA. For more complex COM Add-Ins, the same method can be used to provide access to a full object model for controlling the Add-In.

0 0

Responses

  • faruz
    How to reference comaddins library, vba, vbe?
    3 months ago

Post a comment