Part VI: Programming Excel with VBA
906
Note
If you open an add-in created in a version prior to Excel 2007, any user interface modifications made by the
add-in will not appear as they were intended to appear. Rather, you must access the user interface items
(menus and toolbars) by choosing Add-Ins ➪ Menu Commands or Add-Ins ➪ Custom Toolbars. n
Tip
You can also download additional Excel add-ins from http://office.microsoft.com.
Why 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 pursuing
this topic further.Here are some reasons why you may want to convert your Excel workbook application to an
add-in:l Avoid confusion. If an end user loads your application as an add-in, the file isn’t visible
in the Excel window — and, therefore, is less likely to confuse novice users or get in the
way. Unlike a hidden workbook, an add-in can’t be unhidden.l (^) 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 function
named MOVAVG stored in a workbook named Newfuncs.xlsm, you have to use a syntax
such as the following to use this function in a different workbook:
=NEWFUNC.XLSM!MOVAVG(A1:A50)
However, if this function is stored in an add-in file that’s open, the syntax is much simpler
because you don’t need to include the file reference:
=MOVAVG(A1:A50)
l (^) Provide easier access. After you identify the location of your add-in, it appears in the
Add-Ins dialog box and can display a friendly name and a description of what it does.
l (^) Permit better loading control. You can automatically open add-ins when Excel starts,
regardless of the directory in which they’re stored.
l (^) Omit prompts when unloading. When an add-in is closed, the user never sees the Save
Change In prompt because changes to add-ins aren’t saved unless you specifically do so
from the VB Editor window.