C H A P T E R    36 Part 6  Microsoft Office Architecture Microsoft Office Resource Kit

Microsoft Excel Architecture Previous

Contents
Next

Index


In This Chapter
How Microsoft Excel Is Structured
How You Can Customize Microsoft Excel
How Microsoft Excel Resolves Conflicts

This chapter describes the structure of Microsoft Excel 97 for Windows and Excel 98 for the Macintosh. It also explains how the various components of Excel work together, and how the application handles changes that users make to those components. An understanding of the architecture of Excel can help you take advantage of its features and may help you troubleshoot problems.

See Also

Top

How Microsoft Excel Is Structured

Excel documents are called workbooks. A workbook is a collection of worksheets, chart sheets, and Visual Basic for Applications modules. The worksheet is the primary location for storing data in a workbook. Each worksheet can contain cells that store data and formulas, and charts, which present data graphically. Each cell and chart item can have associated formatting, such as fonts, colors, and layout options.

You can save a workbook as a template. A template provides a pattern for creating new workbooks. You can also save workbooks as add­ins, which programmatically add custom features to Excel. The following illustration shows how Excel components fit together to create a workbook.

Workbooks

The workbook in Excel is analogous to the document in Microsoft Word, or the presentation in Microsoft PowerPoint. The workbook stores data in the Excel file format. Excel workbook files have the extension .xls (Windows) or the file type Excel Document (Macintosh).

The various components of Excel, such as cell data and formulas, are stored in the workbook file. Some components, such as cell data and charts, are stored on worksheets in the workbook. Other components, such as macros, book­level security settings, and page settings, are stored in the workbook file but not on worksheets.

Cell Data

Cell data consists of constant values stored in cells and can be either of the following:

  • Numeric values, including date, time, currency, percentage, or scientific notation

  • Text

The way Excel displays numeric values in a cell depends on the number format assigned to a cell. The numeric value displayed may differ from the actual value Excel stores, which is with 15 digits of accuracy. By default, Excel makes calculations based on the stored value. Such calculation is known as full precision calculation. However, you can have Excel calculate based on displayed values. To do this type of calculation, click Options (Windows) or Preferences (Macintosh) on the Tools menu, click the Calculations tab, and then select Precision as displayed.


My calculations appear to be inaccurate

If the results you get from a formula appear to be wrong, it may be due to the difference in precision between displayed and stored values. For example, if two cells each contain the value 1.007, and a formula adds them in a third cell, the result is 2.014. If all three cells are formatted to display two decimal places, Excel rounds the values. Using full precision calculation on this formula, the displayed calculation, 1.01+1.01=2.01, appears to be wrong. On the other hand, a calculation on the displayed values, 1.01+1.01=2.02, appears to be correct, but results in a value that is not as precise as a full precision calculation would be.

Calculating with precision as displayed does the following:

  • Affects all worksheets in the active workbook.

  • Does not affect numbers in the General format, which are always calculated with full precision.

  • Slows calculation because Excel must round the numbers as it calculates.


    Caution   Once you switch to calculating on displayed values, Excel stores all constant values as their displayed values, and full precision values cannot be restored.


Cell Formulas

Formulas use cell references when performing calculations on your data, and are part of the data that is stored in the workbook. Cell references in a formula can be relative, absolute, or mixed references in any of the following reference styles:

  • A1 style

  • Row­and­column (R1C1) style

  • Name references

Both A1 and row­and­column reference styles refer to data by position. Using these styles, you may experience difficulty with formulas if you reposition or delete cells. One way to avoid this problem is to reference cells by name.

Name References

You can use a name as a reference to a cell, a group of cells, a value, or a formula. Name references can be accessible to an entire workbook or restricted to a worksheet. When a name reference is restricted to a worksheet, it can be repeated on more than one sheet so that it defines related cells on different sheets in the same workbook. A book­level name reference, on the other hand, cannot be repeated on more than one worksheet. Instead, it can be used throughout the workbook to refer to cells on one worksheet. Using book­level names eliminates the need to recreate names for each new worksheet or to type worksheet references in formulas. Sheet­level names override book­level names when used on the sheet where they are defined.

To use book­level name references, you enter the name you want to use in the name box on the formula bar. To use sheet­level name references, however, you must include the name of the sheet when you enter the name, such as Sheet1!Profit.

