Page 286 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

The OnConnection routine is called by the host application to initialize the add-in. As you
saw earlier in this chapter, the only real work required by this routine is to save an object ref­
erence to the application object. All the following code is automatically generated by the wiz­
ard. The applicationObject variable is also defined by the wizard as a module-level variable
and is used to access the resources owned by the application hosting the add-in.

Public Sub OnConnection(ByVal application As Object, _
ByVal connectMode As Extensibility.ext_ConnectMode, _
ByVal addInInst As Object, ByRef custom As System.Array) _
Implements Extensibility.IDTExtensibility2.OnConnection

applicationObject = application
addInInstance = addInInst

End Sub

Note The Implements keyword indicates that this routine implements a routine found in
a particular interface. In this case, you can see that the IDTExtensibility2.OnConnection
routine is being referenced.

The OnStartupComplete routine is called after the add-in has been initialized, but before the
user can start using the application. This is the best place to modify menus or make any other
changes to Excel.
You can add a button to a toolbar with this code. First you create a new instance of the
MyButton object by using the Add method associated with the Controls collection on a par­
ticular toolbar. Next set the Caption property to hold the name to be displayed on the button
and set the Style property so that the caption is displayed on the button rather than on an icon.

Public Sub OnStartupComplete(ByRef custom As System.Array) _
Implements Extensibility.IDTExtensibility2.OnStartupComplete
MyButton = applicationObject.CommandBars("Standard").Controls.Add(1)
MyButton.Caption = "My Button"
MyButton.Style = MsoButtonStyle.msoButtonCaption
MyButton.Tag = "My Button"
MyButton.OnAction = "!<MyCOMAddin.Connect>"

MsgBox("Startup complete")
Chapter 11
End Sub
You should then set the Tag property to indicate the name of the button, along with defining
the OnAction property so that it points to this component.

Refer to Chapter 18, “Manipulating Menus and Toolbars,” for more information on how to create and
manipulate menus and toolbars.

260
Part 4: Advanced VBA