Excel VBA 24-Hour Trainer

Introduction xxvii Part I: Understanding the Basics Lesson 1: Introducing VBA 3 What is VBA? 3 A Brief History of VBA 4 What VBA Can Do for You 5  Automating a Recurring Task 5 Automating a Repetitive Task 5 Running a Macro Automatically if Another Action Takes Place 5 Creating Your Own Worksheet Functions 7 Simplifying the Workbook’s Look and Feel for Other Users 7 Controlling Other Office Applications from Excel 7 Liabilities of VBA 8 Try It 9 Lesson 2: Getting Started with Macros 11 Composing Your First Macro 11 Accessing the VBA Environment 11 Using the Macro Recorder 16 Running a Macro 21 The Macro Dialog Box 21 Shortcut Key 22 Try It 22 Lesson Requirements 22 Hints 22 Step-by-Step 23 Lesson 3: Introducing the Visual Basic Editor 25 What is the VBE? 25 How to Get Into the VBE 25 Understanding the VBE 26 Understanding Modules 28 Using the Object Browser 28 Exiting the VBE 30 Try It 30 Lesson 4: Working in the VBE 33 Toolbars in the VBE 33 Macros and Modules 33 Locating Your Macros 33 Understanding the Code 36 Editing a Macro with Comments and Improvements to the Code 37 Deleting a Macro 39 Inserting a Module 39 Renaming a Module 41 Deleting a Module 42 Locking and Protecting the VBE 43 Try It 44 Lesson Requirements 44 Hints 44 Step-by-Step 45 Part II: Diving Deeper into VBA Lesson 5: Object-Oriented Programming: An Overview 49 What “Object-Oriented Programming” Means 49 The Object Model 50 Properties 51 Methods 51 Collections 52 Try It 53 Lesson 6: Variables, Data Types, and Constants 55 What is a Variable? 55 Assigning Values to Variables 56 Why You Need Variables 56 Data Types 57 Understanding the Different Data Types 57 Declaring a Variable for Dates and Times 58 Declaring a Variable with the Proper Data Type 59 Forcing Variable Declaration 59 Understanding a Variable’s Scope 61 Local Macro Level Only 62 Module Level 62 Application Level 63 Constants 63 Try It 64 Lesson Requirements 64 Step-by-Step 64 Lesson 7: Understanding Objects and Collections 67 Workbooks 67 Cells and Ranges 69 SpecialCells 70 Try It 71 Lesson Requirements 71 Step-by-Step 71 Lesson 8: Working with Ranges 75 Working with Contiguously Populated Ranges 75 Using the Cells Property 76 Using CurrentRegion 76 Working with Noncontiguously Populated Ranges 77 Using Range with Several Cells 77 Using OFFSET 78 Using RESIZE 78 Identifying a Data Range 79 Identifying the UsedRange 79 Finding the Dynamic Last Rows and Columns 80 Identifying Where the Range Starts and Ends When No Start or End Point is Known 81 Try It 82 Lesson Requirements 82 Hints 82 Step-by-Step 82 Lesson 9: Making Decisions with VBA 85 Understanding Logical Operators 85 AND 86 OR 86 NOT 87 Choosing Between This or That 88 If…Then 88 If…Then…Else 89 If…Then…ElseIf 90 IIF 90 Select Case 91 Getting Users to Make Decisions 92 Message Boxes 93 Input Boxes 94 Try It 94 Lesson Requirements 95 Hints 95 Step-by-Step 95 Part III: Beyond the Macro Recorder: Writing Your Own Code Lesson 10: Repeating Actions with Loops 101 What is a Loop? 101 Types of Loops 102 Do…While 106 Do…Until 107 Do…Loop While 109 Do…Loop Until 109 While…Wend 110 Nesting Loops 110 Try It 111 Lesson Requirements 111 Hints 111 Step-by-Step 111 Lesson 11: Programming Formulas 113 Understanding A1 and R1C1 References 113 Getting Started with a Few One-Liners 114 Comparing the Interface of A1 and R1C1 Styles 115 Toggling between A1 and R1C1 Style Views 116 Programming Your Formula Solutions with VBA 118 Using a Mixed Reference to Fill Empty Cells with the Value from Above 118 Using a Named Range with Relative, Mixed, and Absolute References 119 Programming an Array Formula 120 Summing Lists of Different Sizes along a Single Row 122 Try It 124 Lesson Requirements 124 Step-by-Step 125 Lesson 12: Working with Arrays 127 What is an Array? 127 What Arrays Can Do for You 128 Declaring Arrays 129 The Option Base Statement 130 Boundaries in Arrays 132 Declaring Arrays with Fixed Elements 132 Declaring Dynamic Arrays with ReDim and Preserve 133 Try It 134 Lesson Requirements 134 Step-by-Step 135 Lesson 13: Automating Procedures with Worksheet Events 137 What is an Event? 137 Worksheet Events: An Overview 138 Where Does the Worksheet Event Code Go? 138 Enabling and Disabling Events 140 Examples of Common Worksheet Events 141 Worksheet_Change Event 141 Worksheet_SelectionChange Event 141 Worksheet_BeforeDoubleClick Event 142 Worksheet_Before RightClick Event 142 Worksheet_FollowHyperlink Event 142 Worksheet_Activate Event 143 Worksheet_Deactivate Event 144 Worksheet_Calculate Event 144 Worksheet_PivotTableUpdate Event 144 Try It 144 Lesson Requirements 145 Step-by-Step 145 Lesson 14: Automating Procedures with Workbook Events 149 Workbook Events: An Overview 149 Where Does the Workbook Event Code Go? 149 Entering Workbook Event Code 151 Examples of Common Workbook Events 153 Workbook_Open Event 153 Workbook_BeforeClose Event 154 Workbook_Activate Event 154 Workbook_Deactivate Event 154 Workbook_SheetChange Event 154 Workbook_SheetSelectionChange Event 155 Workbook_SheetBeforeDoubleClick Event 155 Workbook_SheetBeforeRightClick Event 156 Workbook_SheetPivotTableUpdate Event 156 Workbook_NewSheet Event 156 Workbook_BeforePrint Event 157 Workbook_SheetActivate Event 157 Workbook_SheetDeactivate Event 157 Workbook_BeforeSave Event 158 Try It 158 Lesson Requirements 158 Step-by-Step 158 Lesson 15: Handling Duplicate Items and Records 161 Deleting Rows Containing Duplicate Entries 161 Deleting Rows with Duplicates in a Single Column 161 Deleting Rows with Duplicates in More Than One Column 164 Deleting Some Duplicates and Keeping Others 165 Working with Duplicate Data 167 Compiling a Unique List from Multiple Columns 167 Updating a Comment to List Unique Items 169 Selecting a Range of Duplicate Items 171 Inserting an Empty Row at Each Change in Items 172 Try It 173 Lesson Requirements 174 Hints 174 Step-by-Step 174 Lesson 16: Using Embedded Controls 181 Working with Form Controls and ActiveX Controls 181 The Forms Toolbar 182 Buttons 183 Using Application.Caller with Form Controls 184 The Control Toolbox 186 CommandButtons 187 Try It 191 Lesson Requirements 192 Step-by-Step 192 Lesson 17: Programming Charts 199 Adding a Chart to a Chart Sheet 200 Adding an Embedded Chart to a Worksheet 202 Moving a Chart 204 Looping Through All Embedded Charts 206 Deleting Charts 207 Renaming a Chart 208 Try It 208 Lesson Requirements 208 Step-by-Step 209 Lesson 18: Programming Pivottables and Pivotcharts 213 Creating a PivotTable Report 213 Hiding the PivotTable Field List 217 Formatting Numbers in the Values Area 219 Pivoting Your Data 222 Creating a PivotChart 223 Understanding PivotCaches 226 Manipulating PivotFields in VBA 230 Manipulating PivotItems with VBA 231 Creating a PivotTables Collection 231 Try It 232 Lesson Requirements 232 Step-by-Step 233 Lesson 19: User-Defined Functions 237 What is a User-Defined Function? 237 Characteristics of User-Defined Functions 237 Anatomy of a UDF 238 UDF Examples That Solve Common Tasks 239 Summing Numbers in Colored Cells 239 Extracting Numbers or Letters from an Alphanumeric String 241 Extracting the Address from a Hyperlink 242 Volatile Functions 243 Returning the Name of the Active Worksheet and Workbook 243 UDFs with Conditional Formatting 244 Calling Your UDF from a Macro 245 Adding a Description to the Insert Function Dialog Box 246 Try It 248 Lesson Requirements 248 Step-by-Step 249 Lesson 20: Debugging Your Code 251 What is Debugging? 251 What Causes Errors? 252 Weapons of Mass Debugging 254 The Debug Toolbar 254 Trapping Errors 264 Error Handler 264 Bypassing Errors 265 Try It 266 Lesson Requirements 267 Hints 267 Step-by-Step 267 Part IV: Advanced Programming Techniques Lesson 21: Creating Userforms 271 What is a UserForm? 271 Creating a UserForm 272 Designing a UserForm 273 Adding Controls to a UserForm 274 Showing a UserForm 280 Where Does the UserForm’s Code Go? 281 Closing a UserForm 281 Unloading a UserForm 282 Hiding a UserForm 283 Try It 283 Lesson Requirements 283 Step-by-Step 283 Lesson 22: Userform Controls and Their Functions 285 Understanding the Frequently Used UserForm Controls 285 CommandButtons 286 Labels 287 TextBoxes 288 ListBoxes 290 ComboBoxes 292 CheckBoxes 295 OptionButtons 296 Frames 298 MultiPages 300 Try It 301 Lesson Requirements 301 Step-by-Step 301 Lesson 23: Advanced Userforms 305 The UserForm Toolbar 305 Modal versus Modeless 306 Disabling the UserForm’s Close Button 307 Maximizing Your UserForm’s Size 308 Selecting and Displaying Photographs on a UserForm 308 Unloading a UserForm Automatically 309 Pre-sorting the ListBox and ComboBox Items 310 Populating ListBoxes and ComboBoxes with Unique Items 312 Displaying a Real-Time Chart in a UserForm 314 Try It 315 Lesson Requirements 315 Step-by-Step 315 Lesson 24: Class Modules 321 What is a Class? 321 What is a Class Module? 322 Creating Your Own Objects 323 An Important Benefit of Class Modules 323 Creating Collections 326 Class Modules for Embedded Objects 326 Try It 330 Lesson Requirements 330 Step-by-Step 330 Lesson 25: Add-Ins 335 What is an Excel Add-In? 335 Creating an Add-In 336 Converting a File to an Add-In 341 Installing an Add-In 342 Creating a User Interface for Your Add-In 346 Changing the Add-In’s Code 348 Closing Add-Ins 349 Removing an Add-In from the Add-Ins List 349 Try It 350 Lesson Requirements 350 Step-by-Step 350 Lesson 26: Managing External Data 353 Creating QueryTables from Web Queries 353 Creating a QueryTable for Access 356 Using Text Files to Store External Data 359 Try It 361 Lesson Requirements 362 Step-by-Step 362 Lesson 27: Data Access with Activex Data Objects 365 Introducing ADO 365 The Connection Object 367 The Recordset Object 367 The Command Object 368 An Introduction to Structured Query Language (SQL) 368 The SELECT Statement 369 The INSERT Statement 369 The UPDATE Statement 370 The DELETE Statement 370 Try It 371 Lesson 28: Impressing Your Boss (or at Least Your Friends) 373 Selecting Cells and Ranges 373 Coloring the Active Cell, Row, or Column 373 Coloring the Current and Prior Selected Cells 375 Filtering Dates 376 Filtering between Dates 376 Filtering for Dates before Today’s Date 378 Filtering for Dates after Today’s Date 378 Deleting Rows for Filtered Dates More Than Three Years Ago 378 Setting Page Breaks for Specified Areas 379 Using a Comment to Log Changes in a Cell 380 Using the Windows API with VBA 381 Clearing the Clipboard 381 Emptying the Recycle Bin 382 Scheduling Your Workbook for Suicide 382 Try It 382 Lesson Requirements 383 Hints 383 Step-by-Step 383 Part V: Interacting with Other Office Applications Lesson 29: Overview of Office Automation from Excel 391 Why Automate Another Application? 391 Understanding Office Automation 392 Early Binding 392 Late Binding 394 Which One is Better? 394 Try It 395 Lesson Requirements 395 Hints 395 Step-by-Step 395 Lesson 30: Working with Word From Excel 399 Activating a Word Document 399 Activating the Word Application 399 Opening and Activating a Word Document 400 Creating a New Word Document 402 Copying an Excel Range to a Word Document 402 Printing a Word Document from Excel 403 Importing a Word Document to Excel 404 Try It 405 Lesson Requirements 406 Step-by-Step 406 Lesson 31: Working with Outlook From Excel 409 Opening Outlook 409 Composing an E-mail in Outlook from Excel 410 Creating a MailItem Object 410 Transferring an Excel Range to the Body of Your E-mail 411 Putting It All Together 413 E-mailing a Single Worksheet 415 Try It 415 Lesson Requirements 415 Step-by-Step 415 Lesson 32: Working with Access from Excel 419 Adding a Record to an Access Table 419 Exporting an Access Table to an Excel Spreadsheet 423 Creating a New Table in Access 426 Try It 427 Lesson Requirements 427 Step-by-Step 427 Lesson 33: Working with Powerpoint From Excel 431 Creating a New PowerPoint Presentation 431 Copying a Worksheet Range to a PowerPoint Slide 432 Copying Chart Sheets to PowerPoint Slides 433 Running a PowerPoint Presentation from Excel 435 Try It 436 Lesson Requirements 436 Step-by-Step 436 Index 441