Page 273 - Microsoft Office Excel 2003 Programming Inside Out

Creating Add-Ins and COM Add-Ins

Figure 11-5. The AddIns collection contains information about each add-in known to Excel.

Creating Automation and COM Add-Ins

There are times when it isn’t desirable to create an add-in using Visual Basic for Applications.
Because VBA macros are interpreted, they execute slower than compiled code, which can
seriously affect the performance of computation-intensive add-ins. Also, some functions
aren’t easily implemented using VBA, such as image processing or specialized networking
applications.
To address this limitation, all Office applications including Excel have the ability to access an
add-in stored in an external DLL or EXE file. Although there are two different techniques
used to access the external add-ins, both are created with similar programming techniques.

COM add-ins are typically used to respond to clicking a command button or to some other
Excel event such as opening or closing a workbook. Unlike Excel add-ins, you can’t create a
COM add-in that can be used as part of a cell’s formula. Automation add-ins are less Chapter 11
restricted and can be used to respond to Excel events as well as to provide functions that can
be used in a formula.
COM add-ins are defined only through the COM Add-Ins dialog box on the toolbar, whereas
automation add-ins can be defined through the COM Add-Ins dialog box, the Add-Ins dialog
box, or both.

247
Part 4: Advanced VBA