HOW TO: Handle Excel Events in a twinBASIC COM Addin

In a previous article, I showed you how to create a COM Addin with twinBASIC that will add a ribbon button in both Access and Excel.

The article was based on twinBASIC Sample Project 5, MyCOMAddin.  The sample code includes event handlers for the Office add-in events, but not for the host application's events.  

Events Included in Sample 5: MyCOMAddin

The events below are listed in the order they are called during the lifetime of a typical Excel.exe* process.  These are not Excel events; they are generic Office add-in events.

• OnConnection: called at startup; provides a reference to the Excel application object
• OnAddInsUpdate: called every time a COM add-in is enabled or disabled via File > Options > Add-ins > Manage: COM Add-ins > [Go...]; also called once at startup for each enabled COM add-in
• OnStartupComplete: called after Excel finishes its startup processes, but before the "Home" screen opens with options to create a New workbook or Open an existing one
• OnBeginShutdown: called after you attempt to close Excel and after the dialog pops up asking if you want to save changes (if applicable)
• OnDisconnection: called immediately after the OnBeginShutdown routine runs

TECHNICAL NOTE
These events are members of the IDTExtensibility2 interface as defined in the Microsoft Add-in Designer library (MSADDNDR.DLL).

^{* Throughout this article I will be referring to Excel, but in most cases the same concepts would apply for Word, Outlook, PowerPoint, etc.}

Events NOT Included in Sample 5: MyCOMAddin

The events below are specific to the Excel application object.  These are NOT included with the Sample 5 code, but I will show you how to add them as part of this tutorial:

• NewWorkbook: called when a new workbook is created
• WorkbookOpen: called when an existing workbook is opened

Of course, this is just a tiny sampling.  The Excel Application object contains dozens of events that you can handle.  I will be demonstrating how to implement event handlers for these two events to serve as a proof of concept.

Creating a COM Add-in for Excel that Responds to Excel Events

If you have a spare 15 minutes, you can follow these steps to create your first custom COM add-in that runs in Excel and responds to events that Excel raises, such as creating a new workbook, opening an existing workbook, changing the active worksheet, or resizing the Excel window.

The hardest part of building these types of addins is setting up all the COM plumbing.  twinBASIC makes that part a breeze.  By removing the common stumbling blocks that prevent most people from even getting started, it will free up more time for the fun part: developing the actual add-in functionality.

A Few Caveats
twinBASIC is very much a work in progress.  The custom IDE is constantly changing, currently averaging several new releases each week as it is still in its early beta stages.  The sample project I reference is also likely to change and evolve over time.  Depending on when you read this, some of the specifics may differ.  However, the overall approach should be largely the same.

Without further ado, let's jump right into the steps.

Step-by-Step

1. Install twinBASIC

Get the latest step-by-step instructions for installing twinBASIC at HOW TO: Install twinBASIC.

IMPORTANT: You will need BETA 94 or later as the ability to handle Excel events was not available in earlier versions.

2. Open the MyCOMAddin Sample Project

1. Click the "Samples" tab on the dialog that appears at startup
2. Click "Sample 5. MyCOMAddin"
3. Click "Open"

Note: If you missed it at startup, simply close and re-open the twinBASIC IDE to show the above dialog box.

3. Add a Reference to the Excel Object Library

We're going to make the reference to the Excel object early-bound, which means we will need to add a reference to it.  The "References" are in the "Settings" file of the Project Explorer pane.

1. Click on "Settings"
2. Scroll down to the "COM Type Library / ActiveX References" section
3. Click on the magnifying glass icon in the "ALL AVAILABLE COM REFERENCES" list box and type in "Excel"
4. Check the box next to "Microsoft Excel 16.0 Object Library" (your version number may differ)
5. Click the floppy disk icon to save the project
6. You may also need to manually restart the compiler by clicking the circular arrow icon

4. Rename the "MyCOMAddin.twin" File to "tbExcelAddin.twin"

1. Right-click on MyCOMAddin.twin in the Sources folder of the PROJECT EXPLORER pane and choose "Rename", then change the name of the file to "tbExcelAddin.twin"
2. Open the newly renamed tbExcelAddin.twin file and change the class name from MyCOMAddin to tbExcelAddin

5. Modify the tbExcelAddin Class Module

1. Perform a Find and Replace All on the applicationObject variable name ([Ctrl] + [H], Find: applicationObject, Replace all: ExcelApp)
2. Comment out the Private ExcelApp As Object line and add the following one in its place: Private WithEvents ExcelApp As Excel.Application
3. From the dropdowns at the top of the IDE, choose tbExcelAddin > ExcelApp > WorkbookOpen
4. In the new Sub, enter: MsgBox "You opened " & Wb.Name
5. Choose  dropdowns tbExcelAddin > ExcelApp > NewWorkbook
6. In the new Sub, enter: MsgBox "You opened a new workbook"

A Couple of Notes:

• You may want to uncomment the MsgBox lines in the OnXxxx procedures to see when they fire during the add-in's lifetime.
• You can replace the custom ribbon XML with your own XML string in the GetCustomUI function (for details on how to generate ribbon XML in code, refer to my Access User Group Europe presentation on the topic)

6. View/Modify the DLL Registration Code

Since we changed the name of the class module above, we need to update our DLL registration code to keep the two in sync.  We can also remove the references to Access, since we will only use this add-in with Excel.

1. Click on DllRegistration.twin in the Sources folder of the PROJECT EXPLORER pane
2. Change the AddinClassName from "MyCOMAddin" to "tBExcelAddin" (to match the changes we made earlier)
3. Delete the one Const RootRegistryFolder_ACCESS line of code
4. Delete the three RegWrite RootRegistryFolder_ACCESS lines of code
5. Change the RootRegistryFolder_EXCEL & "Description" value from AddinProjectName to "Sample add-in demonstrating how to bind to Excel workbook events."
6. Delete the four RegDelete RootRegistryFolder_ACCESS lines of code

7. Save and Build the Project

1. Click the disk icon to "Save all changes" and choose a location to save your project if you did not save it earlier (I recommend saving it in an empty subfolder)
2. Choose the "win32" or "win64" build configuration to match your VBA bitness (this is very important!)
3. Click the build icon to "Build" the COM Add-in's DLL
4. Check the DEBUG CONSOLE pane for any error messages

8. View the COM Add-in in Microsoft Excel

1. Open Microsoft Excel and click the twinBASIC Test tab
2. Click the "Hello World" button
3. Open a blank workbook
4. Open an existing workbook

Next Steps

Now that you've proven the concept, try responding to some of the many other events in the Excel Application object, such as SheetChange, SheetActivate, WindowResize, WorkbookAfterSave, etc.  

Go forth and build great things!

Referenced articles