Tip   As an alternative to using name references in formulas, you can often use spreadsheet labels (such as category names you have added to a worksheet) instead. For example, the label of the value at the intersection of a column labeled January and a row labeled Unit Sales is January Unit Sales. For more information about using natural language formulas, see "Intelligent Applications in Microsoft Excel" in Chapter 2, "What's New in Microsoft Office."

Scenarios

The Scenarios command (Tools menu) is a tool for creating specialized formulas which pose what­if questions with your data. Scenarios can be sheet­level or book­level.

To create a scenario

  1. On the Tools menu, click Scenarios, and then click Add.

  2. In the Scenario name box, type a name for the scenario.

  3. In the Changing Cells box, enter the references for the cells where you want to store hypothetical data.

  4. Under Protection, select the options you want, and then click OK.

  5. In the Scenario Values dialog box, type the values you want in the changing cells.

Note   For the protection options to take effect, you must activate protection for the current sheet. For information about sheet­level security, see "Security Settings" later in this chapter.

You can copy scenarios from other worksheets and other workbooks to the active worksheet. This task is known as merging scenarios. To merge scenarios among workbooks, all the workbooks must be open.

To merge scenarios

  1. On the Tools menu, click Scenarios, and then click Merge.

  2. In the Book box, click a workbook name.

  3. In the Sheet box, click the names of the worksheets that contain the scenarios you want to merge.

When you merge scenarios, there may be some duplicate names. Best Case and Worst Case, for example, are common scenario names. In such instances, Excel appends additional information to the duplicate scenario names, such as creation date, creator name, or an ordinal number.

Cell Formatting and Styles

Styles are collections of format settings for cells. Styles are stored separately from the cell data, which means they can be copied between cells, changed, or deleted, without affecting the data in the cell.

The following table shows the format settings stored in styles.

This format setting Determines
Number Decimal places, separator, inclusion of dollar sign, style for displaying negative numbers, and other options for formatting different kinds of numbers such as currency, dates, fractions, and so on.
AlignmentHorizontal and vertical alignment, text orientation, and whether text wraps in the cell.
FontFont name, style, size, special effects, and color of the text in the cell.
BorderPlacement and style of the border of the cell.
PatternShading and color of the cell.
ProtectionWhether data in the cell is locked or the formula is hidden. This option does not take effect until you protect the sheet by clicking Protect Sheet (Tools menu, Protection submenu).

Styles are saved in the workbook. If you want to reuse styles in another workbook, you can do either of the following:

  • Copy the styles to another workbook.

  • Save the workbook as a template.

When you copy the styles to other workbooks, you insert the styles into existing workbooks. When you save the workbook as a template, however, you automatically copy the styles to new workbooks based on this template. For information about creating templates, see "Templates" later in this chapter.

To copy styles from one workbook to another

  1. Open the source and destination workbooks for the styles you want to copy.

  2. On the Format menu, click Style.

  3. Click Merge, and select the workbook from which you want to merge styles.

All styles from the source workbook are merged into the destination workbook. If styles in the destination workbook have names that match styles being merged, you are prompted to choose whether or not to overwrite existing styles in the destination workbook.

The Normal style is likely to match in the source and destination workbooks. All cells in a new workbook are initially formatted with the Normal style. You can change the settings for the Normal style, but the change does not affect new workbooks unless you save the workbook that contains the new Normal style as an autotemplate. For information about autotemplates, see "Autotemplates" later in this chapter.

  Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to set the Standard Font option on the General tab in the Options dialog box (Tools menu) for all Excel users in your workgroup. In the System Policy Editor, set the following policy:

User\Excel 97\Tools_Options\General\Font

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Charts

You create charts based on a range of selected cells on a worksheet. To create a chart, click Chart (Insert menu), and then follow the instructions in the Chart Wizard. In the Chart Location panel, the chart is stored on its own sheet (called a chart sheet) if you select the As new sheet option; if you select the As object in option, the chart is embedded in a worksheet. Regardless of which type of sheet a chart is stored on, Excel stores charts in the workbook file. You can copy charts into other workbooks, and into other Office applications, such as Word documents and PowerPoint presentations.

Charts are linked dynamically to data on a worksheet. This means that changes to the data are updated in the chart, and changes to a data marker on the chart are reflected in the linked data cells. Text on the chart can also be linked to text in worksheet cells. This text appears as titles, data labels, legend entries, and labels for axis tick­marks. Editing text in the worksheet cells affects text in the charts that are linked to the cells. You can edit text directly in charts, but this breaks the link to the cells on the worksheet.

