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