Excel® 2007 VBA Programmer's Reference

Chapter 18. Automation Add-Ins and COM Add-Ins

With the release of Office 2000, Microsoft introduced a new concept for creating custom Add-Ins for all the Office applications. Instead of creating application-specific Add-Ins (xlam in Excel, dotm in Word, and so on), you can create DLLs using Visual Basic, C++, or .NET that all the Office applications can use. Because these DLLs conform to Microsoft's Component Object Model, they are known as COM Add-Ins. The second half of this chapter explains how to create and implement your own COM Add-Ins.

In Excel 2002, Microsoft extended the concept and simplified the implementation of the COM Add-In mechanism, so their functions could be used in the same way as worksheet functions and VBA user-defined functions. These Add-Ins are known as Automation Add-Ins.

In Excel 2007, Microsoft has further extended COM Add-Ins to support application-level customization of the Ribbon and the creation of custom task panes.

Automation Add-Ins

Automation Add-Ins are COM DLLs (ActiveX DLLs) that have a creatable class and a public function in the creatable class. For example, when Excel can do the following with your class, you can then call FunctionName from the worksheet:

Dim oAutoAddin As Object

Set oAutoAddin = CreateObject("TheProgID")
TheResult = CallByName(oAutoAddin, "FunctionName", _
                       VbMethod, param1, param2, ...)

In other words, your function must satisfy the following conditions:

• The class must be publicly creatable (that is, have an Instancing property ...