HOW TO: Creating an Office COM Add-in with twinBASIC

The Microsoft Access community is about to get a massive infusion of innovation thanks to the continued development of twinBASIC.

In the past, developing COM add-ins–compiled DLLs that add functionality to MS Office desktop applications like Access–required a completely different skill set than most VBA developers had.  You could build them in VB6, but that meant they would never be able to work with 64-bit Access.  The other option was to build them in a different language entirely.  While certainly not impossible, it required learning new syntax and added friction to the process.

twinBASIC is changing all that.

Creating a COM Add-in for Access & Excel

If you have a spare 15 minutes, you can follow these steps to create your first custom COM add-in that runs in both Access and Excel.

The hardest part of building these types of controls 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.

HOW TO: Install twinBASIC

The latest step-by-step instructions for installing twinBASIC (aka, Visual Basic 8).

No Longer SetMike Wolfe

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. View/Modify the MyCOMAddin.twin Code

1. Click on MyCOMAddin.twin in the Sources folder of the PROJECT EXPLORER pane
2. You may want to uncomment the MsgBox lines in the OnXxxx procedures to see when they fire during the add-in's lifetime
3. 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, be sure to register for my upcoming Access User Group Europe presentation on July 6, 2022)

4. View/Modify the DLL Registration Code

1. Click on DllRegistration.twin in the Sources folder of the PROJECT EXPLORER pane
2. By default, the COM Add-in sample includes code to register the add-in for both Access and Excel; you can delete one section or the other if you don't need both...or add additional sections to register the Add-in for Outlook, Word, PowerPoint, etc.
3. Feel free to change the RegWrite "Description" lines to something more descriptive
4. If you create multiple COM Add-ins, you'll need to change the AddinQualifiedClassName to avoid overwriting earlier add-ins; the best way to do this is to change the Const AddinClassName line while also changing the name of Class MyCOMAddin (in the MyCOMAddin.twin file) to match

5. Save and Build the Project

1. Click the disk icon to "Save all changes" and choose a location to save your project (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

6. View the COM Add-in in Microsoft Access

1. Open Microsoft Access
2. Click the "twinBASIC Test" tab
3. Click the "Hello World" button

7. View the COM Add-in in Microsoft Excel

1. Open Microsoft Excel
2. Click the "twinBASIC Test" tab
3. Click the "Hello World" button

Detecting the Host Application

Notice that the Excel message box shows, "(via Microsoft Excel)" while the Access one shows, "(via Microsoft Access)".

How does twinBASIC know where it's being called from?  It saves the host application object in the OnConnection IDTExtensibility2 event:

Then, in the OnHelloWorldClicked callback function, we return the application object's name:

The applicationObject variable itself is declared As Object so that it can accept either an Access or Excel (or Word or Outlook or etc.) application object:

Next Steps

Go forth and build great things!

UPDATE [22-08-12]: Update instructions for installing twinBASIC.