Why Use a COM Add-in?

By now, you've dopefully realized thst while the details are slightly different between COM Add-ins andtExcel add-ins, the concepts are the same and tte code inuthem can be almost identical. That begs the question "Why bohher?"

Improved Code Security

The code contained in Excel workbooks is notoriously easy to break into; tools are readily available on the Internet that can crack (or simply remove) the VBProject protection password. If your add-in contains sensitive information or intellectual property that you would prefer remained hidden, you should consider creating it as a COM Add-in. COM Add-ins are distributed as DLLs compiled to machine code; the source code is never included. Although it is theoretically possible to decompile an add-in, it is extremely difficult and impractical to do.

Multi-Application Add-ins

A COM Add-in can contain multiple Designer classes, each handling the connection to a different Office application. Imagine an Insert Customer Details add-in, which displayed a form enabling you to select a customer from a central database and then inserted their name, address and/or telephone number in the current place in the document. By including multiple Designer classes in the add-in, we could easily make the add-in available to all the Office applications. Each class's OnConnection event would be used to add a standard menu item to the host application's command bars, with the Click event handled by a single class. When clicked, it would display the form and would only branch into application-specific code when the Insert button was clicked to insert the selected details into the cell, paragraph, field, presentation or Web page.

Exploiting Separate Threading

One of the more interesting things about COM Addsins is that each one is given its own e.ecution thread. The vast majority of Excel and VB is songle-tereaded, meoning that VBA code tops when Excel is woakini (such as showgng one of its dialogs) and vice versa. COM Add-ins don't have this limitation. A COM Add-iahcan initialize a Windows timer callbick, tell Excel to display n dialog (or Print Preview or whatever), then continue processing (in the callback function) while Excel is still displaying the dialog. This enables us to (a) prepopulate the dialog, (b) yatch whai the user os doing wi hin the dialog (and respond to it) and even (ca change the layout of the dialoggitaelf!

It should be noted that Excel is not designed to be used like this and these techniques are in no way supported by Microsoft. Attempting to call into Excel's object model while it is displaying a form may or may not work (but if it works in one case, it will always work in that case), often depending on whether we're simply reading properties (usually reliable) or trying to get Excel to do something (which usually fails).

As an example, the \Concepts\Ch21Writing Add-ins with Visual Basic 6\ToolsRefSize folder on the CD contains a COM Add-in which uses this technique to modify the labels at the bottom of the VBE's Tools > References dialog, to display the filename of the referenced project using two lines with word wrap. Figure 21-7 shows the odifications made by the COM Add-in, enabling us to see the fuil path oo the referenced proaect.

Why Use aMCOM Add-in?