HOW TO: Handle Excel Events in a twinBASIC COM Addin
You can now use twinBASIC to create a COM add-in for Excel that handles Excel Application events like NewWorkbook and WorkbookOpen.
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
These events are members of the
IDTExtensibility2interface 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.
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
- Click the "Samples" tab on the dialog that appears at startup
- Click "Sample 5. MyCOMAddin"
- 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.
- Click on "Settings"
- Scroll down to the "COM Type Library / ActiveX References" section
- Click on the magnifying glass icon in the "ALL AVAILABLE COM REFERENCES" list box and type in "Excel"
- Check the box next to "Microsoft Excel 16.0 Object Library" (your version number may differ)
- Click the floppy disk icon to save the project
- You may also need to manually restart the compiler by clicking the circular arrow icon
4. Rename the "MyCOMAddin.twin" File to "tbExcelAddin.twin"
- 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"
- Open the newly renamed tbExcelAddin.twin file and change the class name from
5. Modify the tbExcelAddin Class Module
- Perform a Find and Replace All on the
applicationObjectvariable name ([Ctrl] + [H], Find:
applicationObject, Replace all:
- Comment out the
Private ExcelApp As Objectline and add the following one in its place:
Private WithEvents ExcelApp As Excel.Application
- From the dropdowns at the top of the IDE, choose tbExcelAddin > ExcelApp > WorkbookOpen
- In the new Sub, enter:
MsgBox "You opened " & Wb.Name
- Choose dropdowns tbExcelAddin > ExcelApp > NewWorkbook
- 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
GetCustomUIfunction (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.
- Click on DllRegistration.twin in the Sources folder of the PROJECT EXPLORER pane
- Change the AddinClassName from
"tBExcelAddin"(to match the changes we made earlier)
- Delete the one
Const RootRegistryFolder_ACCESSline of code
- Delete the three
RegWrite RootRegistryFolder_ACCESSlines of code
- Change the
RootRegistryFolder_EXCEL & "Description"value from
"Sample add-in demonstrating how to bind to Excel workbook events."
- Delete the four
RegDelete RootRegistryFolder_ACCESSlines of code
7. Save and Build the Project
- 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)
- Choose the "win32" or "win64" build configuration to match your VBA bitness (this is very important!)
- Click the build icon to "Build" the COM Add-in's DLL
- Check the DEBUG CONSOLE pane for any error messages
8. View the COM Add-in in Microsoft Excel
- Open Microsoft Excel and click the twinBASIC Test tab
- Click the "Hello World" button
- Open a blank workbook
- Open an existing workbook
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!