Excel 2000 Programming For Dummies

by Walkenbach, John; Wyatt, Allen L.

• ISBN13: 9780764505669
• ISBN10: 0764505661
• Edition: 1st
• Format: Paperback
• Copyright: 1999-08-05
• Publisher: For Dummies

Summary

If you're ready to take the next step with Excel, then look no further. By using VBA (Visual Basic Application), you can discover a side of Microsoft Excel that most users never uncover. Excel 2000 Programming For Dummies introduces you to a wide array of new Excel options, including options for creating new worksheet functions; automating tasks and operations; creating new appearances, toolbars, and menus; and doing much more.First, you get well-acquainted with the most important tools and operations for the Visual Basic Editor; then, you get a quick overview of the essential elements and concepts for programming with Excel. Discover techniques for handling errors and exterminating bugs, the basics of working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes (also known as UserForms) and create custom toolbars and menus, you'll soon be creating the interfaces that best suit your unique needs.By the time you rip through Excel 2000 Programming For Dummies, you'll not only have maximized your macros, you'll have moved on to creating Excel applications with the best programmers on the block.

Author Biography

<b>John Walkenbach</b> is one of the country's leading authorities on spreadsheet software. He's principal of Jwalk and Associates Inc., a Southern California-based consulting firm that specializes in spreadsheet application development. John is also a shareware developer, and his most popular product is the Power Utility Pak add-in for Excel. John has written more than 250 articles and reviews for publications such as <i>PC World, InfoWorld, Windows</i>, and <I>PC/Computing</i>. In addition, he's authored more than 20 other spreadsheet books, including <i>Excel 97 Bible, Excel 97 For Windows For Dummies Quick Reference</i>, and <i>Excel 97 Programming For Windows For Dummies</i>. jwalk.com). When he's not banging away on his computer keyboard, he's probably banging away on one of his guitars, trying to cop a few blues licks from Freddie King.

Table of Contents

