Excel VBA Programming for Dummies

✍ Scribed by Walkenbach, John

• Publisher: For Dummies
• Year: 2010
• Tongue: English
• Leaves: 432
• Series: For Dummies
• Category: Library

✦ Synopsis

Take your Excel skills to the next level with VBA programmingNow that you've mastered Excel basics, it's time to move to the next level--creating your own, customized Excel 2010 solutions using Visual Basic for Applications (VBA). The new edition of this non-threatening guide is your key to getting there. Using step-by-step instruction and the accessible, friendly For Dummies style, this practical book shows you how to use VBA, write macros, customize your Excel apps to look and work the way you want, avoid errors, and more.

The book's Web site provides all the programming examples from the book.


Introduces you to a new range of Excel 2010 options Shows you how to use Visual Basic for Applications (VBA) to program your own custom Excel applications or to automate functions Explains how to create VBA macros, program, handle errors, debug, and more Covers Visual Basic Editor tools and functions; working with range objects; controlling program flow; and the easiest ways to create custom dialog boxes, toolbars, and menus Includes a companion Web site with all the sample programs from the book Create your own, custom Excel programs with this information-packed guide!

Note:CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

✦ Table of Contents

Excel® VBA Programming For Dummies®, 2nd Edition......Page 1
About the Author......Page 5
Author’s Acknowledgments......Page 7
Contents at a Glance......Page 9
Table of Contents......Page 11
Is This the Right Book?......Page 21
So You Want to Be a Programmer . . .......Page 22
What I Assume about You......Page 23
Obligatory Typographical Conventions Section......Page 24
Check Your Security Settings......Page 25
How This Book Is Organized......Page 26
Getting the Sample Files......Page 28
Now What?......Page 29
Part I: Introducing VBA......Page 31
Okay, So What Is VBA?......Page 33
What Can You Do with VBA?......Page 34
Advantages and Disadvantages of VBA......Page 36
VBA in a Nutshell......Page 38
An Excursion into Versions......Page 40
First Things First......Page 43
Taking the First Steps......Page 45
Recording the Macro......Page 46
Examining the Macro......Page 47
Saving Workbooks That Contain Macros......Page 50
Understanding Macro Security......Page 51
More about the NameAndTime Macro......Page 53
Part II: How VBA Works with Excel......Page 55
What Is the Visual Basic Editor?......Page 57
Working with the Project Window......Page 60
Working with a Code Window......Page 62
Customizing the VBA Environment......Page 69
Chapter 4: Introducing the Excel Object Model......Page 75
Climbing the Object Hierarchy......Page 76
Referring to Objects......Page 78
Diving into Object Properties and Methods......Page 80
Finding Out More......Page 84
Subs versus Functions......Page 89
Executing Sub procedures......Page 91
Executing Function procedures......Page 98
Is It Live or Is It VBA?......Page 101
Recording Basics......Page 102
Relative or Absolute?......Page 104
What Gets Recorded?......Page 107
Recording Options......Page 108
Is This Thing Efficient?......Page 110
Part III: Programming Concepts......Page 113
Using Comments in Your VBA Code......Page 115
Using Variables, Constants, and Data Types......Page 117
Using Assignment Statements......Page 129
Working with Arrays......Page 132
Using Labels......Page 134
A Quick Review......Page 135
Other Ways to Refer to a Range......Page 137
Some Useful Range Object Properties......Page 139
Some Useful Range Object Methods......Page 146
What Is a Function?......Page 149
Using Built-In VBA Functions......Page 150
Using Worksheet Functions in VBA......Page 155
Using Custom Functions......Page 159
Going with the Flow, Dude......Page 161
The GoTo Statement......Page 162
Knocking Your Code for a Loop......Page 171
Looping through a Collection......Page 178
Preparing for the Big Event......Page 181
Where Does the VBA Code Go?......Page 184
Writing an Event-Handler Procedure......Page 185
Introductory Examples......Page 187
Examples of Activation Events......Page 190
Other Worksheet-Related Events......Page 194
Events Not Associated with Objects......Page 197
Types of Errors......Page 203
An Erroneous Example......Page 204
Handling Errors Another Way......Page 208
Handling Errors: The Details......Page 210
An Intentional Error......Page 214
Species of Bugs......Page 217
Identifying Bugs......Page 218
Debugging Techniques......Page 219
About the Debugger......Page 222
Bug Reduction Tips......Page 228
Working with Ranges......Page 231
Changing Excel Settings......Page 240
Working with Charts......Page 242
VBA Speed Tips......Page 247
Part IV: Communicating with Your Users......Page 253
UserForm Alternatives......Page 255
The MsgBox Function......Page 256
The InputBox Function......Page 261
The GetOpenFilename Method......Page 264
The GetSaveAsFilename Method......Page 268
Getting a Folder Name......Page 269
Displaying Excel’s Built-in Dialog Boxes......Page 270
Knowing When to Use a UserForm......Page 273
Creating UserForms: An Overview......Page 274
Working with UserForms......Page 275
A UserForm Example......Page 280
Getting Started with Dialog Box Controls......Page 289
Dialog Box Controls: The Details......Page 293
Working with Dialog Box Controls......Page 304
Dialog Box Aesthetics......Page 307
A UserForm Example......Page 309
More UserForm Examples......Page 316
A Dialog Box Checklist......Page 331
What Happened to CommandBars?......Page 333
Ribbon Customization......Page 334
Customizing Shortcut Menus......Page 341
VBA Shortcut Menu Examples......Page 346
Creating a Custom Toolbar......Page 349
Part V: Putting It All Together......Page 351
Why Create Custom Functions?......Page 353
Understanding VBA Function Basics......Page 354
Working with Function Arguments......Page 355
Function Examples......Page 356
Functions That Return an Array......Page 363
Using the Insert Function Dialog Box......Page 365
Okay . . . So What’s an Add-In?......Page 369
Why Create Add-Ins?......Page 370
Working with Add-Ins......Page 371
Add-In Basics......Page 372
An Add-In Example......Page 373
Part VI: The Part of Tens......Page 381
Chapter 22: Ten VBA Questions (And Answers)......Page 383
Microsoft Product Support......Page 387
Internet Newsgroups......Page 388
Bing......Page 389
My Other Books......Page 390
Index......Page 391
Color My World......Page 411
Understanding Grayscale......Page 415
Experimenting with Colors......Page 418
Understanding Document Themes......Page 420
Don’t Confuse Passwords with Security......Page 427
Don’t Put Everything in One Procedure......Page 428
Do Get in the Habit of Experimenting......Page 429
Don’t Forget About Backups......Page 430

✦ Subjects

Nonfiction;Computer Science;Programming;Reference;Computers;Science;Software;Technical;Coding