Running Microsoft Excel 2000

Author(s)

Bibliographic Information

Running Microsoft Excel 2000

Mark Dodge, Craig Stinson

Microsoft Press, c1999

  • : pbk

Other Title

Microsoft Excel 2000

Running Microsoft Excel two thousand

Available at  / 2 libraries

Search this Book/Journal

Note

Includes index

Description and Table of Contents

Description

RUNNING MICROSOFT EXCEL 2000 takes you through the most used tasks and functions of Microsoft's popular spreadsheet program. Whether you need quick, how-to instructions or in-depth answers to more advanced questions, this one-stop reference has the answers you need to work more productively-every day-with Excel. Comprehensive: Get new information on topics ranging from creating and managing worksheets and incorporating external data to using pivot tables and working with VBA-plus much more Learn to create Web pages with Excel 2000 Discover how to collaborate with others almost anywhere using Microsoft Excel, workgroups, and the Internet Gain insight into using Microsoft Excel 2000 as a component within Microsoft Office 2000 Easy Access to Information: "Chapters at a Glance" for quick reference Master table of contents Extensive cross-referenced index Two-color interior for easier navigation Everything Is Clear Step-by-step instructions Hundreds of screen illustrations Real-world examples Tips and advice from the experts

Table of Contents

Chapters at a Glance Acknowledgments xxiv Part I Getting Started with Microsoft Excel 2000 1 Chapter 1 Getting Your Bearings 3 Chapter 2 Getting Help 23 Chapter 3 Toolbars and Menus 37 Chapter 4 Managing Files 61 Part II Worksheets 91 Chapter 5 Worksheet Basics 93 Chapter 6 Building Formulas 115 Chapter 7 Formatting a Worksheet 155 Chapter 8 Editing a Worksheet 233 Chapter 9 Worksheets and Windows 299 Chapter 10 Graphics 345 Chapter 11 Printing and Presenting 389 Part III Analyzing Data 409 Chapter 12 Common Worksheet Functions 411 Chapter 13 Dates and Times 459 Chapter 14 Financial Analysis 479 Chapter 15 Statistical Analysis 501 Chapter 16 What-If Analysis 535 Part IV Interfacing with the Rest of the World 571 Chapter 17 Integrating Applications with OLE 573 Chapter 18 Sharing Files with Others 583 Chapter 19 Hyperlinks and the Internet 603 Chapter 20 Creating Web Pages with Excel 629 Part V Charts 649 Chapter 21 Basic Charting Techniques 651 Chapter 22 Changing the Appearance of Your Charts 667 Chapter 23 Working with Chart Data 711 Chapter 24 Mapping Geographic Data 723 Part VI Database and List Management 745 Chapter 25 Managing Information in Lists 747 Chapter 26 Working with External Data 787 Chapter 27 Using PivotTables 835 Part VII Macros and Visual Basic 877 Chapter 28 Creating Macros 879 Chapter 29 Custom Functions 901 Chapter 30 A Sample Visual Basic Application 915 Index 945 Table of Contents Acknowledgments xxiv Part I Getting Started with Microsoft Excel 2000 1 Chapter 1 Getting Your Bearings 3 Starting Microsoft Excel 2000 4 A Tour of the Excel Workspace 5 The Workbook Window 5 The Menu Bar 13 Shortcut Menus 17 The Formula Bar 19 The Status Bar 20 The Pointer 21 Exiting Microsoft Excel 2000 22 Chapter 2 Getting Help 23 Meet the Assistant 24 Firing the Assistant 27 Getting Answers to Unasked Questions 27 Selecting Office Assistant Options 28 Using the Help System 31 Navigating through the Help System 31 Getting Help on the World Wide Web 33 Help for Lotus 1-2-3 Users 33 Getting Context-Sensitive Help 34 Identifying Toolbar Buttons with ScreenTips 35 Chapter 3 Toolbars and Menus 37 Introducing Toolbars 38 "Rafting" Toolbars 38 Adding More Toolbars to Your Screen 40 Positioning Toolbars and Menu Bars on Your Screen 41 Customizing Toolbars and Menus 43 Removing and Adding Buttons and Commands 44 Creating New Toolbars 53 Deleting Custom Toolbars 54 Creating New Menus 54 Saving Toolbar and Menu Changes 55 Changing the Look of Buttons 56 Creating a Custom Button 57 Copying Button Faces 58 Editing and Button Size 58 Attaching a Toolbar to a Workbook 58 Chapter 4 Managing Files 61 Working with Files 62 Creating a New File 62 Creating a New File Based on an Existing File 63 Saving Files 65 Saving the Entire Workspace 76 Opening Files 77 Searching for Files 83 Adding Additional Search Criteria 84 Importing and Exporting Files 86 Using the Open and Save As Commands to Import Files 86 Importing and Exporting Text Files 87 Exporting Files to Earlier Versions of Excel 88 Sharing Data with Microsoft Excel for the Macintosh 88 Part II Worksheets 91 Chapter 5 Worksheet Basics 93 Moving Around the Worksheet 94 Selecting with the Mouse 94 Selecting with the Keyboard 100 Entering Data 103 Simple Numeric and Text Values 103 Entering Numeric Values 103 Entering Text Values 106 Making Entries in Ranges 108 Correcting Errors in Entries 109 Protecting Your Data 111 Unlocking Individual Cells 112 Hiding Cells and Sheets 113 Entering Passwords 114 Removing Protection 114 Chapter 6 Building Formulas 115 Creating Formulas 116 Precedence of Operators 116 Matching Parentheses 117 Using Cell References in Formulas 117 Editing Formulas 123 Using Numeric Text in Formulas 124 Text Values 125 Using Functions: A Preview 126 Formula Bar Formatting 129 Creating Formulas Using Plain Language 129 Naming Cells and Ranges 133 Using Names in Formulas 133 Defining Names with the Name Box 133 Defining Names with the Name Command 134 Rules for Naming Cells and Ranges 138 Editing Names 138 Creating Names from Text Cells 138 Naming Constants and Formulas 139 Three-Dimensional Names 140 Pasting Names into Formulas 141 Applying Names to Formulas 142 Using Range Names with the Go To Command 143 Understanding Error Values 144 Working with Arrays 144 Introducing Arrays 145 Two-Dimensional Arrays 146 Array Formula Rules 147 Using Array Constants 147 Array Expansion 148 Calculating the Worksheet 149 Manual Recalculation 149 Copying Formulas and Pasting Values 151 Circular References 151 Intentional Circular References 153 Precision of Numeric Values 154 Chapter 7 Formatting a Worksheet 155 Assigning and Removing Formats 157 Formatting with Toolbars 158 Formatting Individual Characters 159 Using AutoFormat 159 Formatting Numbers and Text 163 The General Format 164 The Number Formats 165 The Currency Formats 165 The Accounting Formats 166 The Percentage Formats 167 The Fraction Formats 167 The Scientific (Exponential) Formats 168 The Text Format 169 The Special Formats 169 Creating Custom Numeric Formats 170 Aligning Cell Contents 179 The Horizontal Text Alignment Options 179 Centering Text across Columns 180 The Fill Option 180 The Justify and Wrap Text Options 182 The Vertical Text Alignment Options 183 The Orientation Options 185 The Shrink-To-Fit Option 185 Text-Alignment Prefix Characters 186 Formatting Fonts 186 Selecting a Typeface and a Size 188 Selecting Font Styles and Effects 188 Accounting Underlines 189 Selecting a Color 189 Adding Custom Borders 190 Selecting a Line Style 190 Selecting Line Color 191 Selecting Border Placement 191 Applying Two or More Borders at Once 191 Changing or Removing Borders 192 Applying Border Formats with the Borders Button 193 Adding Colors and Patterns 195 Using Shading 196 Applying Conditional Formatting 198 Merging Cells 200 Merging and Unmerging Cells 201 Controlling Column Width 203 Using the Mouse to Change Column Widths 203 Using the Column Commands 205 Controlling Row Height 206 Using the Mouse to Change Row Heights 207 Using the Row Commands 207 How Wrapping and Justifying Text Affect Row Height 208 Formatting with Styles 208 Applying a Style 209 Defining a Style 210 Modifying a Style 212 Overriding a Style with Direct Formatting Changes 213 Merging Styles from Different Workbooks 213 Deleting a Style 214 Using Template Files 214 Modifying a Template File 217 Controlling Display Options 217 Controlling the Display of the Formula Bar and Status Bar 218 Changing the Standard Font 221 Changing the Available Colors 222 Outlining Your Worksheets 225 Creating an Outline 227 Collapsing and Expanding Outline Levels 229 Displaying a Specific Outline Level 231 Selecting Only Visible Cells 231 Ungrouping and Grouping Columns and Rows 232 Removing an Outline 232 Chapter 8 Editing a Worksheet 233 Setting Workspace Options for Editing 234 The Move Selection After Enter Option 234 The Fixed Decimal Option 235 The Provide Feedback With Animation Option 235 Shortcuts for Entering and Editing 236 Moving and Copying with the Mouse 238 Inserting, Deleting, and Clearing Cells with the Mouse 239 Filling and Creating Series with the Mouse 241 Editing Directly in Cells 247 Undoing, Redoing, and Repeating Previous Actions 247 Actions That Don t Affect Undo 248 Redoing What You ve Undone 249 Repeating Your Last Action 249 Using Editing Commands 250 Clearing Cell Contents and Formats 250 Deleting Cells, Columns, and Rows 251 Inserting Columns and Rows 254 Cutting and Pasting 256 Copying and Pasting 261 Using Cut, Copy, Paste, and Clear in the Formula Bar 267 Selective Pasting 268 Filling Cell Ranges with Data 274 Finding and Replacing Data 278 Using Find 278 Using Replace 282 Getting the Words Right 283 Fix Typing Errors on the Fly 283 Let Excel Help with the Typing 285 Check Your Spelling 287 Auditing and Documenting a Worksheet 288 Tracing Cell References 288 Adding Comments to Cells 292 Using the Go To Special Dialog Box 294 Chapter 9 Worksheets and Windows 299 Working with Workbook Windows 300 Managing Multiple Workbooks 300 Opening Multiple Windows for the Same Workbook 306 Hiding and Protecting Workbooks 308 Working with Sheets 311 Sheet Basics 311 Moving and Copying Sheets in a Workbook 314 Moving and Copying Sheets Between Workbooks 316 Splitting Sheets into Panes 317 Zooming Worksheets 322 Custom Views 324 Editing Groups of Sheets Simultaneously 326 Filling a Group 327 Working with Linked Workbooks 329 Saving Linked Workbooks 330 Updating Links Without Opening Supporting Workbooks 333 Redirecting Links 333 Linking Microsoft Excel 2000 to Another Application 334 Copying, Cutting, and Pasting in Linked Workbooks 334 Severing Links Between Workbooks 336 Consolidating Worksheets 337 Consolidating by Position 338 Consolidating by Category 341 Creating Links to the Source Worksheets 343 Chapter 10 Graphics 345 Creating Graphic Objects 346 Drawing Constrained Objects 348 Types of Graphic Objects 349 Drawing Freehand Lines and Polygons 350 Working with Text Boxes 353 Working with AutoShapes 355 Creating WordArt 358 Selecting and Grouping Objects 360 Formatting Objects 362 Positioning Objects 369 Tools to Help You Position Objects on the Worksheet 371 Cutting, Copying, and Sorting Objects with Cells 373 Controlling the Display of Objects 374 Protecting Objects 375 Taking Pictures of Your Worksheets 376 Using the Camera Button 376 Using the Copy Picture Command 377 Using Graphics from Other Programs 379 The Picture Command 379 The Object Command 380 Using Clip Art 382 Importing Clips 385 Organizing Clips 385 Restoring Defaults 387 Chapter 11 Printing and Presenting 389 Specifying What to Print 390 Defining an Area to Print 391 Printing Multiple Copies 391 Controlling the Appearance of Your Pages 392 Printing Wide or Tall 392 Setting a Reduction Ratio 392 Specifying Paper Size and Print Quality 393 The First Page Number Setting 393 Setting Margins 393 Centering Your Work on the Page 393 Creating a Header and Footer 394 Setting Print Titles 397 Printing Gridlines 398 Translating Screen Colors to Black and White 398 Draft Quality 398 Printing Row and Column Headings 398 Printing Comments 399 Setting the Printing Order of Large Print Ranges 399 Setting Printer Driver Options 399 Adjusting Page Breaks with Page Break Preview 400 Removing Manual Page Breaks 401 Using Print Preview 402 Zooming In or Out 403 Adjusting Margins and Column Widths 404 Using the Report Manager 404 Defining the Report 405 Part III Analyzing Data 409 Chapter 12 Common Worksheet Functions 411 Getting More Help with Worksheet Functions 412 The Power of Functions 413 The Syntax of Functions 414 Using Arguments 414 Types of Arguments 415 Entering Functions in a Worksheet 417 Using the Insert Function Command 417 Inserting References 419 Selected Mathematical Functions 419 Logarithmic Functions 427 Trigonometric Functions 428 Engineering Functions 430 Text Functions 430 Logical Functions 439 Conditional Tests 439 Lookup and Reference Functions 443 Chapter 13 Dates and Times 459 How Excel Records Dates and Times 460 Entering Dates and Times 461 Entering a Series of Dates 463 Formatting Dates and Times 466 Combining Date and Time Formats 468 Creating Your Own Date and Time Formats 469 Using Date and Time Arithmetic 471 Date and Time Functions 472 Chapter 14 Financial Analysis 479 Functions for Calculating Investments 480 The PV Function 480 The NPV Function 482 The FV Function 483 The PMT Function 483 The IPMT Function 484 The PPMT Function 484 The NPER Function 485 Functions for Calculating the Rate of Return 486 The RATE Function 486 The IRR Function 487 The MIRR Function 488 Functions for Calculating Depreciation 488 The SLN Function 488 The DDB and DB Functions 489 The VDB Function 490 The SYD Function 491 Functions for Analyzing Securities 491 The DOLLARDE and DOLLARFR Functions 493 The ACCRINT and ACCRINTM Functions 493 The INTRATE and RECEIVED Functions 494 The PRICE, PRICEDISC, and PRICEMAT Functions 495 The DISC Function 495 The YIELD, YIELDDISC, and YIELDMAT Functions 496 The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions 497 The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions 497 The DURATION and MDURATION Functions 499 The ODDFPRICE, ODDFYIELD, ODDLPRICE, and ODDLYIELD Functions 500 Chapter 15 Statistical Analysis 501 Accessing the Analysis ToolPak 502 Analyzing Distributions of Data 503 Built-In Statistical Functions 503 Sample and Population Statistical Functions 507 The Descriptive Statistics Tool 509 The Histogram Tool 512 Functions That Analyze Rank and Percentile 515 The Rank and Percentile Tool 518 Generating Random Numbers 519 The Uniform Distribution Option 520 The Normal Distribution Option 521 The Bernoulli Distribution Option 521 The Binomial Distribution Option 522 The Poisson Distribution Option 522 The Discrete Distribution Option 522 The Patterned Distribution Option 523 Sampling a Population of Numbers 523 Calculating Moving Averages 524 Linear and Exponential Regression 526 Calculating Linear Regression Statistics 527 Calculating Exponential Regression Statistics 532 Chapter 16 What-If Analysis 535 Data Tables 536 Data Tables Based on One Input Variable 536 Data Tables Based on Two Input Variables 539 Editing Tables 541 The Scenario Manager 542 Defining Scenarios 544 Using the Show Button to View Scenarios 546 Adding, Editing, and Deleting Scenarios 547 Merging Scenarios 548 Creating Scenario Reports 552 The Goal Seek Command 556 Precision and Multiple Solutions 558 The Solver 559 Stating the Objective 561 Specifying Variable Cells 562 Specifying Constraints 562 Saving and Reusing the Solver Parameters 565 Other Solver Options 566 Generating Reports 568 When the Solver Is Unable to Solve 570 Part IV Interfacing with the Rest of the World 571 Chapter 17 Integrating Applications with OLE 573 Linking vs. Embedding 574 Why and How to Embed 575 Why and How to Link 578 Automatic vs. Manual Links 579 Opening a File that Contains Links 580 Suspending Links 580 How Links Are Identified in Excel 580 Linking Text vs. Linking Objects or Pictures 581 Activating a Link 581 Editing Links 582 Chapter 18 Sharing Files with Others 583 Sharing Files Using a Network 584 Saving and Retrieving Files on Remote Computers 584 Retrieving Busy Files Automatically 584 Sharing Workbooks on a Network 585 Advanced Sharing Options 588 Tracking Changes 590 Reviewing Changes 592 Canceling the Shared Workbook Session 593 Combining Changes Made to Multiple Workbooks 594 Merging Workbooks 594 Mailing Workbooks Using Electronic Mail 597 Routing Workbooks to a Workgroup Using Electronic Mail 599 Chapter 19 Hyperlinks and the Internet 603 Basic Internet Concepts 604 Using Hyperlinks 607 Internet Addressing 607 Creating a Hyperlink to a Web Page 608 Editing a Hyperlink 610 The Web Toolbar 611 Cruising with a Web Hyperlink 611 Copying a Link 613 More about Creating Hyperlinks 614 Making a Button or Drawing Object a Hyperlink 616 Using the HYPERLINK Worksheet Function 617 Working with FTP Sites in Excel 619 Preparing for FTP Access 620 Opening a Workbook at an FTP Site 621 Downloading a Workbook to Your Computer 622 Using Web Folders 627 Chapter 20 Creating Web Pages with Excel 629 Converting an Entire Excel Workbook into HTML 630 Converting Individual Excel Worksheets into HTML 634 Other Web Publishing Options 635 Changing the Title 635 Choosing Other Items to Publish 636 Adding Data to an Existing Web Page 637 Adding Interactivity 637 Spreadsheet Functionality 638 Chart Functionality 641 PivotTable Functionality 644 Part V Charts 649 Chapter 21 Basic Charting Techniques 651 Creating a New Chart 652 Step 1: Choosing a Chart Type 652 Step 2: Specifying the Data to Plot 653 Step 3: Choosing Chart Options 656 Step 4: Deciding Where to Put the Chart 661 Changing a Chart s Size and Position 661 Plotting Hidden Cells 662 How Excel Handles Missing Values 663 Changing the Default Chart Type 664 Printing Charts 665 Saving, Opening, and Protecting Charts 665 Chapter 22 Changing the Appearance of Your Charts 667 Working with the Chart Menu and Chart Toolbar 668 Selecting Chart Elements 669 Copying Formats from One Chart to Another 670 Adding a Customized Chart to the Chart Wizard Gallery 671 Repositioning Chart Elements with the Mouse 671 Moving and Resizing the Plot Area 672 Formatting Text Elements 672 Changing the Font and Color 673 Changing Text Rotation 675 Changing the Alignment of Multiline Titles 676 Changing the Numeric Format Used by Tick-Mark and Data Labels 676 Formatting the Borders and Backgrounds of Text Areas 677 Formatting and Scaling Axes 677 Specifying Line Style, Color, and Weight 677 Specifying the Position of Tick Marks and Tick-Mark Labels 678 Scaling Axes Manually 679 Displaying and Formatting Gridlines 688 Formatting Data Series and Markers 689 Assigning a Series to a Secondary Value Axis 689 Using Two or More Chart Types in the Same Chart 690 Changing the Series Order 691 Toggling the Column/Row Orientation 691 Changing Colors, Patterns, Fills, and Borders for Markers 692 Adjusting Marker Spacing in Two-Dimensional Column and Bar Charts 693 Adjusting Marker Spacing in Three-Dimensional Charts 694 Adding Series Lines in Stacked Column and Bar Charts 695 Changing Marker Shapes in Three-Dimensional Column and Bar Charts 695 Smoothing the Lines in Line and XY (Scatter) Charts 696 Changing Line and Marker Styles in Line, XY (Scatter), and Radar Charts 697 Adding High-Low Lines and Up-Down Bars in Line Charts 697 Adding Drop Lines in Area and Line Charts 698 Exploding Pie Slices and Doughnut Bites 698 Split and Formatting Options in Pie-Column and Pie-Pie Charts 699 Changing the Angle of the First Pie Slice or Doughnut Bite 700 Display Options for Bubble Charts 700 Formatting Background Areas 701 Formatting the Border 702 Filling an Area with a Solid Color 702 Filling an Area with a Color Gradient 702 Filling an Area with a Pattern 703 Filling an Area with a Texture or Picture 704 Changing Three-Dimensional Viewing Angles 706 Adjusting the Elevation 708 Changing the Rotation 708 Changing the Height 709 Changing the Perspective 709 Changing the Axis Angle and Scale 709 Chapter 23 Working with Chart Data 711 Adding Data 712 Removing Data 715 Replacing Data 716 Changing the Plot Order 717 Using Multilevel Categories 718 Adding Trendlines 719 Adding Error Bars 720 Dragging Chart Markers to Change Data 721 Chapter 24 Mapping Geographic Data 723 Preparing Your Data 724 Creating a Data Map 725 Single-Series Map Formats 728 Changing the Look of a Map 730 Adding New Layers to a Map 733 Adding Map Features 735 Refreshing the Map 738 Saving the Map Template 738 Multiple-Series Map Formats 739 Inserting Data into a Map 742 Creating Custom Pin Maps 743 Part VI Database and List Management 745 Chapter 25 Managing Information in Lists 747 Building and Maintaining a List 748 Using a Form to Add, Change, or Delete Rows 749 Sorting Lists and Other Ranges 752 A Simple Example 752 Sorting on More Than One Column 754 Sorting Only Part of a List 756 The Header Row Option 756 Sorting by Columns 757 Creating and Using Custom Sort Orders 759 Using Filters to Analyze a List 760 The AutoFilter Command 760 The Advanced Filter Command 766 Using Subtotals to Analyze a List 777 How Subtotal Formulas Are Built 780 Using Automatic Page Breaks 781 Applying More Than One Aggregation Formula to a Column 781 Removing or Replacing Subtotals 782 Using Functions to Analyze a List 782 Using the COUNTIF and SUMIF Functions 782 Using the D Functions 783 Validating Data Entry 785 Specifying Data Type and Acceptable Values 785 Specifying an Input Message 786 Specifying Error Alert Style and Message 786 Chapter 26 Working with External Data 787 Getting Data from Text Files 788 The Import Text File Wizard 789 Working with the External Data Toolbar 793 Revisiting the Import Text File Wizard 793 Refreshing a Text Query 794 Refreshing All Queries at Once 794 Working with the Text Query s Property Sheet 795 Using Web Queries to Get Information from the Internet 797 Executing an Existing Web Query 798 Refreshing a Web Query 800 Working with the Web Query s Property Sheet 800 Creating Your Own Web Queries 801 Using Microsoft Query to Create Database Queries 804 When to Use Query, When to Use the Query Wizard 805 Specifying a Data Source 806 Using the Query Wizard 809 Working Directly with Microsoft Query 813 Chapter 27 Using PivotTables 835 A Simple Example 836 Creating a PivotTable 839 Starting the PivotTable and PivotChart Wizard 839 Step 1: Specifying the Type of Data Source 840 Step 2: Indicating the Location of Your Source Data 840 Step 3: Telling the Wizard Where to Put Your PivotTable 842 Laying Out the PivotTable 843 Changing the Layout of a PivotTable 844 Using the Page Axis 844 Selecting Items to Display on the Row and Column Axes 846 Creating a PivotChart 846 Refreshing a PivotTable 848 Refreshing on File Open 848 Refreshing Linked PivotTables 848 Refreshing a PivotTable Based on External Data 849 Selecting Elements of a PivotTable 849 Turning Structured Selection On or Off 850 Selecting Labels Only or Data Only 851 Selecting an Entire PivotTable 851 Formatting a PivotTable 851 Using AutoFormat with PivotTables 852 Changing the Number Format for All Cells in a PivotTable 852 Changing the Way a PivotTable Displays Empty Cells 853 Changing the Way a PivotTable Displays Error Values 853 Merging Labels 854 Using Multiple Data Fields 854 Renaming Fields and Items 856 Sorting Items 856 Using AutoSort 857 Using Nonstandard Sorting Orders 857 Showing the Top or Bottom Items in a Field 858 Hiding and Showing Inner Field Items 859 Displaying the Details Behind a Data Value 859 Grouping and Ungrouping Data 860 Creating Ad Hoc Item Groupings 860 Grouping Numeric Items 862 Grouping Items in Date or Time Ranges 862 Removing Groups (Ungrouping) 864 Using Grand Totals and Subtotals 864 Grand Totals 864 Subtotals 864 Subtotals for Innermost Fields 865 Changing a PivotTable s Calculations 866 Using a Different Summary Function 866 Applying Multiple Summary Functions to the Same Field 867 Using Custom Calculations 867 Using Calculated Fields and Items 868 Referencing PivotTable Data from Worksheet Cells 871 Using a PivotTable to Consolidate Ranges 871 Part VII Macros and Visual Basic 877 Chapter 28 Creating Macros 879 Recording Macros 880 Using the Macro Dialog Box 883 Behind the Scenes: The Visual Basic Environment 883 Getting Help on Visual Basic Keywords 884 Objects, Methods, and Properties 885 Collections of Objects 888 Naming Arguments to Methods 890 Adding Code to an Existing Macro 891 Using Absolute and Relative References 894 Macro Subroutines 896 Other Ways to Run Macros 898 Using the Personal Macro Workbook 899 Chapter 29 Custom Functions 901 Creating a Custom Function 902 Using Custom Functions 905 What s Happening 906 Custom Function Rules 908 Visual Basic Keywords Available to Custom Functions 909 Documenting Macros and Custom Functions 909 Designing Flexible Custom Functions 911 Chapter 30 A Sample Visual Basic Application 915 A Visual Basic Application 916 Loading a Workbook Automatically 917 Declaring Public Variables 918 The Main Sequence 919 Running a Macro Automatically 920 Waiting for an Event 921 Using a Custom Dialog Box to Load and Save Options 923 Creating an Options Sheet 924 Creating a UserForm 925 Displaying the UserForm 927 A Minor Problem 928 UserForm Code 929 Downloading Data via the Internet 931 Working with Text Files 932 Formatting a Worksheet 934 Creating a Chart 935 Controlling Word from an Excel Macro 938 The GenWordReport Routine 938 Converting to an Add-In Application 942 Index 945 Acknowledgments When I took over this book in 1990, I was sure that soft- ware would soon improve so that books like this would have become obsolete by now. The good news for you is that software does lots more cool stuff than ever. Which is also the bad news it takes many lines of code to do all that cool stuff. Amazingly, brick-like books are still very much in demand to help sort it all out. While some might explain software "bloat" using chaos theory, we like to think of this phenomenon as "job security." Many thanks to Kim Fryer, Sandra Haynes, Bill Teel, Kristen Weatherby, and all my Microsoft Press pals for doing that thing they continue to do so well. Equally important were the contributions of the ever-diligent Peter Whitmer and nSight, Inc. Special thanks to technical editor Ron Miller for finding all the right stuff. Thanks also to Microsoftees Eric Patterson and Gerry Lenocker for their kindly assistance in matters of miscellany. This edition s musical thanks go to the Amazing Rhythm Aces (http://www.theaces.com), creators of the world s deepest grooves, for coming back strong Out of the Blue. Mark Dodge I d like to echo Mark s thanks to all the aforementioned Softees and nSighters. Your expertise is deeply appreciated. Thanks, too, to Jean, Russell, and Miranda for patience and support. And my deepest gratitude to Wagner, Sibelius, Elgar, and the gang. C.S.

by "Nielsen BookData"

Details

Page Top