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.
The appearance and behavior of Excel is influenced by countless options. You can set a large fraction of these options with the menu command ToolsOptions, 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 ToolsOptions, 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.)
With the menu command TOOLSOPTIONS 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.
The form for setting the printing options is hidden within the menu command FILEPAGE SETUP. This setting normally holds only for the active worksheet. (However, several worksheets can be selected and then set simultaneously .)
With FILESAVE ASTOOLSGENERAL OPTIONS you can set various save options (password, write protection, backup file). The command FILESAVE ASTOOLSWEB 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 TOOLSOPTIONSVIEW. The exception is the zoom factor, which is found in VIEWZOOM. 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 VIEWTOOLBARSCUSTOMIZE. (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 VIEWTOOLBARSCUSTOMIZEOPTIONS . |
Some virus protection options are hidden in the form TOOLSMACROSECURITY. 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.)
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 |
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 .
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. |
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.0\Excel\InstallRoot HKeyLocalMachine\Software\Microsoft\Office.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.0\Excel\Options HKey_Current_User\Software\Microsoft\Office.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.)
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 FILEOPEN 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 VIEWTOOLBARSCUSTOMIZETOOLBARSATTACH . This makes sense when the toolbar is to made available to other users (possibly on another computer). |
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 TOOLSOPTIONSGENERALALTERNATE 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 WINDOWUNHIDE.
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 TOOLSMACROSECURITYTRUSTED 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. |
Tip | If you wish to launch Excel without having certain files open automatically, you may use the command line option /s . Thus execute STARTRUN and input excel /s . |
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 FILENEW (see Figure 5-16), you must have saved them in one of the following folders:
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 TOOLSOPTIONSGENERAL.
Book.xlt and Sheet.xlt can be used to set a host of options that otherwise would have to be set in every sheet, causing much unnecessary work, such as print format templates, settings for page layout including headers and footers, and window options (gridlines, zoom factor, form of the row and column headings).
Tip | Templates and their extensive possibilities are described more fully in Chapter 9. There the support of input of formulas by VBA code ( intelligent forms) will be explained. |
In principle, charts can also be saved as templates. For charts Excel prefers a more practical variant: user-defined formats (previously autoformats ). These chart templates are stored in the file Officedirectory\Office\n\Xl8galry.xls . The chart types thus defined can be accessed with the command CHART TYPECUSTOM TYPES of the chart context (pop-up) menu.
To save your custom chart templates you first create a chart according to your specifications. Then select the above-mentioned command and click on the button ADD. You can now give a name to your chart type as well as a short description. The chart template will be saved in Xl8galry.xls and is available in the future whenever Excel is launched.
A large part of all configuration data is stored (independently of whatever templates are later used) directly in individual Excel files, such as information on the arrangement of windows for that file, window options (such as gridlines, zoom factor, for of row and column headers), type styles, and page setup (printing parameters).
The place for storing these data is at once practical and impractical: practical, because most Excel options can be set individually for each file (to some extent for each individual worksheet and window); and impractical because often the same settings (such as relate to headers and footers) have to be set over and over again. This is burdensome, and one would rather not have to do it. You can minimize this formatting task by using automatic templates, but then you cannot later make changes. (Changes in templates affect only new Excel files, not those that already exist.)
As you can read in Chapter 14 in full detail, Excel recognizes two types of add-ins. Traditional add-ins are in principle normal Excel files, which, however, are stored as add-in files with the file extension *.xla . On the other hand, COM add-ins are ActiveX DLLs with the extension *.dll . COM add-ins can be created only with Office Developer.
So that traditional add-in files are recognized as such and can be activated in the add-in manager (TOOLSADD-INS) they have to be installed in one of the following two folders:
Userdirectory\Application Data\Microsoft\AddIns global Officedirectory\Office\Library personal
In the case of COM add-ins the installation location can be anywhere on the hard drive. What is important is that the COM add-ins be correctly registered in the registry. This task is usually handled by a separate installation program.