Configuration Files, Custom Configuration

There are numerous ways in which Excel can be configured to the user's specifications:

  • custom toolbars,
  • changing and extending the menu system,
  • changing general options,
  • use of templates,
  • extension of Excel by macros in the personal macro workbook and/or add-ins.

This section describes how to carry out and save such configuration settings.

Options

The appearance and behavior of Excel is influenced by countless options. You can set a large fraction of these options with the menu command Tools|Options, others with various other commands. This section discusses briefly some of the most important options (both manual changes and changes via program code). Please observe that in spite of their centrally located setting through Tools|Options, options have various realms of validity (Excel as a whole, a workbook, a window) and are stored in various files! (Details on configuration files appear in the next section.)

What Can Be Set with the Command Options?

With the menu command TOOLS|OPTIONS you open a form with eight tabs, in which numerous Excel options can be set. The meaning of most of these settings is obvious or can be read about in the on-line help, so that here we do not need an extensive discussion.

Other Settings

The form for setting the printing options is hidden within the menu command FILE|PAGE SETUP. This setting normally holds only for the active worksheet. (However, several worksheets can be selected and then set simultaneously.)

With FILE|SAVE AS|TOOLS|GENERAL OPTIONS you can set various save options (password, write protection, backup file). The command FILE|SAVE AS|TOOLS|WEB OPTIONS opens up a further form with a host of options that govern the conversion of a document to HTML format.

Note / The file and print options hold only for the active file. Unlike Word, Excel cannot easily be given such default settings as always to make a backup copy when a file is saved or to maintain a particular page format. One possible solution to this problem is the use of templates (see Chapter 9).

Window options are to be found, with one exception, in TOOLS|OPTIONS|VIEW. The exception is the zoom factor, which is found in VIEW|ZOOM. Window options are valid only for the currently active window (and not for the entire workbook or in general for Excel). Options for the display of toolbars are set with VIEW|TOOLBARS|CUSTOMIZE. (For details see Chapter 8.)

Tip / Office 2000 has the peculiarity of at first displaying incomplete menus. The missing entries appear only after a while. It seems that in its benevolence Microsoft believes that this arrangement simplifies the life of the user. However, you can undo this nonsense by deactivating the option MENUS SHOW RECENTLY USED COMMANDS FIRST in the form VIEW|TOOLBARS|CUSTOMIZE|OPTIONS.

Some virus protection options are hidden in the form TOOLS|MACRO|SECURITY. Where these settings are stored is fortunately not documented (in any case, not in the form just described). A change in these options by VBA code was not planned for. (But there are doubtless inventive programmers who will figure it out, who will also manage to pull it off with calls to certain API functions.)

Setting Options with Program Code

Most Excel options are set with numerous properties of the Application object. Options that do not affect Excel as a whole, but only a file, a window, or a chart, for example, can be changed via the properties of the corresponding object (Worksheet, Window, etc.), where the association is not always logical.

The settings for the page format, headers and footers, and so on, are carried out with the PageSetup object, which is set for every sheet object (WorkSheet, Chart, etc.) and can also be addressed with the Window object. It is not possible to change all at once the page format of several sheets through program code. (Execute a loop over the sheets in question and change PageSetup for each individual object.)

The active printer, on the other hand, is set with the ActivePrinter property of the Application object. However, there is no possibility of using VBA code to obtain a list of all available printers.

The following tables give an overview of the most important properties and methods.

