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 Liabilities of VBA 8 Try It 9 LESSON 2: GETTING STARTED WITH MACROS 11 Composing Your First Macro 11 Running a Macro 21 Try It 22 LESSON 3: INTRODUCING THE VISUAL BASIC EDITOR 25 What Is the VBE? 25 Try It 30 LESSON 4: WORKING IN THE VBE 33 Toolbars in the VBE 33 Macros and Modules 33 Understanding the Code 36 Editing a Macro with Comments and Improvements to the Code 37 Try It 44 PART II: DIVING DEEPER INTO VBA LESSON 5: OBJECT-ORIENTED PROGRAMMING: AN OVERVIEW 49 What "Object-Oriented Programming" Means 49 The Object Model 50 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 Forcing Variable Declaration 59 Understanding a Variable's Scope 61 Try It 64 LESSON 7: UNDERSTANDING OBJECTS AND COLLECTIONS 67 Workbooks 67 Cells and Ranges 69 Try It 71 LESSON 8: WORKING WITH RANGES 75 Working with Noncontiguously Populated Ranges 77 Try It 82 LESSON 9: MAKING DECISIONS WITH VBA 85 Understanding Logical Operators 85 Choosing Between This or That 88 Getting Users to Make Decisions 92 Try It 94 PART III: BEYOND THE MACRO RECORDER: WRITING YOUR OWN CODE LESSON 10: REPEATING ACTIONS WITH LOOPS 101 What Is a Loop? 101 Nesting Loops 110 Try It 111 LESSON 11: PROGRAMMING FORMULAS 113 Understanding A1 and R1C1 References 113 Programming Your Formula Solutions with VBA 118 Try It 124 LESSON 12: WORKING WITH ARRAYS 127 What Is an Array? 127 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 13: AUTOMATING PROCEDURES WITH WORKSHEET EVENTS 137 What Is an Event? 137 Worksheet Events: An Overview 138 Examples of Common Worksheet Events 141 Try It 144 LESSON 14: AUTOMATING PROCEDURES WITH WORKBOOK EVENTS 149 Workbook Events: An Overview 149 Examples of Common Workbook Events 153 Try It 158 LESSON 15: HANDLING DUPLICATE ITEMS AND RECORDS 161 Deleting Rows Containing Duplicate Entries 161 Working with Duplicate Data 167 Try It 173 LESSON 16: USING EMBEDDED CONTROLS 181 Working with Form Controls and ActiveX Controls 181 Try It 191 LESSON 17: PROGRAMMING CHARTS 199 Try It 208 LESSON 18: PROGRAMMING PIVOTTABLES AND PIVOTCHARTS 213 Creating a PivotTable Report 213 Understanding PivotCaches 226 Manipulating PivotFields in VBA 230 Manipulating PivotItems with VBA 231 Creating a PivotTables Collection 231 Try It 232 LESSON 19: USER-DEFINED FUNCTIONS 237 What Is a User-Defi ned Function? 237 UDF Examples That Solve Common Tasks 239 Volatile Functions 243 Try It 248 LESSON 20: DEBUGGING YOUR CODE 251 What Is Debugging? 251 What Causes Errors? 252 Weapons of Mass Debugging 254 Trapping Errors 264 Try It 266 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 Try It 283 LESSON 22: USERFORM CONTROLS AND THEIR FUNCTIONS 285 Understanding the Frequently Used UserForm Controls 285 Try It 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 24: CLASS MODULES 321 What Is a Class? 321 What Is a Class Module? 322 Creating Your Own Objects 323 An Important Benefi t of Class Modules 323 Creating Collections 326 Class Modules for Embedded Objects 326 Try It 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 Closing Add-Ins 349 Removing an Add-In from the Add-Ins List 349 Try It 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 27: DATA ACCESS WITH ACTIVEX DATA OBJECTS 365 Introducing ADO 365 An Introduction to Structured Query Language (SQL) 368 Try It 371 LESSON 28: IMPRESSING YOUR BOSS (OR AT LEAST YOUR FRIENDS) 373 Selecting Cells and Ranges 373 Filtering Dates 376 Setting Page Breaks for Specifi ed Areas 379 Using a Comment to Log Changes in a Cell 380 Using the Windows API with VBA 381 Scheduling Your Workbook for Suicide 382 Try It 382 PART V: INTERACTING WITH OTHER OFFICE APPLICATIONS LESSON 29: OVERVIEW OF OFFICE AUTOMATION FROM EXCEL 391 Why Automate Another Application? 391 Understanding Offi ce Automation 392 Try It 395 LESSON 30: WORKING WITH WORD FROM EXCEL 399 Activating a Word Document 399 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 31: WORKING WITH OUTLOOK FROM EXCEL 409 Opening Outlook 409 Composing an E-mail in Outlook from Excel 410 Putting It All Together 413 E-mailing a Single Worksheet 415 Try It 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 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 INDEX 441