When Excel loads, it automatically loads any spreadsheets stored in its startup and alternate startup folders. The default location of the startup folder is usually a subfolder of the main Excel folder named XlStart. By default, there is no alternate startup folder, although one can be defined using the General tab of the Options dialog; to open it, select Options from the Tools menu.
Because the contents of these folders are opened at startup as ordinary workbooks, their macros are easily accessible to all other Excel workbooks. This makes them ideal as a storage location for macros. The only drawback is that Excel actually opens the spreadsheets stored in these directories; to prevent this, they should be hidden by selecting the Hide option from Excel's Window menu (not the Format menu) when the spreadsheet to be hidden is active.
Macros that are stored in the startup and alternate startup folders are available from the Macro dialog, and we can assign them to toolbars and menus through the Excel user interface, as well as programmatically. (On the other hand, an add-in, which is discussed later in this chapter, does not make its subroutines directly accessible to other Excel workbooks, but instead requires that they be assigned to toolbar or menu items programmatically.)
A workbook stored in either of these folders is an excellent choice for a library of macros that you want to be globally available to your spreadsheets. It is also suitable for developing Excel macros for others to use, although Excel add-ins (which are discussed in Section 10.2.3 later in this chapter) provide greater flexibility and control, and are much more suitable for macros intended for distribution.
We will assume in this book that you want to store macros in an add-in. As we will see, there are clear advantages to using add-ins. Moreover, this will give us a chance to discuss how add-ins are created in Excel. However, you can feel free to place the example macros in a spreadsheet that is kept in the startup or alternate startup folder.
Was this article helpful?