APPLICATION OBJECT (GENERAL OPTIONS)
ActivePrinter / set the currently active printer
AddIns(…) / access to add-ins
AutoRecover.Enabled / determines whether Excel 2002 should automatically make a backup copy
AutoRecover.Path / specifies the directory for backup copies
Calculation / recalculation of worksheets automatic/manual
CommandBars(…) / access to menu bars and toolbars (see Chapter 8)
DisplayAlerts / display alerts
DisplayFormulaBar / formula bar on/off (True/False)
DisplayFullScreen / full screen mode on/off
DisplayNoteIndicators / red markings in cells to indicate notes
DisplayStatusBar / status bar
ErrorCheckingOptions / options for error checking
MoveAfterReturn / cursor moves on Return into the next cell of a table
MoveAfterReturnDirection / direction of cursor movement on Return
OnEvent … / various event procedures (see Chapter 4)
PromptForSummaryInformation / form for input of information on saving
ScreenUpdating / update screen during macro execution
SheetsInNewWorkbook / number of empty worksheets in a new file
SmartTagRecognizers.Recognize / activation of the smart tag function (Excel 2002)
SmartTagRecognizers(n).Enabled / activation of individual smart tag modules (Excel 2002) activation of the smart tag module (Excel 2002)
Speech.SpeakCellOnEnter / automatic speech output of the cell's contents
StandardFont / name of the default font in worksheets
StandardFontsize / size of the default font in worksheets
WORKBOOK OBJECT (FILE-SPECIFIC OPTIONS)
ChangeFileAccess / change access privileges
Colors / access file's color palette (56 colors)
CreateBackup / create backup file on saving
DisplayDrawingObjects / display drawing objects
EnableAutoRecover / turn on/off automatic backup copy for the file (Excel 2002)
Protect / turn write protection on and off
SmartTagOptions.DisplaySmartTags / displays smart tags (Excel 2002)
SmartTagOptions.EnableSmartTags / save smart tags with file (Excel 2002)
Styles(…) / access to templates
Visible / file visible/invisible (hidden)
WORKSHEET OBJECT (WORKSHEET-SPECIFIC OPTIONS)
DisplayAutomaticPageBreaks / display page breaks in worksheets
EnableAutoFilter / enables display of autofilters
EnableOutlining / enables display of grouping
EnablePivotTable / enables the creation of pivot tables
FilterMode / autofilter on/off
PageSetup / access to page and printer settings
Protection / sheet protection options
SetBackgroundPicture / set background picture
Visible / worksheet is visible/invisible
WINDOW OBJECT (WINDOW-SPECIFIC OPTIONS)
DisplayFormulas / display formulas instead of results
DisplayGridlines / display gridlines
DisplayHeadings / display row and column headings
DisplayHorizontalScrollbar / display horizontal scroll bar
DisplayOutline / display grouping
DisplayZeros / display 0 values (or display empty cell)
DisplayVerticalScrollbar / display vertical scroll bar
DisplayWorkbookTabs / display workbook tabs
FreezePanes / split window frozen/unfrozen
GridLineColor / set color (RGB value) of gridlines
GridLineColorIndex / color of gridlines from the color palette (0 to 55)
PageSetup / access to page and printer settings
Split / window split/not split
SplitColumn / column in which the window is split
SplitRow / row in which the window is split
TabRatio / ratio of tab area to horizontal scroll bar
Zoom / zoom factor
PAGESETUP OBJECT (PAGE LAYOUT, SET SEPARATELY FOR EACH SHEET)
BlackAndWhite / print in black and white
BottomMargin / bottom margin, in points (1/72 inch = 0.35 mm)
CenterFooter / footer, central part
CenterHeader / header, central part
CenterHorizontal / print horizontally centered
CenterVertical / print vertically centered
FirstPageNumber / start value for pagination
FooterMargin / size of footer
HeaderMargin / size of header
LeftFooter / footer, left part
LeftHeader / header, left part
LeftMargin / left margin in points (= 1/72 inch = 0.35 mm)
Orientation / print in vertical or horizontal format
PaperSize / paper size
PrintArea / area of page to be printed
PrintTitleColumns / column title (printed on each page)
PrintTitleRows / row title (printed on each page)
RightFooter / footer, right side
RightHeader / header, right side
RightMargin / right margin in points (= 1/72 inch = 0.35 mm)
TopMargin / top margin in points (= 1/72 inch = 0.35 mm)
DEFAULTWEBOPTIONS (EXCEL GLOBAL) / WEBOPTIONS (FILE-SPECIFIC)
AllowPNG / encode pictures in PNG format
DownloadComponents / download missing web components.
Encoding / desired character set for web browser
LocationOfComponents / location to which web components are to be saved
OrganizeInFolder / save pictures, etc., in their own folder
RelyOnCSS / use Cascading Style Sheets
RelyOnVML / use Vector Markup Language

