Download: Microsoft Excel/Word Developer COM Add-In

Sunday, August 01, 2004

The Zip file contains a setup exe and cab for "bOffDev", which stands for "Bill's Office Development", a simple developer COM add-in for VBA that works with versions of Excel and Word that support COM add-ins. (Note: ONLY Excel and Word, not Access or anything else.) (Screen shots below.)

This started as an in-house tool and, well, has pretty much ended up as an in-house tool.  But I've polished it up enough to make it useful for others, I hope.  No fancy icons -- just the ugly default VB Form icon on all dialogs.

Why such a COM add-in? The important thing for me originally was to get an "Add Procedure" dialog that included the ability to use different procedure templates (see the Add Proc screen shot below).  I don't provide any documentation of how to use templates; instead just look at the templates.xml file that gets installed in the same folder as boffdevlib.xml (that's where it is looked-for, too, so be sure to keep it there.)  If you study that file, you'll get the idea.

I also like to use good ole VB line numbering to help diagnose runtime errors -- but like most people I don't want to leave the line numbers in the final product, so the "Line Numbers" feature enables you to add and remove VB line numbers from procedures, entire modules, or the entire project.

The "Export Modules" feature lets you export all or some of your VBA project's modules and, optionally, pass them through a command line as they go.  This is handy for quickly backing up stuff.

The "Notes" feature is just a little popup notes utility with a tabbed interface -- the notes get saved often and automatically, so no need for a Save button or menu item.

UPDATE 15. Jan 2005:: Excel and Word security settings must be taken into account when running an "unsigned" COM Add-In such as this. Please see the contents of the README.txt file below at the bottom of this page.

Personally, I like to know exactly what I'm installing when I install stuff from other sources, especially so I can diagnose any dependency problems, so I'll list a few things here. 

There are two custom DLLs in the cab: boffdevlib.dll and bdVBAExtensibility.dll.  The boffdevlib.dll is made from a COM add-in project in VB6.  The bdVBAExtensibility.dll is a normal in-process COM automation DLL made in VB6.

The bdVBAExtensibility.dll had these References (besides normal VB stuff):

Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft VBScript Regular Expressions 5.5

The boffdevlib.dll -- which has UI elements -- has these Components and References:

Microsoft Windows Common Controls 6.0 (numbers 1, 2 and 3 from SP5)
bdVBAExtensibility
Microsoft Add-In Designer (this is a VB6 designer)
Microsoft Office 9.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft VBScript Regular Expressions 5.5
Microsoft XML, version 3.0

Screenshots

The add-in menu. You can set the name of the menu and choose its location using the Options dialog.

The "Add Procedure" dialog:

The "Export Modules" dialog:

The "Notes" dialog:

The "Options" dialog:

Creating a command in the "Options" dialog.  The command can then be used on the "Export Modules" screen -- it will be run on each exported module.

This add-in is free. The source code is not included. If you think the add-in improves your own productivity and you wish to show your appreciation in a monetary fashion, who am I to stop you!? The "About" dialog tells you how to donate should you wish to do so.

This is the README.txt file included in the package:

Bill's Office Development Add-In for Word/Excel

This is a COM Add-In.

SECURITY NOTES:

By default, the Word and Excel 2003 Tools menus do not show you the "COM Add-Ins" menu item. To see it, right-click your menu and chose Customize, then look through the available menu items for the Tools menu. You'll see "COM Add-Ins" there: drag it to your menu or to a command bar.

For Word and Excel 2003 (and I think for 2002 too, but I uninstalled that a while ago and can't remember now!), you need to go to Tools - Macro - Security, then go to the Trusted Publishers page and check both "Trust all installed add-ins and templates" and "Trust access to Visual Basic Project." Without these settings, Excel/Word won't let the add-in look at the VB project associated with a DOC or XLS.

EVEN AFTER you set that, you might find that the Add-In does not get loaded on startup. I've seen behavior whereby the Add-In's right to look at the VBA project is still rejected, unless a VB project already exists. You can remedy this by using your Personal Workbook, or Normal template project (or some other thing that runs on startup) to force loading like this:

' For Excel:
Sub StartBDAddIn()
    On Error GoTo errorhandler
    Dim oneAddin As COMAddIn
    For Each oneAddin In Application.COMAddIns
        If StrComp(oneAddin.progID, "bOffDevLib.bOffDevExcel", vbTextCompare) = 0 Then
            oneAddin.Connect = True
        End If
    Next
    Exit Sub
errorhandler:
    MsgBox "Error loading bOffDev COM Add-in: " & Err.Description
    Err.Clear
    Exit Sub
End Sub

' For Word:
Sub StartBDAddIn()
    On Error GoTo errorhandler
    Dim oneAddin As COMAddIn
    For Each oneAddin In Application.COMAddIns
        If StrComp(oneAddin.progID, "bOffDevLib.bOffDevWord", vbTextCompare) = 0 Then
            oneAddin.Connect = True
        End If
    Next
    Exit Sub
errorhandler:
    MsgBox "Error loading bOffDev COM Add-in: " & Err.Description
    Err.Clear
    Exit Sub
End Sub

Of course you also need to have something (like Auto_Open in Excel) call this sub. Note that the only difference between the two is the ProgID of the COM add-in: bOffDevLib.bOffDevExcel and bOffDevLib.bOffDevWord.