Excel VBA Programming For Dummies

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at www.datapigtechnologies.com.

Introduction 1 About This Book 1 Obligatory Typographical Conventions Section 2 Check Your Security Settings 3 Foolish Assumptions 4 Icons Used in This Book 5 Sample Files Online 5 Where to Go from Here 6 Part 1: Getting Started with Excel VBA Programming 7 Chapter 1: What Is VBA? 9 Okay, So What Is VBA? 9 What Can You Do with VBA? 10 Inserting a bunch of text 11 Automating a task you perform frequently 11 Automating repetitive operations 11 Creating a custom command 11 Creating a custom button 12 Developing new worksheet functions 12 Creating custom add-ins for Excel 12 Advantages and Disadvantages of VBA 12 VBA advantages 12 VBA disadvantages 13 VBA in a Nutshell 13 Excel Compatibility 16 Chapter 2: Jumping Right In 17 First Things First 17 What You'll Be Doing 18 Taking the First Steps 18 Recording the Macro 19 Testing the Macro 21 Examining the Macro 21 Modifying the Macro 23 Saving Workbooks That Contain Macros 24 Understanding Macro Security 24 Revealing More about the NameAndTime Macro 26 Part 2: How VBA Works with Excel 29 Chapter 3: Working in the Visual Basic Editor 31 What Is the Visual Basic Editor? 31 Activating the VBE 32 Understanding VBE components 32 Working with the Project Window 34 Adding a new VBA module 35 Removing a VBA module 36 Exporting and importing objects 36 Working with a Code Window 37 Minimizing and maximizing windows 37 Creating a module 38 Getting VBA code into a module 39 Entering code directly 39 Using the macro recorder 42 Copying VBA code 45 Customizing the VBA Environment 45 Using the Editor tab 45 Using the Editor Format tab 48 Using the General tab 49 Using the Docking tab 50 Chapter 4: Introducing the Excel Object Model 51 Excel Is an Object? 52 Climbing Down the Object Hierarchy 52 Wrapping Your Mind around Collections 53 Referring to Objects 54 Navigating through the hierarchy 55 Simplifying object references 56 Diving into Object Properties and Methods 56 Object properties 58 Object methods 59 Object events 61 Finding Out More 61 Using VBA's Help system 61 Using the Object Browser 62 Automatically listing properties and methods 63 Chapter 5: VBA Sub and Function Procedures 65 Understanding Subs versus Functions 65 Looking at Sub procedures 66 Looking at Function procedures 66 Naming Subs and Functions 67 Executing Sub procedures 68 Executing the Sub procedure directly 70 Executing the procedure from the Macro dialog box 70 Executing a macro by using a shortcut key 71 Executing the procedure from a button or shape 72 Executing the procedure from another procedure 74 Executing Function procedures 75 Calling the function from a Sub procedure 75 Calling a function from a worksheet formula 76 Chapter 6: Using the Excel Macro Recorder 79 Recording Basics 80 Preparing to Record 81 Relative or Absolute? 82 Recording in absolute mode 82 Recording in relative mode 83 What Gets Recorded? 84 Recording Options 86 Macro name 86 Shortcut key 87 Store Macro In option 87 Description 87 Is This Thing Efficient? 87 Part 3: Programming Concepts 91 Chapter 7: Essential VBA Language Elements 93 Using Comments in Your VBA Code 93 Using Variables, Constants, and Data Types 95 Understanding variables 95 What are VBA's data types? 97 Declaring and scoping variables 98 Working with constants 104 Premade constants 105 Working with strings 106 Working with dates 107 Using Assignment Statements 108 Assignment statement examples 108 About that equal sign 108 Smooth operators 109 Working with Arrays 111 Declaring arrays 111 Multidimensional arrays 112 Dynamic arrays 112 Using Labels 113 Chapter 8: Working with Range Objects 115 A Quick Review 115 Other Ways to Refer to a Range 117 The Cells property 117 The Offset property 118 Some Useful Range Object Properties 119 The