Excel 2003 VBA Programmer's Reference

13

Automation Addins and COM Addins

Since the release of Office 2000, Microsoft introduced a new concept for creating custom Addins for all the Office applications. Instead of creating application-specific Addins (.xla files in Excel, .dot files in Word, .mde files in Access, etc.), we can create DLLs using Visual Basic, C++, or the Office Developer Edition that all the Office applications can use. Since these DLLs conform to Microsoft's Component Object Model, they are known as COM Addins. The second half of this chapter explains how to create and implement your own COM Addins.

The biggest failing of COM Addins is that the functions inside them can't be called from the worksheet. Excel 2002 and carried over to Excel 2003, Microsoft extended the concept and simplified the implementation of the COM Addin mechanism, in order for their routines to be used in the same way as worksheet functions. These new Addins are known as Automation Addins.

Automation Addins

Automation Addins are COM DLLs (ActiveX DLLs) that have a creatable class and a public function in the creatable class. Similarly to other objects, you need to create an instance and invoke methods. The syntax is slightly more verbose because you are invoking the method indirectly. Instead of naming the object, method, and passing parameters, you must pass this information to the CallByName method. The basic requirement is to create the COM object and invoke CallByName passing the COM object, the function name, and the parameters. ...