Configuration Files

Basics

Most current operating systems are automatically configured in such a way that a personal directory (folder) is available to each user (that is, for each login name or each account). The location of this folder depends on both the operating system and the individual configuration.

For example, if you are logged in as the administrator under Windows 2000, the personal folder is C:\Documents and Settings\Administrator. This folder is recommended as the location to save your personal files.

Of course, there is a reason for describing the fundamentals of Windows in such detail: The user-specific configuration files of Excel are stored in subdirectories of the personal folder. For the remainder of this section this folder will be abbreviated as Userdirectory.

Some additional configuration files are stored relative to the installation folder of Office 2000. This folder will hereinafter be abbreviated as Officedirectory.

Overview of Excel Configuration Files

Excel strews information about the current configuration and the setting of options all over the hard drive. The abundance of configuration files becomes more and more difficult to keep track of with each new version.

  • Some individual settings are saved in the Windows registry.
  • Information on the individual content and placement of toolbars is located in Userdirectory\Application Data\Microsoft\Excel\Excel.xlb.
  • The personal macro workbook is stored in Userdirectory\Application Data\Microsoft\Excel\Xlstart\Personal.xls.
  • Globally available macros can be stored in arbitrary files in the folder Officedirectory\Office\Xlstart.
  • Personal templates are stored in Userdirectory\Application Data\Microsoft\Templates.
  • The appropriate folder for storing global templates is Officedirectory \Office\Xlstart.
  • Global add-in files are stored in Officedirectory\Office\Library.
  • Personal add-in files, however, are located in Userdirectory\Application Data\Microsoft\AddIns.
  • Predefined (that is, included with Excel) chart templates are stored in Officedirectory\Office\n\Xl8galry.xls. Here n is the language code.
  • User-defined chart templates are stored in Userdirectory\Application Data\Microsoft\Excel\Xlusrgal.xls.
  • Backup copies of all opened files are stored by default in Excel 2002 in the directory Userdirectory\Application Data\Microsoft\Excel\. This path can be changed (property Application.AutoRecover.Path).
  • All remaining settings are file-specific and are stored in their own Excel files.

Tip / File names and paths of configuration files change with every version, not least to avoid conflicts with the simultaneous use of several versions of Office. If you wish to create portable Excel applications, you should not rely on the configuration files being located in any particular place.
Tip / You can find an overview of Excel properties for accessing most of the above-mentioned folders, in the section of Excel-specific methods and properties.

Settings in the Office Registry

The Microsoft Office registry contains an entry in one of the folders (9.0 for Excel 2000, 10.0 for Excel 2002)

HKeyLocalMachine\Software\Microsoft\Office\9.0\Excel\InstallRoot

HKeyLocalMachine\Software\Microsoft\Office\10.0\Excel\InstallRoot

that identifies the Office installation directory on the hard drive (that is, the folder that is usually denoted in this book by Officedirectory). Furthermore, various individual settings are stored in the registry, and in fact, in the following location:

HKey_Current_User\Software\Microsoft\Office\9.0\Excel\Options

HKey_Current_User\Software\Microsoft\Office\10.0\Excel\Options

These settings can be edited with the programs RegEdit.exe and RegEdt32.exe (see Figure 5-14). (However, it is not recommended to do so unless you know what you are doing. By changing the registry you can screw up Office and even Windows to such an extent that they will have to be reinstalled.)


Figure 5-14: The registry editor in Windows 2000

Information on Toolbars in Excel.xlb

The file Userdirectory\Application Data\Microsoft\Excel\Excel.xlb is automatically created for each user the first time a change in a toolbar is made. The file contains information on the arrangement of the toolbars and the office assistant for making changes in the given toolbars, paths to the associated macro functions, and new toolbars that were available the last time Excel was exited.

