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