Excel VBA 24-Hour Trainer

Tom Urtis is an Excel Microsoft Excel MVP, developer, and programmer with 30 years of experience in business management and developing spreadsheet and database applications for companies of all sizes. He is an Excel instructor and frequent contributor to newsgroups in the Excel community. Wrox guides are crafted to make learning programming languages and technologies easier than you think. Written by programmers for programmers, they provide a structured, tutorial format that will guide you through all the techniques involved.

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