*.xlb files can be opened with FILE|OPEN and then be used to change the current state of the toolbars. The last valid state when Excel is exited will automatically be saved. However, there is no way of saving the file in a menu command or a macro without simultaneously leaving Excel.

Tip / It is possible to store user-defined toolbars directly in an Excel file with VIEW|TOOLBARS|CUSTOMIZE|TOOLBARS|ATTACH. This makes sense when the toolbar is to made available to other users (possibly on another computer).

Macro Templates in the Xlstart Folder

Starting with Excel 2000 the "personal macro workbook" is justly named, since every user actually receives his own version. (In earlier versions of Excel all users of a particular computer had to share this workbook.) For this reason there is no longer just one, but two Xlstart folders:

Userdirectory\Application Data\Microsoft\Excel\Xlstart personal

Officedirectory\Office\Xlstart global

When it is launched, Excel first loads all *.xls files from the personal Xlstart folder, then all *.xls files from the global Xlstart folder. The personal macro workbook with the name Personal.xls is not given any preference. So far as I have been able to determine, Excel simply loads all files in alphabetical order (but always first the personal, then the global files). The order is not documented, so do not depend on it.

To be precise, in addition to the two Xlstart folders just referred to, there is a third folder whose *.xls files are automatically loaded upon launch of Excel. The location of this folder is not predetermined, but can be set with TOOLS|OPTIONS|GENERAL|ALTERNATE STARTUP FILE LOCATION.

This location is of particular interest if global macro files are to be shared across a network. In that case a network directory can be given here. The information about this additional start directory is stored in the registry.

In practice, the Xlstart directories are used primarily to load automatically VBA code that is meant to be always available. Of course, VBA can automatically be executed, for example with a Workbook_Open event procedure. Files in the directory Xlstart can thus also be used to carry out extensions or changes in the menu structure.

Workbooks from the Xlstart folders are usually stored in a "hidden" state, so that they are not visible on the screen and appear only in the VBA development environment. Invisible files can be made visible with WINDOW|UNHIDE.

A special place within the personal Xlstart files is held by the personal macro workbook Personal.xls. In this workbook all newly recorded macros are automatically stored if in the options for macro recording the entry "personal macro workbook" has been activated. If you never use user-created macros, then this file does not even exist.

Caution / Excel normally displays a virus warning before files with VBA code are loaded. However, this does not automatically hold for files that come from the Xlstart folder. The reason for this is the option TRUST ALL INSTALLED ADD-INS AND TEMPLATES, which is the default (see Figure 5-15).You can change this in TOOLS|MACRO|SECURITY|TRUSTED SOURCES.
This default setting makes sense, of course. It would be a pain in any one of a number of places if every time Excel was launched it asked permission to run the macros that you personally had stored in Personal.xls and other Xlstart folders. On the other hand, one could imagine that a virus programmer would take delight in this breach of security in Excel.


Figure 5-15: Files in the Xlstart folder are considered safe.

Tip / If you wish to launch Excel without having certain files open automatically, you may use the command line option /s. Thus execute START|RUN and input excel /s.

Templates

Templates are Excel files with the file extension *.xlt, which serves as the model for new worksheets, charts, and workbooks. These are in principle normal Excel files for which the file type "Template" was given in SAVE UNDER.

In order for templates to appear as selections in the form FILE|NEW (see Figure 5-16), you must have saved them in one of the following folders:


Figure 5-16: Selection from the installed templates

Userdirectory\Application Data\Microsoft\Templates personal

Officedirectory\Office\Xlstart global

additional start folder depending on setting

If one of the two Xlstart folders or the additional start folder contains files with the names Book.xlt, Sheet.xlt, and Chart.xlt, these files are considered the automatic templates and will be used (without asking) as templates when new workbooks are created with the tool NEW or when new worksheets or chart sheets are appended to preexisting worksheets.

Sheet.xlt and Chart.xlt can contain only a worksheet or chart sheet. Book.xlt can contain an arbitrary combination of sheets. The number of worksheets contained in Book.xlt has priority over the setting of the number of empty worksheets in TOOLS|OPTIONS|GENERAL.