Chapter 48: Creating Custom Excel Add-Ins
48
Pressing Alt+T followed by I is a much faster way to display the Add-Ins dialog box. Or, if the Developer tab is visible,
choose Developer ➪ Add-Ins ➪ Excel Add-Ins.
You can also open most add-in files by opening the file using File ➪ Open. After an add-in is opened, however, you
can’t choose File ➪ Close to close it. The only way to remove the add-in is to exit and restart Excel or to write a
macro to close the add-in. Therefore, you’re usually better off opening the add-ins by using the Add-Ins dialog box.
The user interface for some add-ins (including those included with Excel) may be integrated
into the Ribbon. For example, when you open the Analysis ToolPak add-in, you access these
tools by choosing Data ➪ Analysis ➪ Data Analysis.If you open an add-in created in a version prior to Excel 2007 (an *.xla file), any user interface modifications made
by the add-in will not appear as intended. Instead, you must access the user interface items (menus and toolbars) by
choosing Add-Ins ➪ Menu Commands or Add-Ins ➪ Custom Toolbars. The Add-ins tab will show on the Ribbon only if
an add-in is loaded that uses the old menu and CommandBar user interface.
Understanding Why to Create Add-Ins
Most Excel users have no need to create add-ins. However, if you develop spreadsheets for
others—or if you simply want to get the most out of Excel—you may be interested in pur-
suing this topic further.Here are some reasons why you may want to convert your Excel workbook application to an
add-in:To avoid confusion If an end user loads your application as an add-in, the file isn’t vis-
ible in a window—and, therefore, is less likely to confuse novice users or get in the way.
Unlike a hidden workbook window, an add-in can’t be unhidden.
To simplify access to worksheet functions Custom worksheet functions stored in an
add-in don’t require the workbook name qualifier. For example, if you have a custom func-
tion named MOVAVG stored in a workbook named Newfuncs.xlsm, you have to use syntax
such as the following to use this function in a different workbook:
=NEWFUNCS.XLSM!MOVAVG(A1:A50)If this function is stored in an add-in file that’s open, however, the syntax is much simpler
because you don’t need to include the file reference:
=MOVAVG(A1:A50)