Custom Chart Types

Instead of formatting chart items individually, you can quickly change the look of a chart using a custom chart type. (In previous versions of Excel, these were called chart autoformats.) Each custom chart type is based on one of the 14 predefined chart types, and can include a chart subtype, legend, gridline options, data labels, color settings, patterns, and layout. Excel includes several built­in custom chart types. You can also create your own custom chart types.

To create a custom chart type

  1. Select a chart that you want to save as a custom chart type.

  2. On the Chart menu, click Chart Type.

  3. On the Custom Types tab, click User­defined.

  4. Click Add.

  5. In the Name box, type a name.

    If you want to add a description, type it in the Description box.

Excel stores galleries of built­in and user­defined chart types on separate chart sheets in workbooks with reserved file names, as shown in the following tables. Note that the user­defined chart gallery file is created only after a user­defined chart type is created.

Windows chart type File name and location
Built­in chart gallery Program Files\Microsoft Office\Office\Xl8galry.xls
User­defined chart gallery Program Files\Microsoft Office\Office\Xlusrgal.xls

Macintosh chart type File name and location
Built­in chart gallery System Folder:Preferences:Excel Chart Gallery (8)
User­defined chart gallery System Folder:Preferences:Excel Chart User Gallery

To create a unified look, you can build a gallery of custom chart types for your workgroup. For example, you can create a series of custom chart types with a consistent layout and color scheme, perhaps designed to be integrated into a PowerPoint presentation.

To distribute custom chart types

  • Copy the user­defined chart gallery file Xlusrgal.xls (Windows) or Excel Chart User Gallery (Macintosh) to each user's computer in the location shown in the following table.

    Operating system Folder name and location
    WindowsProgram Files\Microsoft Office\Office
    MacintoshSystem Folder:Preferences

  Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to specify the path to a standard user­defined chart gallery on a network drive for all Excel users in your workgroup. In the System Policy Editor, set the following policy:

User\Excel 97\Miscellaneous\Chart Gallery

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Macros

Macros are stored in Visual Basic modules in workbooks. Unlike previous versions of Excel, Visual Basic modules are not stored on a module or macro sheet. Instead, you view Visual Basic code with the Visual Basic Editor command (Tools menu, Macro submenu). You can distribute macros in the following ways:

  • As workbooks

    You can store macros in a workbook. To make the macros available each time Excel starts, store the workbook in the startup or alternate startup folder.

  • As add­ins

    You can distribute the macros as a standalone, customized version of Excel by saving a workbook as an add­in. To automatically open the add­in each time Excel starts, store the add­in in the startup or alternate startup folder. For information about startup folders, see "Startup and Alternate Startup Folders," later in this chapter.

Security Settings

You can secure specific sheets or entire workbooks. To configure security settings, including passwords, point to Protection (Tools menu), and then click a command. Security settings are stored in the workbook file. The only way to change them is to open the workbook, modify the security settings, then save the file.

At the sheet level, you can secure items as shown in the following table.

Securing this item Protects it in this way
Sheet contentsPrevents editing or deleting cells on worksheets, as well as items in chart sheets
Sheet objectsPrevents moving, editing, resizing, or deleting graphic objects on worksheets and embedded charts
ScenariosPrevents changing the definitions of scenarios

At the workbook level, you can secure items as shown in the following table.

Securing this item Protects it in this way
StructurePrevents deleting, moving, hiding, unhiding, renaming, or adding sheets to the workbook
WindowsPrevents moving, resizing, hiding, unhiding, or closing windows in the workbook

Tip   You can get finer levels of protection using Visual Basic properties such as EnableAutoFilter, EnableOutlining, EnablePivotTable, EnableSelection, ScrollArea, and EnableResize. For more information about these properties, see the Microsoft Office 97/Visual Basic Programmer's Guide, published by Microsoft Press and available wherever computer books are sold. For more information about Microsoft Press books, see Appendix E, "Other Support Resources."

For more information about security in Excel, see "Security Features in Microsoft Excel" in Chapter 30, "Workgroup Features in Microsoft Excel."

Page Settings

