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

A Hello World Add-in

At its most fundamental level, an add-in architecture has four basic requirements:

A mechanism for telling the host application that the add-in exists.

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 application to call a procedure in the add-in when the add-in is unloaded, usually during the application's shutdown processing.

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 dialog to select the add-in file and mark it as installed (on a per-user basis). When Excel starts up, it sees the add-in, opens the file and runs the startup procedure. When Excel 5 had loaded the add-in, it called the special Auto_Open procedure and prior to shutting down, it called the special Auto_Close procedure. Both procedures were placed in a standard VBA module (the only module type that existed at the time!). A Hello World 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 loaded
Sub Auto_Open()
MsgBox "Hello World"
End Sub
'Run when the add-in is closed
Sub Auto_Close()
MsgBox "Goodbye World"
End Sub

When the VBIDE was introduced in Excel 97, every workbook was given a ThisWorkbook class module, within which we could write code to respond to a number of workbook-related events, including Workbook_Open and Workbook_BeforeClose. These procedures were also called when Excel 97 opened and closed an add-in workbook. Listing 21-2 shows a Hello World add-in that uses workbook events.

Listing 21-2. A Hello World Add-in Using Workbook Events in the ThisWorkbook Module

'Run when the add-in is loaded
Private Sub Workbook_Open()
MsgBox "Hello World"
End Sub
'Run when the add-in is closed
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 same with Visual Basic 6. Start VB and select the Addin project type from the New Project dialog. This creates a new COM Add-in project called MyAddin, with a default form and Designer class called Connect. We prefer to always start with a clean project, so change the project name to HelloWorld, remove the default form and delete 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 all the communication between Excel and the COM Add-in. It has a simple UI for us to set the add-in's properties, such as its title, description and which Office application it targets, as shown in Figure 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 ThisWorkbook class gives us a Workbook object that has the Open and BeforeClose events that we use for our startup and shutdown code. The equivalent in the Add-in Designer class is the AddinInstance object and OnConnection and OnDisconnection events, shown in Figure 21-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 ConnectMode As _
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)
MsgBox "Hello World"
End Sub
'Run when the add-in is unloaded
Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
MsgBox "Goodbye World"
End 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 HelloWorld.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-ins 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 the Commands tab and find COM Add-ins… halfway down the list for the Tools category. Drag it to Excel's Tools menu. Figure 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.

Figure 21-3. The COM Add-ins Dialog