Introduction	p. 1
What This Book Covers	p. 1
So You Want to Be a Programmer ...	p. 2
Why Bother?	p. 3
What I Assume about You	p. 3
Obligatory Typographical Conventions Section	p. 4
How This Book Is Organized	p. 5
Marginal Icons	p. 6
Wanna Reach Out?	p. 7
Now What?	p. 7
Introducing VBA	p. 9
What Is VBA?	p. 11
Okay, So What Is VBA?	p. 11
What Can You Do with VBA?	p. 12
Advantages and Disadvantages of VBA	p. 15
VBA in a Nutshell	p. 16
An Excursion into Versions	p. 18
Jumping Right In	p. 21
What You'll Be Doing	p. 21
First Steps	p. 22
Recording the Macro	p. 22
Testing the Macro	p. 24
Examining the Macro	p. 25
Modifying the Macro	p. 27
More about This Macro	p. 29
How VBA Works with Excel	p. 31
Introducing the Visual Basic Editor	p. 33
What Is the VBE?	p. 33
Working with the Project Explorer	p. 36
Working with a Code Window	p. 37
Customizing the VBA Environment	p. 44
Introducing the Excel Object Model	p. 51
Excel Is an Object?	p. 52
The Object Hierarchy	p. 52
Understanding Collections	p. 55
Referring to Objects	p. 56
Object Properties and Methods	p. 57
Finding Out More	p. 61
VBA Subroutines and Functions	p. 65
Subroutines versus Functions	p. 65
Executing Subroutines	p. 68
Executing Functions	p. 73
Using the Excel Macro Recorder	p. 77
Is It Live, or Is It VBA?	p. 77
Recording Basics	p. 78
Types of Macros Appropriate for Recording	p. 79
Preparing to Record	p. 80
Relative or Absolute?	p. 80
What Gets Recorded?	p. 83
Recording Options	p. 84
Is This Thing Efficient?	p. 86
Programming Concepts	p. 89
Essential VBA Language Elements	p. 91
Using Comments in Your Code	p. 91
Using Variables, Constants, and Data Types	p. 93
Using Assignment Statements	p. 104
Working with Arrays	p. 106
Using Labels	p. 108
Working with Range Objects	p. 111
A Quick Review	p. 111
Other Ways to Refer to a Range	p. 112
Some Useful Range Object Properties	p. 114
Some Useful Range Object Methods	p. 119
Using VBA and Worksheet Functions	p. 123
What Is a Function?	p. 123
Using VBA Functions	p. 124
Using Worksheet Functions in VBA	p. 129
More about Using Worksheet Functions	p. 133
Using Custom Functions	p. 134
Controlling Program Flow and Making Decisions	p. 135
Go with the Flow, Dude	p. 135
The GoTo Statement	p. 136
Decisions, Decisions	p. 137
Knocking Your Code for a Loop	p. 144
Looping through a Collection	p. 150
Automatic Procedures and Events	p. 153
Preparing for the Big Event	p. 153
Where Does the VBA Code Go?	p. 156
Writing an Event-Handler Subroutine	p. 157
Introductory Examples	p. 159
Examples of Activation Events	p. 162
Other Worksheet-Related Events	p. 165
Events Not Associated with Objects	p. 168
Error-Handling Techniques	p. 171
Types of Errors	p. 171
An Erroneous Example	p. 172
Another Way to Handle Errors	p. 176
Handling Errors The Details	p. 178
An Intentional Error	p. 183
Bug Extermination Techniques	p. 185
Species of Bugs	p. 185
Identifying Bugs	p. 186
Debugging Techniques	p. 187
About the Debugger	p. 189
Bug Reduction Tips	p. 194
VBA Programming Examples	p. 197
Working with Ranges	p. 197
Changing Excel Settings	p. 205
Changing Non-Boolean Settings	p. 206
Working with Charts	p. 206
VBA Speed Tips	p. 209
Developing Custom Dialog Boxes	p. 213
Custom Dialog Box Alternatives	p. 215
Why Create Dialog Boxes?	p. 215
Do You Really Need a Custom Dialog Box?	p. 216
The MsgBox Function	p. 216
The InputBox Function	p. 221
The GetOpenFilename Method	p. 223
The GetSaveAsFilename Method	p. 227
Displaying Excel's Built-In Dialog Boxes	p. 227
Custom Dialog Box Basics	p. 231
When a Custom Dialog Box Is Useful	p. 231
Creating Custom Dialog Boxes: An Overview	p. 232
Working with UserForms	p. 233
A Custom Dialog Box Example	p. 237
Using Dialog Box Controls	p. 245
Getting Started with Dialog Box Controls	p. 245
Dialog Box Controls -- the Details	p. 249
Working with Dialog Box Controls	p. 259
Dialog Box Aesthetics	p. 262
Dialog Box Techniques and Tricks	p. 263
Using Dialog Boxes	p. 263
An Example	p. 264
More Dialog Box Examples	p. 270
A Dialog Box Checklist	p. 283
Creating Custom Toolbars and Menus	p. 285
Customizing the Excel Toolbars	p. 287
Introducing CommandBars	p. 287
What You Can Do with Toolbars	p. 287
Working with Toolbars	p. 289
Adding and Removing Toolbar Controls	p. 293
Distributing Toolbars	p. 295
Using VBA to Manipulate Toolbars	p. 296
VBA Examples	p. 300
When the Normal Excel Menus Aren't Good Enough	p. 305
Menu Terminology	p. 305
How Excel Handles Menus	p. 306
Customizing Menus Directly	p. 307
The CommandBar Object	p. 308
Some Menu Examples	p. 311
Working with Shortcut Menus	p. 318
Finding Out More	p. 320
Putting It All Together	p. 321
Creating Worksheet Functions -- and Living to Tell about It	p. 323
Why Create Custom Functions?	p. 323
Function Basics	p. 324
Writing Functions	p. 325
Function Arguments	p. 325
Function Examples	p. 326
The Paste Function Dialog Box	p. 334
Creating Excel Add-Ins	p. 337
Okay ... So What Is an Add-In?	p. 337
Why Create Add-Ins?	p. 338
Working with Add-Ins	p. 338
Add-In Basics	p. 339
An Add-In Example	p. 340
Creating Excel Applications for Others	p. 347
What Is a Spreadsheet Application?	p. 347
Application Development, Step By Step	p. 349
The Part of Tens	p. 357
Ten VBA Tips and Tricks	p. 359
Getting VBA Help, Fast	p. 359
Speeding Up Your Macros	p. 359
Avoiding Excel's Questions	p. 359
Displaying One Procedure at a Time	p. 360
Using With-End With	p. 360
Reducing the Size of a Workbook	p. 361
Avoiding a Workbook_Open Subroutine	p. 361
Using Your Personal Macro Workbook	p. 361
Displaying Messages in the Status Bar	p. 362
Forcing Yourself to Declare All Variables	p. 362
Ten VBA Questions (And Answers)	p. 363
The Top Ten Questions about VBA	p. 363
Ten Excel Resources	p. 367
The VBA Online Help System	p. 367
Microsoft Product Support	p. 367
Internet Newsgroups	p. 367
Internet Web Sites	p. 368
Internet Mailing Lists	p. 369
Online Newsletters	p. 369
Online Services	p. 369
Local User Groups	p. 370
My Book	p. 370
My E-mail Address	p. 370
Index	p. 371
Book Registration Information
Table of Contents provided by Syndetics. All Rights Reserved.