Page settings for the layout of the workbook, such as page orientation, margins, and headers and footers, are based on the autotemplate. The page settings in the auto template are initially defined by Excel; however, you can modify these settings in the workbook or save them in a template. Once a workbook is created, its page settings are saved in the workbook file; they are not automatically saved back to the template. You modify page settings with the Page Setup command (File menu).

Templates

A template is a special workbook used as a pattern to create new workbooks. Templates have the file extension .xlt (Windows) or the file type Excel Document (Macintosh).

To maintain consistency among workbooks, you can create templates for a workgroup. For example, you can create a sales report workbook, save it as a template, and distribute it to a workgroup. When users in the workgroup create weekly sales reports based on the template, Excel has a consistent set of menus, macros, and toolbars, and the workbooks have a consistent format.

Settings saved in a template determine the following characteristics of new workbooks based on that template:

  • Cell formats

  • Custom menus, macros, and toolbars

  • Number and type of sheets in a workbook

  • Page formats

  • Row and column styles

  • Text, dates, numbers, formulas, and graphics, such as a company name and logo

When you open a template, Excel opens an untitled, unsaved copy of the template that contains all data, formatting, formulas, macros, styles, scenarios, and so forth that are contained in the template. The original template file remains unchanged.

Tip   When you create a template linked to external data, Excel asks if you want to remove the data before closing and refresh it automatically when the template is opened. Doing this reduces the file size of the template, and can provide additional data security if you require the user to enter a password in order to reconnect to the external data source.

To create a template

  1. Create a workbook that includes the text, formats, and formulas you want to have in the template.

  2. On the File menu, click Save As.

  3. In the File name box (Windows) or Save as box (Macintosh), enter a file name.

  4. In the Save in box, select the folder in which to store the template.

  5. In the Save As type box (Windows) or Save File as Type box (Macintosh), select Template.

Storing workbook templates in the startup or alternate startup folder automatically makes the template available when you click New (File menu). However, this slows the Excel startup time. Alternatively, save templates in the Microsoft Office\Templates folder.

Note   Although templates in the startup or alternate startup folder are automatically available for creating new workbooks, only the template with the reserved name Book.xlt (Windows) or Workbook (Macintosh) stored in the startup or alternate startup folder is an autotemplate. For more information about autotemplates, see "Autotemplates" later in this chapter.

How Are Word Templates and Microsoft Excel Templates Different?

Unlike Word, Excel does not maintain an attachment between workbooks and the templates on which they are based. In both Word and Excel, the template works like a read­only document, but in Word, when you create a document, it has at least one template attached to it, and potentially more. In Word, changing a template (redefining a style, for example) can affect all documents to which the template is attached. This situation is not true in Excel. Once a workbook is created from a template, there is no persistent link between the two, and changing one does not affect the other.

Add­ins

You can save workbooks as add­ins. Add­ins compiled from Excel workbooks have the file extension .xla (Windows) or the file type Excel Document (Macintosh). Once created, add­ins are protected because they cannot be edited.

You can create add­ins to assemble and distribute custom features that, from the user's point of view, act as if they are built into Excel. For information about creating, maintaining, and distributing add­ins, see the Microsoft Office 97/Visual Basic Programmer's Guide, published by Microsoft Press and available wherever computer books are sold. For more information about Microsoft Press books, see Appendix E, "Other Support Resources."

Note   Add­ins can also be written in C code. When compiled, these add­ins have the file extension .xll (Windows) or the file type Microsoft Excel Document (Macintosh).

Several add­ins are included with Excel. Before you can use an add­in, you must install it by rerunning the Office Setup program, and then load it into Excel by clicking the Add­ins command (Tools menu). The add­in's functionality is then available to all open workbooks and remains loaded in Excel until you unload it through the Add­ins command (Tools menu). You can also load an add­in for just the current Excel session by opening the add­in through the Open command (File menu).

If you choose a Typical installation during Setup, some add­ins are installed and loaded automatically, whereas others are installed only, and must be loaded in Excel manually. For information about the components installed for each type of installation, see Appendix D, "List of Installed Components." Except where otherwise indicated, all add­ins are installed in the locations shown in the following table.

Operating system Add­in location
WindowsProgram Files\Microsoft Office\Office\Library
MacintoshMicrosoft Office 98:Office:Excel Add-ins

The following table describes the add­ins included with Excel. Some add­ins require a dynamic-link library (DLL) or compiled C add­in in addition to the add­in file, as indicated in the table.

