Introduction 1 About This Book 2 Foolish Assumptions 2 Icons Used in This Book 3 Beyond the Book 3 Where to Go from Here 4 Part 1: Holy Macro Batman! 7 Chapter 1: Macro Fundamentals 9 Why Use a Macro? 9 Macro Recording Basics 10 Examining the macro 12 Testing the macro 13 Editing the macro 13 Comparing Absolute and Relative Macro Recording 14 Recording macros with absolute references 14 Recording macros with relative references 17 Other Macro Recording Concepts 19 Macro-enabled file extensions 19 Macro security in Excel 2010 19 Trusted locations 20 Storing macros in your Personal Macro Workbook 21 Assigning a macro to a button and other form controls 22 Placing a macro on the Quick Access Toolbar 23 Examples of Macros in Action 24 Building navigation buttons 24 Dynamically rearranging PivotTable data 25 Offering one-touch reporting options 27 Chapter 2: Getting Cozy with the Visual Basic Editor 29 Working in the Visual Basic Editor 29 The VBE menu bar 30 The VBE toolbar 31 The Project window 31 The Code window 31 The Immediate window 31 Working with the Project Window 32 Adding a new VBA module 32 Removing a VBA module 33 Working with a Code Window 34 Minimizing and maximizing windows 34 Getting VBA code into a module 35 Customizing the VBA Environment 37 The Editor tab 38 The Auto Syntax Check option 38 The Require Variable Declaration option 38 The Editor Format tab 40 The General tab 41 The Docking tab 42 Chapter 3: The Anatomy of Macros 43 A Brief Overview of the Excel Object Model 43 Understanding objects 44 Understanding collections 45 Understanding properties 45 Understanding methods 46 A Brief Look at Variables 46 The common variable types 47 Understanding Event Procedures 49 Worksheet events 49 Workbook events 51 Error Handling in a Nutshell 53 On Error GoTo SomeLabel 53 On Error Resume Next 54 On Error GoTo 0 55 Part 2: Making Short Work of Workbook Tasks 57 Chapter 4: Working with Workbooks 59 Creating a New Workbook from Scratch 60 How it works 60 How to use it 61 Saving a Workbook when a Particular Cell Is Changed 61 How it works 62 How to use it 63 Saving a Workbook before Closing 64 How it works 64 How to use it 65 Protecting a Worksheet on Workbook Close 66 How it works 66 How to use it 67 Unprotecting a Worksheet on Workbook Open 68 How it works 68 How to use it 68 Opening a Workbook to a Specific Tab 69 How it works 69 How to use it 70 Opening a Specific Workbook Defined by the User 70 How it works 71 How to use it 72 Determining Whether a Workbook Is Already Open 72 How it works 73 How to use it 75 Determining Whether a Workbook Exists in a Directory 75 How it works 76 How to use it 77 Closing All Workbooks at Once 77 How it works 77 How to use it 78 Printing All Workbooks in a Directory 78 How it works 79 How to use it 80 Preventing the Workbook from Closing Until a Cell Is Populated 80 How it works 80 How to use it 81 Creating a Backup of the Current Workbook with Today's Date 82 How it works 82 How to use it 83 Chapter 5: Working with Worksheets 85 Adding and Naming a New Worksheet 85 How it works 85 How to use it 86 Deleting All but the Active Worksheet 87 How it works 87 How to use it 88 Hiding All but the Active Worksheet 88 How it works 89 How to use it 90 Unhiding All Worksheets in a Workbook 90 How it works 90 How to use it 91 Moving Worksheets Around 92 How it works 92 How to use it 93 Sorting Worksheets by Name 93 How it works 93 How to use it 95 Grouping Worksheets by Color 95 How it works 96 How to use it 97 Copying a Worksheet to a New Workbook 98 How it works 98 How to use it 98 Creating a New Workbook for Each Worksheet 99 How it works 99 How to use it 100 Printing Specified Worksheets 101 How it works 101 How to use it 101 Protecting All Worksheets 102 How it works 102 How to use it 103 Unprotecting All Worksheets 104 How it works 104 How to use it 105 Creating a Table of Contents for Your Worksheets 106 How it works 106 How to use it 109 Zooming In and Out of a Worksheet with Double-Click 109 How it works 109 How to use it 110 Highlighting the Active Row and Column 110 How it works 111 How to use it 112 Part 3: One-Touch Data Manipulation 113 Chapter 6: Feeling at Home on the Range 115 Selecting and Formatting a Range 116 How it works 116 How to use it 117 Creating and Selecting Named Ranges 118 How it works 120 How to use it 120 Enumerating Through a Range of Cells 121 How it works 121 How to use it 122 Inserting Blank Rows in a Range 122 How it works 122 How to use it 124 Unhiding All Rows and Columns 124 How it works 124 How to use it 124 Deleting Blank Rows 125 How it works 125 How to use it 126 Deleting Blank Columns 127 How it works 127 How to use it 129 Limiting Range Movement to a Particular Area 129 How it works 129 How to use it 130 Selecting and Formatting All Formulas in a Workbook 131 How it works 131 How to use it 133 Finding and Selecting the First Blank Row or Column 133 How it works 134 How to use it 136 Chapter 7: Manipulating Data with Macros 137 Copying and Pasting a Range 138 How it works 138 How to use it 139 Converting All Formulas in a Range to Values 139 How it works 139 How to use it 141 Text to Columns on All Columns 141 How it works 142 How to use it 144 Converting Trailing Minus Signs 144 How it works 144 How to use it 146 Trimming Spaces from All Cells in a Range 147 How it works 147 How to use it 148 Truncating ZIP Codes to the Left Five 149 How it works 149 How to use it 151 Padding Cells with Zeros 151 How it works 152 How to use it 154 Replacing Blanks Cells with a Value 154 How it works 154 How to use it 156 Appending Text to the Left or Right of Your Cells 156 How it works 156 How to use it 158 Cleaning Up Non-Printing Characters 158 How it works 159 How to use it 160 Highlighting Duplicates in a Range of Data 160 How it works 161 How to use it 162 Hiding All but Rows Containing Duplicate Data 162 How it works 163 How to use it 165 Selectively Hiding AutoFilter Drop-down Arrows 165 How it works 165 How to use it 167 Copying Filtered Rows to a New Workbook 167 How it works 167 How to use it 168 Showing Filtered Columns in the Status Bar 168 How it works 169 How to use it 171 Part 4: Macro-Charging Reports and Emails 173 Chapter 8: Automating Common Reporting Tasks 175 Refreshing All PivotTables in a Workbook 176 How it works 176 How to use it 177 Creating a PivotTable Inventory Summary 177 How it works 178 How to use it 180 Adjusting All Pivot Data Field Titles 181 How it works 181 How to use it 183 Setting All Data Items to Sum 183 How it works 183 How to use it 185 Applying Number Formatting for All Data Items 185 How it works 186 How to use it 189 Sorting All Fields in Alphabetical Order 189 How it works 189 How to use it 190 Applying a Custom Sort to Data Items 191 How it works 191 How to use it 192 Applying PivotTable Restrictions 192 How it works 192 How to use it 194 Applying Pivot Field Restrictions 194 How it works 194 How to use it 196 Automatically Deleting PivotTable Drill-Down Sheets 196 How it works 196 How to use it 198 Printing a PivotTable for Each Report Filter Item 200 How it works 200 How to use it 202 Creating a New Workbook for Each Report Filter Item 202 How it works 203 How to use it 205 Resizing All Charts on a Worksheet 205 How it works 205 How to use it 207 Aligning a Chart to a Specific Range 207 How it works 207 How to use it 209 Creating a Set of Disconnected Charts 209 How it works 209 How to use it 211 Printing All Charts on a Worksheet 211 How it works 211 How to use it 212 Chapter 9: Sending Emails from Excel 213 Mailing the Active Workbook as an Attachment 214 How it works 214 How to use it 215 Mailing a Specific Range as an Attachment 216 How it works 216 How to use it 218 Mailing a Single Sheet as an Attachment 218 How it works 218 How to use it 220 Sending Mail with a Link to Your Workbook 220 How it works 220 How to use it 222 Mailing All Email Addresses in Your Contact List 222 How it works 222 How to use it 224 Saving All Attachments to a Folder 224 How it works 224 How to use it 226 Saving Certain Attachments to a Folder 227 How it works 227 How to use it 229 Chapter 10: Wrangling External Data with Macros 231 Working with External Data Connections 231 Manually creating a connection 232 Manually editing data connections 235 Using Macros to Create Dynamic Connections 236 Iterating through All Connections in a Workbook 239 Using ADO and VBA to Pull External Data 240 Understanding ADO syntax 241 Using ADO in a macro 243 Working with text files 245 Part 5: Part of Tens 251 Chapter 11: Ten Handy Visual Basic Editor Tips 253 Applying Block Comments 254 Copying Multiple Lines of Code at Once 255 Jumping between Modules and Procedures 255 Teleporting to Your Functions 256 Staying in the Right Procedure 256 Stepping through Your Code 257 Stepping to a Specific Line in Your Code 258 Stopping Your Code at a Predefined Point 258 Seeing the Beginning and End of Variable Values 259 Turning Off Auto Syntax Check 260 Chapter 12: Ten Places to Turn for Macro Help 263 Let Excel Write the Macro for You 264 Use the VBA Help Files 264 Pilfer Code from the Internet 265 Leverage User Forums 265 Visit Expert Blogs 266 Mine YouTube for Video Training 267 Attend Live and Online Training Classes 267 Learn from the Microsoft Office Dev Center 267 Dissect the Other Excel Files in your Organization 268 Ask Your Local Excel Genius 268 Chapter 13: Ten Ways to Speed Up Your Macros 269 Halt Sheet Calculations 269 Disable Sheet Screen Updating 270 Turn Off Status Bar Updates 271 Tell Excel to Ignore Events 272 Hide Page Breaks 273 Suspend PivotTable Updates 273 Steer Clear of Copy and Paste 274 Use the With Statement 275 Don't Explicitly Select Objects 276 Avoid Excessive Trips to the Worksheet 277 Index 279
Michael Alexander is a Microsoft Certified Application Developer (MCAD) who has been named a Microsoft MVP for his ongoing contributions to the Excel community. He has more than 15 years of experience in consulting and developing Office solutions, and shares basic Access and Excel tips on his free tutorial site, www.datapigtechnologies.com.