Excel® 2007 Power Programming with VBA

Chapter 21. Creating and Using Add-Ins

In This Chapter

In this chapter, I explain the benefits of using add-ins, and I show you how to create your own add-ins by using only the tools built into Excel.

• An overview of add-ins and why this concept is important for developers

• Details about Excel’s Add-In Manager

• How to create an add-in, including a hands-on example

• How XLSA add-in files differ from XLSM files

• Examples of VBA code that manipulates add-ins

• How to detect whether an add-in is installed properly

One of Excel’s most useful features for developers is the ability to create addins. Creating add-ins adds a professional touch to your work and, as you’ll see, add-ins offer several key advantages over standard workbook files.

What Is an Add-In?

Generally speaking, a spreadsheet add-in is something added to a spreadsheet to give it additional functionality. For example, Excel ships with several add-ins. One of the most popular is the Analysis ToolPak, which adds statistical and analysis capabilities that are not built into Excel.

Some add-ins also provide new worksheet functions that can be used in formulas. With a well-designed add-in, the new features blend in well with the original interface, so they appear to be part of the program.

Comparing an add-in with a standard workbook

Any knowledgeable Excel user can create an add-in from an Excel workbook file; no additional software or programming tools are required. Any workbook file can be converted to an add-in, but not every workbook is appropriate ...