Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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 for an add-in. An Excel add-in is basically a normal XLSM workbook with the following differences:

• The IsAddin property of the ThisWorkbook object is True . By default, this property is False .

• The workbook window is hidden in such a way that it can't be unhidden by choosing the View Window Unhide command. This means that you can't display worksheets or chart sheets contained in an add-in unless you write code to copy the sheet to a standard workbook.

• An add-in is not a member of the Workbooks collection. Rather, it's a member of the AddIns collection. However, you can access an add-in via the Workbooks collection (see "XLAM file VBA collection membership," later in this chapter).

• Add-ins can be installed and uninstalled by using the Add-Ins dialog box. To display this dialog box, choose Office Excel Options Add-Ins. Then, in the Excel Options dialog box, choose Excel Add-Ins from the Manage drop-down list and click Go. After it's installed, an add-in remains installed across Excel sessions.

• The Macro dialog box (invoked by choosing Developer Code Macros or View Macros Macros) does not display the names of the macros contained in an add-in.

• A custom worksheet function stored within an add-in can be used in formulas without having to precede its name with the source workbook's filename.

Why create add-ins?

You might decide to convert your Excel application into an add-in for any of the following reasons:

• To restrict access to your code and worksheets: When you distribute an application as an add-in and you protect its VBA project with a password, users can't view or modify the sheets or the VBA code in the workbook. Therefore, if you use proprietary techniques in your application, you can prevent anyone from copying the code - or at least make it more difficult to do so.

• To avoid confusion: If a user loads your application as an add-in, the file is not visible and is, therefore, less likely to confuse novice users or get in the way. Unlike a hidden workbook, an add-in can't be unhidden.

• To simplify access to worksheet functions: Custom worksheet functions stored within an add-in don't require the workbook name qualifier. For example, if you store a custom function named MOVAVG in a workbook named Newfuncs.xlsm , you must use a syntax like the following to use this function in a formula that's in a different workbook:

  =Newfuncs.xlsm!MOVAVG(A1:A50)

  But if this function is stored in an add-in file that's open , you can use a much simpler syntax because you don't need to include the file reference:

  =MOVAVG(A1:A50)

• To provide easier access for users: After you identify the location of your add-in, it appears in the Add-Ins dialog box with a friendly name and a description of what it does.

• To gain better control over loading: Add-ins can be opened automatically when Excel starts, regardless of the directory in which they are stored.

• To avoid displaying prompts when unloading: When an add-in is closed, the user never sees the Do you want to save change? prompt.

Excel also supports COM (Component Object Model) add-ins. These files have a .dll or .exe file extension. A COM add-in can be written so it works with all Office applications that support add-ins. An additional advantage is that the code is compiled, thereby offering better security. Unlike XLAM add-ins, a COM add-in cannot contain Excel sheets or charts . COM add-ins are developed with Visual Basic 5 (or later) or Visual Basic .NET. Discussion of creating COM add-in procedures is well beyond the scope of this book.