Microsoft Excel VBA programming for dummies

**Take your Excel skills to the next level with VBA programming Now 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.**** 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 Having Excel and just using it for standard spreadsheets is a little like getting the ultimate cable system and a 50" flat panel plasma HDTV and using it exclusively to watch Lawrence Welk reruns. With Visual Basic for Applications (VBA) programming, you can take advantage of numerous Excel options such as: creating new worksheet functions; automating tasks and operations; creating new appearances, toolbars, and menus; designing custom dialog boxes and add-ins; and much more. This guide is not for rank Excel amateurs. It's for intermediate to advanced Excel users who want to learn VBA programming (or whose bosses want them to learn VBA programming). You need to know your way around Excel before you start creating customized short cuts or systems for speeding through Excel functions. If you're an intermediate or advanced Excel user, Excel VBA For Dummies helps you take your skills (and your spreadsheets) to the next level. It includes: An introduction to the VBA language A hands-on, guided, step-by-step walk through developing a useful VBA macro, including recording, testing, and changing it, and testing it The essential foundation, including the Visual Basic Editor (VBE) and its components, modules, Excel object model, subroutines and functions, and the Excel macro recorder The essential VBA language elements, including comments, variables and constants, and labels Working with Range objects and discovering useful Range objective properties and methods Using VBA and worksheet functions, including a list and examples Programming constructions, including the GoTo statement, the If-Then structure, Select Case, For-Next loop, Do-While loop, and Do-Until loop Automatic procedures and Workbook events, including a table and event-handler procedures Error-handling and bug extermination techniques, and using the Excel debugging tools Creating custom dialog boxes, also known as UserForms, with a table of the toolbox controls and their capabilities, how-to for the dialog box controls, and UserForm techniques and tricks Customizing the Excel toolbars Using VBA code to modify the Excel menu system Creating worksheet functions and working with various types of arguments Creating Excel add-ins such as new worksheet functions you can use in formulas or new commands or utilities Author John Walkenbach is a leading authority on spreadsheet software and the author of more than 40 spreadsheet books including Excel 2003 Bible and Excel 2003 Power Programming with VBA. While this guide includes tons of examples and screenshots, Walkenbach knows there's no substitute for hands-on learning. The book is complete with: A dedicated companion Web site that includes bonus chapters plus all sample programs to save you a lot of typing and let you play around and experiment with various changes Information to help you make the most of Excel's built-in Help system so you can find out other stuff you may need to know What are you waiting for? Sure, learning to do VBA programming takes a little effort, but it's a Very Big Accomplishment. " ... Open the book and find : how to work in the Visual Basic Editor ; tips for error-handling and bug extermination ; ways to build automatic procedures and events ; how to use Excel macro recorder ; UserForm technique and tricks ; instructions for designing VBA macros ; steps for accessing your macros through the user interface ; secrets for creating Excel add-ins ..."--Quatrième de couverture " ... Open the book and find : How to work in the Visual Basic Editor ; Tips for error-handling and bug extermination ; Ways to build automatic procedures and events ; How to use Excel macro recorder ; UserForm technique and tricks ; Instructions for designing VBA macros ; Steps for accessing your macros through the user interface ; Secrets for creating Excel add-ins ..."--Page 4 of cover