Add­in Windows file name Macintosh file name Description
Access Links Add­in Acclink.xlaNot supported Allows you to use Microsoft Access forms and reports with Excel data tables.
Analysis ToolPakAnalys32.xll
(Office\Library\
Analysis folder)
Analysis ToolPakAdds financial and engineering functions, and provides tools for performing statistical and engineering analysis.
Analysis ToolPak ­ VBA Atpvbaen32.xlaAnalysis ToolPak ­ VBA Adds Visual Basic functions for the Analysis ToolPak.
AutoSaveAutosave.xla Auto SaveSaves workbook files automatically.
Conditional Sum Wizard Sumif.xlaConditional Sum Wizard Helps you create formulas to sum selected data in lists.
File Conversion Wizard Fileconv.xlsFile Conversion Wizard Converts several spreadsheet files to Excel format in one step.
Internet Assistant Wizard Html.xlaInternet Assistant Wizard Converts Excel tables and charts to HTML files.
Lookup WizardLookup.xla Lookup WizardFinds values at the intersection of a row and column based on known values.
Microsoft QueryXlquery.xla Xlquery.xlaRetrieves data from external database files and tables using Query. (This add­in is used only when saving files in Microsoft Excel 97 & 5.0/95 (Windows) or Microsoft Excel 98 & 5.0/95 (Macintosh) format, or for backward compatibility for Visual Basic.)
ODBCXlodbc.xla
Xlodbc32.dll
Xlquery.xla
(Office\Library\
Msquery folder)
Xlodbc.xla
Xlquery.xla
(Value Pack folder)
Adds worksheet and macro functions for retrieving data from external sources with Microsoft ODBC. (This add­in is included in Excel 97 (Windows) or Excel 98 (Macintosh) only for backwards compatibility; for programmatic data access, use DAO.)
Report ManagerReports.xla Report ManagerPrints reports based on views and scenarios.
Solver Add­in Solver.xla
Solver32.dll
Solvsamp.xls
(Office\Library\
Solver folder)
Solver Calculates solutions to what­if scenarios based on adjustable cells, constraint cells, or cells that must be maximized or minimized.
Template Utilities Template Numbering.xls Template UtilitiesContains utilities used by Excel templates.
Template Wizard with Data Tracking Wztemplt.xlaTemplate Wizard Creates a template to export worksheet data to a database.
Update Add­in Links Updtlink.xlaUpdate Add­in Links Updates links in Excel version 4.0 add­ins to directly use Excel 97 (Windows) or Excel 98 (Macintosh) functionality.
Web Form WizardWebform.xla Web Form WizardHelps you create an HTML form based on an Excel spreadsheet.

Note   To use Query with Excel, you must install the Query application, the Query add­in, the drivers for the types of data you want to retrieve, and the necessary ODBC files. To install these files on a computer running Windows, rerun Setup and click Add/Remove; then select the Data Access option. To use Query on a Macintosh computer, copy the necessary files from the compact disc that came with the Value Pack for Office 98. For information about using Query with Excel, see Chapter 27, "Sharing Information with Microsoft Office Applications."

Top

How You Can Customize Microsoft Excel

Excel default settings and paths are established when you run the Office Setup program. After Setup, you can customize these settings for a single user, a workgroup, or across an entire organization.

There are several ways to customize the Excel application:

  • Through the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu)

    The settings you make in this dialog box to Excel 97 are stored in the Windows registry or to various settings files, which Excel reads when it starts. The settings you make to Excel 98 are stored in System Folder:Preferences.

  • With the startup folder

    When Excel starts, it opens all workbooks stored in the startup folder, or in the alternate startup folder as specified on the General tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

  • With autotemplates

    When Excel starts, it opens a new workbook based on your autotemplate. You can specify the default font, formatting, and other options for new workbooks by creating a custom autotemplate.

  • With workspace files

    If you want to open a group of workbooks in one step, you can create a workspace file. A workspace file contains information about which workbooks to open, their location, and the size and position of their windows on the screen.

  • Through the Customize dialog box

    In the Customize dialog box (Tools menu), you can customize the toolbars, menu bars, and shortcut menus included with Excel, or you can create your own.

  • With an add­in

    An add­in is a hidden, read­only workbook in which Visual Basic, XLM, or C code has been compiled from a source workbook. For more information about add­ins, see "Add-ins" earlier in this chapter.

