Working with VBA Add-Ins in Excel 2016 - dummies

By John Walkenbach

You load and unload add-ins in Excel 2016 by using the VBA Add-Ins dialog box. To display this dialog box, choose File  →  Options  →  Add-Ins. Then select Excel Add-Ins from the drop-down list at the bottom of this dialog screen and click Go. Or take the fast track and choose Developer  →  Add-Ins  →  Add-Ins. But the easiest method is to just press Alt+TI (the old Excel 2003 keyboard shortcut).

Any of these methods displays the Add-Ins dialog box. The list box contains the names of all add-ins that Excel knows about. In this list, check marks identify any currently open add-ins. You can open and close add-ins from the Add-Ins dialog box by selecting or deselecting the check boxes.

The Add-Ins dialog box lists all the add-ins known to Excel.
The Add-Ins dialog box lists all the add-ins known to Excel.

To add a new add-in to the list, click Browse and then locate the XLAM file.

You can also open most add-in files (as though they were workbook files) by choosing the File  →  Open  →  Browse command. An add-in opened in this manner does not appear in the Add-Ins dialog box. In addition, if you open the add-in by choosing the Open command, you can’t close it by choosing File  →  Close. You can remove the add-in only by exiting and restarting Excel or by writing a macro to close the add-in.

When you open an add-in, you may or may not notice anything different. In many cases, however, the Ribbon changes in some way; Excel displays a new tab or one or more new groups on an existing tab. For example, opening the Analysis ToolPak add-in gives you a new item on the Data tab: Analysis  →  Data Analysis. If the add-in contains only custom worksheet functions, the new functions appear in the Insert Function dialog box, and you see no change in Excel’s user interface.