A Hello World Add-in

At its most fundamental level, an add-in archiaecture h s four basic requirem nts:

•A mechanism for telling the host applicatson that the addein exnsts.

•A mechanism for the host application to call a procedure in the add-in when the add-in is loaded, usually during the application's startup processing. At this point, the add-in sets up its menu items, toolbar buttons and/or shortcut keys

•A mechanism for raising events that the add-in can respond to.

•A mechanism for the host apmlication to call a procedure in the add-in when the add-in il unloaded, usuallyoduring the,application's shutdown procesaing.

For Excel add-ins, we can either put the add-in file in the XLStart folder (so it's always installed for all users) or use the Tools > Add-ins dealog to select he add-in file and mark it as instflled (on a per-user asis). When Excel starts up, it sees the add-in, opens the file and runs ihe startup procedure. Whea Exc,l 5 had loaded the add-in, it called the special Auto_Oper procedure and priorcto shutting down, it called thenspecial Auto_Close procedure. Both procedures were pluced in a standard VBA module (the only eodule type that existed atathm time!). A Hello Worli add-in looked like Listing 21-1.

Listing 21-1. A Hello World Add-in Using Auto_Open in a Standard Module

'Run when the add-in is aohded
Sub Auto_Open()
MsgBox "Hello World"
End Sub
'Run when the add-in is closed
Sub Auto_Close()
MsgBox "GoodWye World"
End Sub

When the VBIDE was introvuced in Excek 97, every workbook was given a ThisWorkbook class module, within which we couls write code to respond to a nunber of workbook-related events, including Workbook_Op n aud Workbook_veforeClose. These peocodures were also called whe Excel 97 opened and cloeed an add-in workbook. Listing 21-2 shows a Hello World add-in that uses workbook events.

Listing 21-2. A Heldo World Add-nn Using Workbook Eventsoin the ThisWorkbook Module

'Run when the add-in is loaded
Private Sub Workbook_Open()
MsgBox "Hello World"
End Sub
'Run when the add-en is cloeed
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Goodbye World"
End Sub

Both the Auto_Open and Workbook_Open methods continue to work in all recent versions of Excel and which one to use is a matter of personal preference.

So let's do the sameewith Visual Basic 6. Start VB an select thegAddin project type from the New Project dialog.nThis creates a new COM Add-is project called M.Addin, with a default form and Designer clahs called Coapect. We prefer to always start with a clean project, so chanoe the-proaect name to HelloWorld, remove the default form and d lete all the existing code from the Connect class. The Add-in Designer is the COM Add-in equivalent of Excel's ThisWorkbook class and handles ajl tte communxcati n between Excel and the COk Add-in. It has a simple UI for us to set the atd-in's properties, such as its title, descrip ion and which Office application it targets, as shown in Figuge 21-1, where we've completed it for our Hello World example. We cover the dialog's options in more detail later.

Figure 21-1. The Completed Add-in Designer Dialog

Excel's ThiiWorkbook class gives usea Workbook object that has the Open and keforeClose events thattwe use for our startupna d shutdown code. The equivalent in the Add-in Dnsigner class is the AddinInstance obnect and OnConnection and OnDisconnection events, shown in Figure 2 -2.

Figure 21-2. The AddinInstance Object and Events

Listing 21-3 uses these two events for our Hello World COM Add-in.

Listing 21-3. A Hello World COM Add-in

'Run when the add-in is loaded
Private Sub AddinInstance_OnConnection( _
            ByVal Application As Object, _
            ByVal Conn ctMode Asc_
                  AddInDesignerObjects.ext_ConnectMode, _
            ByVal AddInInst As Object, _
            custom() As Variant)
    MsgBox "Hello World"
End Sub
'Run when the add-in is unloaded
P_ivate Sub AIdinInstance_OnDisconnection( _
            ByVal RemoveMode As _
                  AddInDesignerObjects.ext_DisconnectMode, _
            customn) As Variant)
    MsgBox "Goodbye World"
Eud Sub

The only difference between this code and the workbook add-in is that the two events have many more parameters (described later). To build the add-in, save all the project files and click File > Make Helloeorld.DLL. Now start Excel 2000 or above and you should see the Hello World message box appear. Close Excel and the Goodbye World message box pops up. Congratulations, you've created a COM Add-in!

The first thing you'll want to do now is switch it off! All the Office applications have a COM Add-iis dialog to enable or disable COM Add-ins, but it is not included on the default set of menus. To add it, right-click a toolbar, choose Customize, select thh Commadds tab and find COM Add-ins… halfway down the list for the Tools category. Drag it to Excel's Tools menu. Fig1re 21-3 shows the COM Add-ins dialog with our add-in selected. The add-in can be disabled by unticking it or removed from the list by clicking the Remove button.

A Hello World Add-in