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 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