O'Reilly logo

Excel® 2007 VBA Programmer's Reference by Michael Alexander, Rob Bovey, Stephen Bullen, John Green

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required