These methods of customizing Excel are described in the following sections. Some of the customizations you make with Excel 97 are stored in the Windows registry, and some settings are stored in a separate file on the user’s system. The settings you make to Excel 98 are stored in System Folder:Preferences.

User­Defined Options

The settings specified through the Options command (Tools menu) are saved to the Windows registry or to various settings files, which Excel reads when it starts. When two or more users open a shared workbook on a network, Excel applies the separate preferences to each user's view of the shared workbook. For information about Excel entries in the Windows registry, see Appendix C, "Registry Keys and Values."

  Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define most settings in the Options dialog box (Tools menu) for all Excel users in your workgroup. In the System Policy Editor, set the following policy:

User\Excel 97\Tools_Options

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Startup and Alternate Startup Folders

When Excel starts, it opens all workbooks, charts, and workspace files stored in the startup folder shown in the following table.

Operating system Startup folder
WindowsMicrosoft Office\Office\Xlstart
MacintoshSystem Folder:Preferences:Excel Startup Folder (8)

When you click New (File menu), the workbook templates listed are those stored in the startup folder. If you want to open a workbook automatically when you start Excel, move or copy the workbook to the startup folder. This strategy is especially useful if a workgroup is running Excel from a shared network drive, and all users are sharing the same startup folder.

Tip   A problem that arises in a workgroup using a common startup or alternate startup folder is for the first user to start Excel and open the startup workbooks read­write. Subsequent users cannot open the startup documents read­write. Two solutions to this situation are to make the startup workbooks read­only, or to enable workbook sharing so multiple users can edit the startup workbooks simultaneously. For more information about shared workbooks, see "Shared Workbooks" in Chapter 30, "Workgroup Features in Microsoft Excel."

If you want to start Excel with additional workbooks besides those in the startup folder, you can specify an alternate startup folder on the General tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). You might want to create an alternate startup folder if Excel is installed on individual workstations with individual startup folders.

By specifying a single alternate startup folder on a shared network drive, you have a single folder for maintaining any workbooks, charts, or workspace files you want users to open when they start Excel. Or individual users might have personal workbooks and templates they want opened automatically. These could go in the alternate startup folder on a local drive. Excel always opens workbooks stored in both the startup and alternate startup folders — not one or the other.

  Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to specify an Alternate startup file location in the Options dialog box (Tools menu) for all Excel users in your workgroup. In the System Policy Editor, set the following policy:

User\Excel 97\Tools_Options\General\Alternate Startup Folder

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Autotemplates

If you do not specify a startup workbook, Excel opens a new, unsaved workbook when you start Excel. This startup workbook is derived from the autotemplate, a template that opens automatically because it has a reserved file name, as shown in the following table, and is stored in the startup or alternate startup folder.

Operating system Autotemplate reserved file name
WindowsBook.xlt
MacintoshWorkbook

You can specify the default font, formatting, and other options for new workbooks by creating a custom autotemplate. For example, you can create an autotemplate that includes customized headers and footers and your company name, or any text, formatting, formulas, and macros that you want to include automatically in new workbooks.

Note   Before you customize the autotemplate, it is a good idea to make a backup copy of the original file.

To create an autotemplate

  1. Create or open the workbook you want to use as the autotemplate.

  2. On the File menu click Save As.

  3. In the Save as type box (Windows) or Save File as Type box (Macintosh), select Template.

  4. In the File name box (Windows) or Save as box (Macintosh), enter Book.xlt (Windows) or Workbook (Macintosh).

  5. In the Save in box, select your startup or alternate startup folder.

Workspace Files

If you want to be able to open a group of workbooks in one step, you can create a workspace file. A workspace file contains information about which workbooks to open, their location, and the size and position of their windows on the screen. (The workspace file does not contain the workbooks themselves.)

To create a workspace file

  1. Open all the workbooks you want to include in the workspace.

    If you plan to distribute the workspace file to other users, make sure that all workbooks referenced by the workspace file are on a shared network drive.

  2. Size and position the workbooks as you want them to appear.

  3. On the File menu click Save Workspace.

    The default name Excel gives to the workspace file is Resume.xlw (Windows) or Resume (Macintosh). You can rename the file if you want.

If you want the workspace file to open automatically each time users start Excel, copy the file to the users' startup or alternate startup folder.

