the advantage series ©2005 the mcgraw-hill companies, inc. all rights reserved chapter 11...
Post on 01-Jan-2016
216 Views
Preview:
TRANSCRIPT
©2005 The McGraw-Hill Companies, Inc. All rights reserved
The Advantage Series
Chapter 11
Developing Applications Using Excel
Microsoft Office Excel 2003
©2005 The McGraw-Hill Companies, Inc. All rights reserved2
11.1 Planning a Workbook Application
• To be successful as a spreadsheet developer, place yourself in the users’ position and strive to meet their expectations. Before you enter even the first label on a worksheet, understand your target audience and identify their basic input and output requirements. The users for your workbook application are the people who will enter and manipulate the data, analyze calculated results, and make decisions based on its reports and charts. They are also, most likely, the people paying your salary!
©2005 The McGraw-Hill Companies, Inc. All rights reserved3
11.1.1 Creating Shared WorkbookFigure 11.1Opening the EX1110 workbook
The OrderForm worksheet draws data from the PriceList and Suppliers worksheet lists
These worksheet lists are used for populating the OrderForm worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved4
11.1.1 Creating Shared WorkbookFigure 11.2Share Workbook dialog box: Advanced tab
Select the interval frequency for displaying changes made by other users
Select whether to store a change history for the workbook (recommended)
Specify the method for dealing with change conflicts between users
Specify the settings to save in the custom view for each user
©2005 The McGraw-Hill Companies, Inc. All rights reserved5
11.1.2 Tracking Changes to a Workbook
Figure 11.3Highlight Changes dialog box
©2005 The McGraw-Hill Companies, Inc. All rights reserved6
11.1.2 Tracking Changes to a Workbook
Figure 11.4Tracking changes in a shared workbook
A ScreenTip, colored row number and column letter, triangular cell indicator, and a cell outline highlight the changed cell
©2005 The McGraw-Hill Companies, Inc. All rights reserved7
11.1.2 Tracking Changes to a Workbook
Figure 11.5History worksheet showing tracked changes
Excel 2003 inserts and populates a new History worksheet with the changes that have been tracked in the shared workbook
Use the AutoFilter drop-down arrows to limit the display of information in the History worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved8
11.1.2 Tracking Changes to a Workbook
Figure 11.6Select Changes to Accept or Reject dialog box
©2005 The McGraw-Hill Companies, Inc. All rights reserved9
11.1.2 Tracking Changes to a Workbook
Figure 11.7Accept or Reject Changes dialog box
©2005 The McGraw-Hill Companies, Inc. All rights reserved10
11.1.3 Merging Workbook RevisionsFigure 11.8Selecting multiple workbook files to compare and merge
©2005 The McGraw-Hill Companies, Inc. All rights reserved11
11.1.3 Merging Workbook RevisionsFigure 11.9Reviewing tracked changes after merging workbooks
©2005 The McGraw-Hill Companies, Inc. All rights reserved12
11.1.3 Merging Workbook RevisionsFigure 11.10Displaying the History tracking worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved13
11.2 Validating data
• Validating data that is entered into a worksheet ensures the reliability and accuracy of a workbook application. In addition to displaying drop-down lists of values, Excel 2003’s data validation feature provides helpful prompts and can even warn users when incorrect entries are made. As a workbook developer, it is your job to determine what data is valid by setting a numeric or date range or by comparing an entry to a list of acceptable values. This module presents several techniques for ensuring the completeness and accuracy of data.
©2005 The McGraw-Hill Companies, Inc. All rights reserved14
11.2.1 Applying Conditional Formatting
Figure 11.11Opening the EX1120 workbook
©2005 The McGraw-Hill Companies, Inc. All rights reserved15
11.2.1 Applying Conditional Formatting
Figure 11.12Conditional Formatting dialog box
Specify a condition by first selecting “Cell Value Is” or “Formula Is”
Preview the formatting options selected
Select the formatting to apply if the current condition evaluates to true
Specify up to three conditions
©2005 The McGraw-Hill Companies, Inc. All rights reserved16
11.2.1 Applying Conditional Formatting
Figure 11.13Specifying a condition based on a cell value
©2005 The McGraw-Hill Companies, Inc. All rights reserved17
11.2.1 Applying Conditional Formatting
Figure 11.14Specifying a condition based on a formula
©2005 The McGraw-Hill Companies, Inc. All rights reserved18
11.2.1 Applying Conditional Formatting
Figure 11.15Applying conditional formatting a worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved19
11.2.2 Applying and Removing Validation Rules
Figure 11.16Data Validation dialog box: Settings tab
©2005 The McGraw-Hill Companies, Inc. All rights reserved20
11.2.2 Applying and Removing Validation Rules
Figure 11.17Data Validation dialog box: Input Message tab
©2005 The McGraw-Hill Companies, Inc. All rights reserved21
11.2.2 Applying and Removing Validation Rules
Figure 11.18Data Validation dialog box: Error Alert tab
©2005 The McGraw-Hill Companies, Inc. All rights reserved22
11.2.2 Applying and Removing Validation Rules
Figure 11.19Displaying a custom warning message
Displays the text entered on the Error Alert tab of the Data Validation dialog box
Displays the text entered on the Input Message tab of the Data Validation dialog box
©2005 The McGraw-Hill Companies, Inc. All rights reserved23
11.2.3 Finding and Copying Validation Rules
Figure 11.20Go To Special dialog box
Select the Data validation option button and then All or Same to locate validation rules in your worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved24
11.2.3 Finding and Copying Validation Rules
Figure 11.21Paste Special dialog box
©2005 The McGraw-Hill Companies, Inc. All rights reserved25
11.2.4 Using Dynamic Ranges For Data Validation
Figure 11.22Creating a dynamic range reference
©2005 The McGraw-Hill Companies, Inc. All rights reserved26
11.2.4 Using Dynamic Ranges For Data Validation
Figure 11.23Using a dynamic range in a validation rule
©2005 The McGraw-Hill Companies, Inc. All rights reserved27
11.3 Auditing a Worksheet
• To audit a worksheet means to review it for incorrect or suspect entries and formulas. Although conditional formatting and data validation provide some measure of error checking, mistakes can still occur when you construct and use a worksheet. For these instances, Excel 2003 offers several auditing features to assist you in tracking down rogue cells. Microsoft Office Excel 2003 also provides cell indicators and option buttons to assist you in performing error checking. In this module, you learn to use various auditing tools to find errors and graphically display cell dependencies.
©2005 The McGraw-Hill Companies, Inc. All rights reserved28
11.3.1 Identifying Cell Indicators, Option Buttons, and Smart Tags
Figure 11.24Opening the EX1131 workbook
©2005 The McGraw-Hill Companies, Inc. All rights reserved29
11.3.1 Identifying Cell Indicators, Option Buttons, and Smart Tags
Figure 11.25Using the Insert Options button
When you insert a new row, the formatting from the topmost row is applied to the new row. The Insert Options button appears to speed the process of changing this formatting assumption.
©2005 The McGraw-Hill Companies, Inc. All rights reserved30
11.3.1 Identifying Cell Indicators, Option Buttons, and Smart Tags
Figure 11.26AutoCorrect dialog box: Smart tags tab
Select this check box to enable smart tags
This list area displays the available libraries of keyword “recognizers.” You can activate the built-in lists independently for identifying cells containing dates, financial symbols, Outlook e-mail recipients, and other itemsSelect this check box to
store the identified smart tags in the workbook
Check the current workbook for cell data recognized as smart tag labels
Access the Microsoft Web site to download additional libraries of keyword recognizers
©2005 The McGraw-Hill Companies, Inc. All rights reserved31
11.3.1 Identifying Cell Indicators, Option Buttons, and Smart Tags
Figure 11.27Displaying the Smart Tag Actions menu
“MSFT” is recognized as a stock symbol and labeled with a smart tag cell indicator
Click the Smart Tag Actions button to display a context-sensitive menu
©2005 The McGraw-Hill Companies, Inc. All rights reserved32
11.3.1 Identifying Cell Indicators, Option Buttons, and Smart Tags
Figure 11.28Retrieving stock information from the Web using a smart tag
Each blue underlined term is a hyperlink that you click to launch your default browser and display a Web page
The stock information is placed on a new worksheet. The original data remains unchanged on the Sheet1 worksheet tab
©2005 The McGraw-Hill Companies, Inc. All rights reserved33
11.3.2 Locating Invalid Data Using The Formula Auditing ToolBar
Figure 11.29Opening the EX1130 workbook
©2005 The McGraw-Hill Companies, Inc. All rights reserved34
11.3.2 Locating Invalid Data Using The Formula Auditing ToolBar
Figure 11.30Formula Auditing toolbar
©2005 The McGraw-Hill Companies, Inc. All rights reserved35
11.3.2 Locating Invalid Data Using The Formula Auditing ToolBar
Figure 11.31Auditing a worksheet for non-complying values
Because this value does not comply with the data validation rule specified, it appears highlighted using a red auditing circle
Formula Auditing toolbar
©2005 The McGraw-Hill Companies, Inc. All rights reserved36
11.3.3 Tracing Precedents and Dependents
Figure 11.32Tracing precedents for a formula
Each circle endpoint shows a cell that provides precedent information for the selected formula
©2005 The McGraw-Hill Companies, Inc. All rights reserved37
11.3.3 Tracing Precedents and Dependents
Figure 11.33Tracing immediate and associated precedents
©2005 The McGraw-Hill Companies, Inc. All rights reserved38
11.3.3 Tracing Precedents and Dependents
Figure 11.34Tracing dependents in a worksheet
Each arrowhead endpoint shows a cell that is dependent on the selected cell for information
©2005 The McGraw-Hill Companies, Inc. All rights reserved39
11.3.4 Locating Errors In Formulas
Figure 11.35Error Checking dialog box
A “Divide by Zero Error” has been found in cell F14. Notice that the formula is also displayed above the error description
©2005 The McGraw-Hill Companies, Inc. All rights reserved40
11.3.4 Locating Errors In Formulas
Figure 11.36Evaluate Formula dialog box
The next expression to be evaluated is shown in the Evaluation area
The formula error checker already knows that an error will result in the next calculation
©2005 The McGraw-Hill Companies, Inc. All rights reserved41
11.3.4 Locating Errors In Formulas
Figure 11.37Displaying the Watch Window
Drag the borderlines to size the columns
©2005 The McGraw-Hill Companies, Inc. All rights reserved42
11.3.4 Locating Errors In Formulas
Figure 11.38Tracing calculation errors using the Watch Window
©2005 The McGraw-Hill Companies, Inc. All rights reserved43
11.3.5 Preventing Calculation Errors
Figure 11.39Nesting an ISERROR function within an IF function
Drag the borderlines to size the columns.
©2005 The McGraw-Hill Companies, Inc. All rights reserved44
11.4 Automating A Workbook
• Excel 2003 has assigned most monotonous tasks to toolbar buttons and “Auto” commands. However, there are still tasks that you will have to perform over and over again that are not included as buttons on a toolbar.
• Excel 2003 allows you to store and play back keystrokes and commands. In addition to saving you an enormous amount of time, these stored instructions, called macros, also improve the consistency and accuracy of repetitive procedures. Using a macro, you can execute a sequence of instructions by simply clicking a button, pressing a key combination, or selecting a name from a list box.
©2005 The McGraw-Hill Companies, Inc. All rights reserved45
11.4.1 Recording a Macro
Figure 11.40Record macro dialog box
Provide a shortcut key for executing the macro quickly
Provide a descriptive one-word name for the macro
Select a storage location for the macro. Where you store a macro determines its availability to other workbooks
Enter a description specifying the purpose of the macro. Excel 2003 enters a description like this one automatically
©2005 The McGraw-Hill Companies, Inc. All rights reserved46
11.4.1 Recording a Macro
Figure 11.41Completing the Record Macro dialog box
Select a storage location for the macro. Where you store a macro determines its availability to other workbooks.
©2005 The McGraw-Hill Companies, Inc. All rights reserved47
11.4.1 Recording a Macro
Figure 11.42Recording cell formatting commands
Stop Recording toolbar
“Recording” status
©2005 The McGraw-Hill Companies, Inc. All rights reserved48
11.4.2 Playing Back a Macro
Figure 11.43Macro dialog box
All of the macros stored in the Macros in selection appear in this list box
Select which macros to view in the list box
A description of the currently selected macro is displayed
©2005 The McGraw-Hill Companies, Inc. All rights reserved49
11.4.2 Playing Back a Macro
Figure 11.44Executing a macro to enter a business name and address
All of the macros stored in the Macros in selection appear in this list box.
©2005 The McGraw-Hill Companies, Inc. All rights reserved50
11.4.3 Protecting Yourself From Macro Viruses
Figure 11.45Security dialog box: Security Level tab
Most users will specify the “Medium” security setting if they also have antivirus software installed on their system. If you do not have a virus scanner, consider selecting the High or Very High security
For more information about security settings, trusted publishers, and digital signing, click the Help button in the Title bar
This computer has a virus scanner installed
©2005 The McGraw-Hill Companies, Inc. All rights reserved51
11.4.3 Protecting Yourself From Macro Viruses
Figure 11.46Opening a workbook containing macros
©2005 The McGraw-Hill Companies, Inc. All rights reserved52
11.4.3 Protecting Yourself From Macro Viruses
Figure 11.47Opening a workbook and running macros
©2005 The McGraw-Hill Companies, Inc. All rights reserved53
11.4.4 Editing a Recorded Macro
Figure 11.48Displaying macro code in the Visual Basic Editor
Code window
Macro name
The Code window contains the VBA programming code
Comment
©2005 The McGraw-Hill Companies, Inc. All rights reserved54
11.4.4 Editing a Recorded Macro
Figure 11.49Editing VBA programming code
©2005 The McGraw-Hill Companies, Inc. All rights reserved55
11.4.5 Printing Your Macros
Figure 11.50Print dialog box in the Visual Basic Editor
Specify whether to print the selected code, the entire module, or all the modules in the current project
When printing a custom form, you can specify whether to print its image, in addition to its code
©2005 The McGraw-Hill Companies, Inc. All rights reserved56
11.5 Customizing Menus and ToolBars
• When Microsoft Office Excel 2003 is first installed on your system, the Menu bar, shortcut menus, and toolbars are set up in a default configuration. As you gain experience with Excel 2003 and create macros for automating everyday procedures, you may want to customize the application window to your preferred way of working. Fortunately, Excel 2003’s environment is extremely flexible and easily changed. You can change the number of worksheets that are displayed in a new workbook. You can even modify the existing interface elements, such as the Menu bar, and create entirely new menu options and toolbars. Whatever your requirements, your overall objective should be to make it easier to access the commands and procedures you use most often.
©2005 The McGraw-Hill Companies, Inc. All rights reserved57
11.5.1 Modifying the Menu Bar
Figure 11.51Customize dialog box: Commands tab
The Categories list box provides options similar to the topmost items on the Menu bar
The Commands list box displays the commands available within the selected category
©2005 The McGraw-Hill Companies, Inc. All rights reserved58
11.5.1 Modifying the Menu Bar
Figure 11.52Adding a new item to the menu
Dragging the “Custom Menu Item” to the bottom of the Insert menu
©2005 The McGraw-Hill Companies, Inc. All rights reserved59
11.5.1 Modifying the Menu Bar
Figure 11.53Assigning a macro to the new “Insert Business Name” menuitem
©2005 The McGraw-Hill Companies, Inc. All rights reserved60
11.5.2 Manipulating Toolbars
Figure 11.54Floating the Standard toolbar
The move handle for the Formatting toolbar
The Standard toolbar appears as a floating window with a Title bar and Close button
©2005 The McGraw-Hill Companies, Inc. All rights reserved61
11.5.2 Manipulating Toolbars
Figure 11.55Displaying and hiding toolbars
©2005 The McGraw-Hill Companies, Inc. All rights reserved62
11.5.3 Customizing a Toolbar
Figure 11.56Removing a button from the Formatting toolbar
Dragging the Bold button from the Formatting toolbar into the worksheet
©2005 The McGraw-Hill Companies, Inc. All rights reserved63
11.5.3 Customizing a Toolbar
Figure 11.57Adding a button to the Standard toolbar
Dragging the “Publish as Web Page” option to the left of the Print button
©2005 The McGraw-Hill Companies, Inc. All rights reserved64
11.5.4 Creating a New Toolbar
Figure 11.58Changing a toolbar button’s image
top related