Step-by-step instructions for creating VBA macros
Harness the power of VBA and create custom Excel applications
Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more.
Discover how to Grasp essential programming...
Step-by-step instructions for creating VBA macros Harness the power of VBA and create custom Excel applications Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more. Discover how toGrasp essential programming conceptsUse the Visual Basic® EditorNavigate the new Excel user interfaceCommunicate with your usersDeal with errors and bugs
Introduction 1Is This the Right Book? 1So You Want to Be a Programmer 2Why Bother? 2What I Assume about You 3Obligatory Typographical Conventions Section 4Check Your Security Settings 4How This Book Is Organized 6Introducing VBA 6How VBA Works with Excel 6Programming Concepts 6Communicating with Your Users 6Putting It All Together 7The Part of Tens 7Marginal Icons 7Get the Sample Files 8Now What? 8Introducing VBA 9What Is VBA? 11Okay, So What Is VBA? 11What Can You Do with VBA? 12Inserting a bunch of text 13Automating a task you perform frequently 13Automating repetitive operations 13Creating a custom command 13Creating a custom button 14Developing new worksheet functions 14Creating complete, macro-driven applications 14Creating custom add-ins for Excel 14Advantages and Disadvantages of VBA 14VBA advantages 15VBA disadvantages 15VBA in a Nutshell 16An Excursion into Versions 18Jumping Right In 21First Things First 21What You'll Be Doing 22Taking the First Steps 23Recording the Macro 23Testing the Macro 25Examining the Macro 25Modifying the Macro 28Saving Workbooks that Contain Macros 29Understanding Macro Security 29More about the NameAndTime Macro 31How VBA Works with Excel 33Working In the Visual Basic Editor 35What Is the Visual Basic Editor? 35Activating the VBE 35Understanding VBE components 36Working with the Project Explorer 38Adding a new VBA module 39Removing a VBA module 39Exporting and importing objects 40Working with a Code Window 40Minimizing and maximizing windows 40Creating a module 42Getting VBA code into a module 42Entering code directly 43Using the macro recorder 45Copying VBA code 47Customizing the VBA Environment 47Using the Editor tab 48Using the Editor Format tab 50Using the General tab 52Using the Docking tab 52Introducing the Excel Object Model 53Excel Is an Object? 54Climbing the Object Hierarchy 54Wrapping Your Mind around Collections 56Referring to Objects 56Navigating through the hierarchy 57Simplifying object references 58Diving into Object Properties and Methods 59Object properties 59Object methods 62Object events 63Finding Out More 63Using VBA's Help system 63Using the Object Browser 64VBA Sub and Function Procedures 67Subs versus Functions 67Looking at Sub procedures 68Looking at Function procedures 68Naming Subs and Functions 69Executing Sub procedures 69Executing the Sub procedure directly 71Executing the procedure from the Macro dialog box 72Executing a macro by using a shortcut key 72Executing the procedure from a button or shape 74Executing the procedure from another procedure 76Executing Function procedures 76Calling the function from a Sub procedure 77Calling a function from a worksheet formula 77Using the Excel Macro Recorder 79Is It Live or Is It VBA? 79Recording Basics 80Preparing to Record 82Relative or Absolute? 82Recording in absolute mode 82Recording in relative mode 83What Gets Recorded? 85Recording Options 86Macro name 87Shortcut key 87Store Macro In 87Description 87Is This Thing Efficient? 88Programming Concepts 91Essential VBA Language Elements 93Using Comments in Your VBA Code 93Using Variables, Constants, and Data Types 95Understanding variables 95What are VBA's data types? 97Declaring and scoping variables 98Working with constants 103Working with strings 105Working with dates 106Using Assignment Statements 106Assignment statement examples 107About that equal sign 107Other operators 108Working with Arrays 109Declaring arrays 109Multidimensional arrays 110Dynamic arrays 111Using Labels 111Working with Range Objects 113A Quick Review 113Other Ways to Refer to a Range 114The Cells property 115The Offset property 116Referring to entire columns and rows 116Some Useful Range Object Properties 117The Value property 117The Text property 118The Count property 118The Column and Row properties 118The Address property 119The HasFormula property 119The Font property 120The Interior property 120The Formula property 121The NumberFormat property 121Some Useful Range Object Methods 122The Select method 122The Copy and Paste methods 123The Clear method 123The Delete method 124Using VBA and Worksheet Functions 125What Is a Function? 125Using Built-in VBA Functions 126VBA function examples 126VBA functions that do more than return a value 128Discovering VBA functions 129Using Worksheet Functions in VBA 132Worksheet function examples 133Entering worksheet functions 136More about Using Worksheet Functions 136Using Custom Functions 137Controlling Program Flow and Making Decisions 139Going with the Flow, Dude 139The GoTo Statement 140Decisions, decisions 141The If-Then structure 141The Select Case structure 146Knocking Your Code for a Loop 149For-Next loops 150Do-While loop 153Do-Until loop 154Looping through a Collection 155Automatic Procedures and Events 157Preparing for the Big Event 157Are events useful? 159Programming event-handler procedures 160Where Does the VBA Code Go? 160Writing an Event-Handler Procedure 161Introductory Examples 163The Open event for a workbook 163The BeforeClose event for a workbook 165The BeforeSave event for a workbook 165Examples of Activation Events 166Activate and deactivate events in a sheet 166Activate and deactivate events in a workbook 167Workbook activation events 168Other Worksheet-Related Events 169The BeforeDoubleClick event 169The BeforeRightClick event 169The Change event 170Events Not Associated with Objects 172The OnTime event 172Keypress events 174Error-Handling Techniques 177Types of Errors 177An Erroneous Example 178The macro's not quite perfect 179The macro is still not perfect 180Is the macro perfect yet? 180Giving up on perfection 181Handling Errors Another Way 182Revisiting the EnterSquareRoot procedure 182About the On Error statement 183Handling Errors: The Details 184Resuming after an error 184Error handling in a nutshell 186Knowing when to ignore errors 186Identifying specific errors 187An Intentional Error 188Bug Extermination Techniques 191Species of Bugs 191Identifying Bugs 192Debugging Techniques 193Examining your code 193Using the MsgBox function 194Inserting Debug.Print statements 195Using the VBA debugger 196About the Debugger 196Setting breakpoints in your code 196Using the Watch window 199Using the Locals Window 201Bug Reduction Tips 201VBA Programming Examples 203Working with Ranges 203Copying a range 204Copying a variable-sized range 205Selecting to the end of a row or column 206Selecting a row or column 207Moving a range 207Looping through a range efficiently 208Prompting for a cell value 209Determining the selection type 210Identifying a multiple selection 211Changing Excel Settings 211Changing Boolean settings 212Changing non-Boolean settings 212Working with Charts 213Modifying the chart type 214Looping through the ChartObjects collection 214Modifying chart properties 215Applying chart formatting 215VBA Speed Tips 216Turning off screen updating 216Turning off automatic calculation 217Eliminating those pesky alert messages 218Simplifying object references 219Declaring variable types 219Using the With-End With structure 220Communicating with Your Users 221Simple Dialog Boxes 223Why Create UserForms? 223The MsgBox Function 224Displaying a simple message box 225Getting a response from a message box 225Customizing message boxes 226The InputBox Function 229InputBox syntax 229An InputBox example 229The GetOpenFilename Method 231The syntax 232A GetOpenFilename example 232Selecting multiple files 234The GetSaveAsFilename Method 235Getting a Folder Name 236Displaying Excel's Built-in Dialog Boxes 236UserForm Basics 239Knowing When to Use a UserForm 239Creating UserForms: An Overview 240Working with UserForms 241Inserting a new UserForm 241Adding controls to a UserForm 242Changing properties for a UserForm control 243Viewing the UserForm Code window 244Displaying a UserForm 245Using information from a UserForm 245A UserForm Example 246Creating the UserForm 246Adding the CommandButtons 247Adding the OptionButtons 248Adding event-handler procedures 250Creating a macro to display the dialog box 251Making the macro available 252Testing the macro 253Using UserForm Controls 255Getting Started with Dialog Box Controls 255Adding controls 255Introducing control properties 257Dialog Box Controls: The Details 259CheckBox control 259ComboBox control 260CommandButton control 261Frame control 262Image control 262Label control 263ListBox control 264MultiPage control 265OptionButton control 266RefEdit control 267ScrollBar control 267SpinButton control 268TabStrip control 269TextBox control 269ToggleButton control 270Working with Dialog Box Controls 270Moving and resizing controls 270Aligning and spacing controls 271Accommodating keyboard users 272Testing a UserForm 273Dialog Box Aesthetics 274UserForm Techniques and Tricks 275Using Dialog Boxes 275A UserForm Example 275Creating the dialog box 276Writing code to display the dialog box 278Making the macro available 279Trying out your dialog box 279Adding event-handler procedures 280Validating the data 282Now the dialog box works 282More UserForm Examples 282A ListBox example 282Selecting a range 287Using multiple sets of OptionButtons 288Using a SpinButton and a TextBox 289Using a UserForm as a progress indicator 291Creating a tabbed dialog box 295Displaying a chart in a dialog box 296A Dialog Box Checklist 297Accessing Your Macros Through the User Interface 299CommandBars and Excel 2007 299Excel 2007 Ribbon Customization 301Working with CommandBars 304Commanding the CommandBars collection 304Listing all shortcut menus 304Referring to CommandBars 305Referring to controls in a CommandBar 306Properties of CommandBar controls 307VBA Shortcut Menu Examples 309Resetting all built-in right-click menus 309Adding a new item to the Cell shortcut menu 309Disabling a shortcut menu 311Creating a Custom Toolbar 312Putting It Alt Together 315Creating Worksheet Functions and Living to Tell about It 317Why Create Custom Functions? 317Understanding VBA Function Basics 318Writing Functions 319Working with Function Arguments 319Function Examples 320A function with no argument 320A function with one argument 320A function with two arguments 322A function with a range argument 323A function with an optional argument 324A function with an indefinite number of arguments 326Functions That Return an Array 327Returning an array of month names 327Returning a sorted list 328Using the Insert Function Dialog Box 330Displaying the function's description 330Argument descriptions 331Creating Excel Add-Ins 333Okay...So What's an Add-In? 333Why Create Add-Ins? 334Working with Add-Ins 335Add-in Basics 336An Add-in Example 337Setting up the workbook 337Testing the workbook 339Adding descriptive information 340Protecting the VBA code 341Creating the add-in 341Opening the add-in 341Distributing the add-in 342Modifying the add-in 342The Part of Tens 345Ten VBA Questions (And Answers) 347The Top Ten Questions about VBA 347(Almost) Ten Excel Resources 351The VBA Help System 351Microsoft Product Support 351Internet Newsgroups 352Internet Web Sites 353Excel Blogs 353Google 353Local User Groups 354My Other Book 354Index 355