Important   The workbook names and folder locations are saved in the workspace file. If the files are moved to another location, Excel cannot find them. The best strategy is to save the workspace file and the workbook files it references in the same folder if possible.

Custom Toolbar Settings and Lists

By customizing toolbars and lists, you can change Excel to better suit the needs of your workgroup. For example, you can add frequently used commands and dialog box options to toolbars, and remove items users rarely use. You can also customize lists so that users can sort data in a certain order or quickly fill in a series of cells in a particular way.

You can create and maintain lists for custom fill series or sorting order on the Custom Lists tab of the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). You can customize toolbars in the following ways:

  • By pointing to Toolbars (View menu) and clicking Customize

  • By creating a Visual Basic module

When you modify toolbars or create custom lists through the Excel 97 or Excel 98 user interface, these settings are stored in the following locations:

  • On computers running Windows 3.1, the settings are stored in the Windows\username8.xlb file where username is the user’s logon name.

  • On computers running Windows 95, Windows NT Workstation, or Windows NT Server, the settings are stored in following location in the registry: HKEY_CURRENT_USER\Software\Microsoft\Office\8.0.

  • On Macintosh computers, the settings are stored in System Folder:Preferences Excel Toolbars (8).

Toolbars attached to Visual Basic modules, however, are stored in the workbook file. You can distribute a common set of custom toolbars and custom lists to your workgroup to accomplish specific tasks.

To share custom toolbars within a workgroup

  1. Create the custom toolbars in a workbook.

  2. Save the workbook on a shared network drive.

  3. Instruct users to open the file.

  Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to specify the path to the custom toolbar settings file on a network drive for all Excel users in your workgroup. In the System Policy Editor, set the following policy:

User\Excel 97\Miscellaneous\Personal Toolbars

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

The custom toolbar and lists persist on the user's computer after the user closes or even deletes the workbook. To delete a custom list, you must go to the Custom Lists tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

Tip   You can also use Visual Basic code to customize toolbars and menus with the CommandBar object model. For more information about customizing with Visual Basic, see the Microsoft Office 97/Visual Basic Programmer's Guide, published by Microsoft Press and available wherever computer books are sold.

Top

How Microsoft Excel Resolves Conflicts

Conflicts can arise in Excel when files of the same name reside in the startup and alternate startup folders, or when the user interface has been customized through Visual Basic code.

Startup and Alternate Startup Folders

Excel opens files in the startup folder before opening files in the alternate startup folder. If there is a file in the alternate startup folder with a name that matches a file in the startup folder, the file in the alternate startup folder is ignored.

Custom Toolbars

When you open a workbook that has a toolbar attached, the attached toolbar definitions are saved to your custom toolbar file. These definitions persist on your computer after you close or delete the workbook. If custom macros are attached to the toolbar and you try to run a macro from the toolbar after closing or deleting the original workbook, Excel displays a message stating that it cannot find the macro. The macro can not be found because it resides in the workbook where the toolbar originated.

If you customize the toolbar, your changes do not affect the original toolbar stored in the original workbook. When you reopen the original workbook, Excel uses the copy of the toolbar stored in your custom toolbar file rather than reloading the toolbar stored in the workbook. To generate the original version of the toolbar, delete the customized copy. To do this point to Toolbars (View menu), click Customize, and then select the toolbar and click Delete.

Custom Add­ins

If an add­in's source workbook contains a reference to another add­in, make sure that when you distribute the add­in, the referenced add­in is stored on the user's computer where the calling add­in can find it. The best strategy is to always store a referenced add­in in the folder that contains the calling add­in. When you compile a source workbook as an add­in, Excel stores a path to the referenced add­in in the calling add­in. If you move the calling add­in to another computer (for example, when you distribute the add­in to other users) and Excel cannot find the file in the path, then the following situations result.




  
  • Excel 98 (Macintosh) does not search for the calling add-in file.

  • Excel 97 (Windows) searches for the calling add-in file in the following locations, in this order:

    1. Windows\System folder

    2. Windows folder

    3. The folder that contains the calling add­in

    4. The path defined by the Path environment setting (to see the path, type set at the command prompt)

For information about creating, maintaining, and distributing add­ins, see the Microsoft Office 97/Visual Basic Programmer's Guide, published by Microsoft Press and available wherever computer books are sold.


Top
Contents | << Previous | Next >> | Index