Microsoft Office Tutorials and References
In Depth Information
10.2.1 The Excel Startup Folder
self-contained and eliminates the need for an installation procedure. All the main office needs to
do is email the workbook to its field offices. There are several possibilities here, however:
• Store the application and its data in the document in which it will be used. This is suitable
macros, such as those contained in code fragments throughout this topic, that we want to
run just to see how some Excel VBA feature is implemented.
• Store the application and its data in an Excel template. This is suitable, of course, when
the template will serve as the basis of multiple spreadsheets.
• Store the application and its data in a hidden Excel workbook in Excel's startup directory.
• Store the application and its data in an Excel add-in.
Each of these choices has its advantages and disadvantages, which, incidentally, vary among the
Office applications. For instance, templates are much more useful in Word than in Excel, and
addins are more useful in Excel than in Access. In any case, our interest here is in Excel.
10.2.1 The Excel Startup Folder
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
chapter) provide greater flexibility and control, and are much more suitable for macros intended
We will assume in this topic 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.
10.2.2 Excel Templates
The purpose of an Excel template is to provide a starting place for a new workbook, worksheet,
chart, or code module. Creating a template is easy. We simply create a new workbook and save it
as a template using the Save As command.
For instance, suppose we start a new workbook and enter the number 123 in the first cell of the
first sheet. Then we save the workbook in the templates directory (more on this later) as a template
called test.xlt . When we next invoke the New command from the File menu, Excel will display a