Excel VBA 24-Hour Trainer

Introduction xxvii

Section I: Understanding the BASI Cs

Lesson 1: Introduc ing 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 5

Simplifying the Workbook’s Look and Feel for Other Users 5

Controlling Other Office Applications from Excel 6

Liabilities of VBA 7

Try It 8

Lesson 2: Getting Started with Macros 9

Composing Your First Macro 9

Accessing the VBA Environment 9

Using the Macro Recorder 12

Running a Macro 16

The Macro Dialog Box 16

Shortcut Key 17

Try It 17

Lesson Requirements 18

Step-by-Step 18

Lesson 3: Introduc ing the Visual Basic Editor 19

What Is the VBE? 19

How To Get Into the VBE 20

Understanding the VBE 20

The Project Explorer Window 21

The Code Window 21

The Properties Window 22

The Immediate Window 22

Understanding Modules 22

Using the Object Browser 23

Exiting the VBE 24

Try It 25

Lesson 4: Working in the VBE 27

Toolbars in the VBE 27

Macros and Modules 28

Locating Your Macros 28

Understanding the Code 29

Editing a Macro with Comments and Improvements to the Code 30

Deleting a Macro 33

Inserting a Module 33

Renaming a Module 34

Deleting a Module 36

Locking and Protecting the VBE 36

Try It 37

Lesson Requirements 37

Step-by-Step 37

Section II : Diving Deeper into VBA

Lesson 5: Ob ject-oriented Programm ing — A n Ov erview 43

What “Object-Oriented Programming” Means 43

The Object Model 44

Properties 45

Methods 46

Collections 46

Try It 47

Lesson 6: Variabl es, Data Types, and Constants 49

What Is a Variable? 49

Assigning Values to Variables 50

Why You Need Variables 50

Data Types 51

Understanding the Different Data Types 51

Declaring a Variable for Dates and Times 53

Declaring a Variable with the Proper Data Type 53

Forcing Variable Declaration 54

Understanding a Variable’s Scope 56

Local Macro Level Only 56

Module Level 56

Application Level 57

Constants 57

Choosing the Scope and Lifetime of Your Constants 58

Try It 58

Lesson Requirements 58

Step-by-Step 58

Understanding Lesson 7: Ob jects and Coll ections 61

Workbooks 61

Worksheets 62

Cells and Ranges 63

SpecialCells 64

Try It 65

Lesson Requirements 65

Step-by-Step 65

Lesson 8: Making Decisions with VBA 69

Understanding Logical Operators 69

AND 70

OR 70

NOT 71

Choosing Between This or That 72

If…Then 72

If…Then…Else 73

If…Then…ElseIf 74

Select Case 74

Getting Users to Make Decisions 76

Message Boxes 76

Input Boxes 77

Try It 78

Lesson Requirements 78

Step-by-Step 78

Section III : the macro recorder:

writing your Own Code

Lesson 9: Repeating Ac tions with Loops 85

What Is a Loop? 85

Types of Loops 86

For…Next 87

For…Each…Next 88

Exiting a For… Loop 89

Looping In Reverse with Step 90

Do…While 91

Do…Until 91

Do…Loop…While 93

Do…Loop…Until 94

While…Wend 94

Nesting Loops 94

Try It 95

Lesson Requirements 96

Step-by-Step 96

Lesson 10: Working with Arrays 99

What Is an Array? 99

What Arrays Can Do for You 101

Declaring Arrays 102

The Option Base Statement 103

Boundaries in Arrays 104

Declaring Arrays with Fixed Elements 104

Declaring Dynamic Arrays with ReDim and Preserve 105

Try It 107

Lesson Requirements 107

Step-by-Step 107

Lesson 11: Au tomating Procedures with Worksheet Ev ents 111

What Is an “Event”? 111

Worksheet Events — an Overview 112

Where Does the Worksheet Event Code Go? 112

Enabling and Disabling Events 114

Examples of Common Worksheet Events 115

Worksheet_Change Event 115

Worksheet_SelectionChange Event 116

Worksheet_BeforeDoubleClick Event 116

Worksheet_BeforeRightClick Event 117

Worksheet_FollowHyperlink Event 117

Worksheet_Activate Event 117

Worksheet_Deactivate Event 118

Worksheet_Calculate Event 118

Worksheet_PivotTableUpdate Event 119

Try It 119

Lesson Requirements 119

Step-by-Step 119

Au tomating Procedures w Lesson 12: ith Workbook Ev ents 123

Workbook Events — An Overview 123

Where Does the Workbook Event Code Go? 123

Entering Workbook Event Code 125

Examples of Common Workbook Events 126

Workbook_Open Event 126

Workbook_BeforeClose Event 127

Workbook_Activate Event 127

Workbook_Deactivate Event 128

Workbook_SheetChange Event 128

Workbook_SheetSelectionChange Event 128

Workbook_SheetBeforeDoubleClick Event 129

Workbook_SheetBeforeRightClick Event 129

Workbook_SheetPivotTableUpdate Event 130

Workbook_NewSheet Event 130

Workbook_BeforePrint Event 130

Workbook_SheetActivate Event 131

Workbook_SheetDeactivate Event 131

Workbook_BeforeSave Event 131

Try It 132

Lesson Requirements 132

Step-by-Step 132

Lesson 13: Emb edded Controls 135

Working with Forms Controls and ActiveX Controls 135

The Forms Toolbar 136

The Control Toolbox 140

Try It 144

Lesson Requirements 144

Step-by-Step 144

Lesson 14: Programm ing Charts 151

Adding a Chart to a Chart Sheet 152

Adding an Embedded Chart to a Worksheet 154

Moving a Chart 155

Looping Through All Embedded Charts 157

Deleting Charts 158

Renaming a Chart 159

Try It 160

Lesson Requirements 160

Step-by-Step 160

Lesson 15: Programm ing PivotTabl es and PivotCharts 163

Creating a PivotTable Report 163

Hiding the PivotTable Field List 167

Using the Report Filter Area 167

Formatting Numbers in the Values Area 168

Why It’s Called a PivotTable 170

Creating a PivotChart 171

Understanding PivotCaches 173

Manipulating PivotFields in VBA 176

Manipulating PivotItems with VBA 177

Creating a PivotTables Collection 177

Try It 178

Lesson Requirements 178

Step-by-Step 179

Lesson 16: User Defined Fu nctions 183

What Is a User Defined Function? 183

Characteristics of User Defined Functions 184

Anatomy of a UDF 184

UDF Examples That Solve Common Tasks 185

Volatile Functions 188

The Name of the Active Worksheet and Workbook 189

UDFs with Conditional Formatting 190

Calling Your Function from a Macro 190

Adding a Description to the Insert Function Dialog 191

Try It 193

Lesson Requirements 193

Step-by-Step 193

Lesson 17: Debu gging Your Code 195

What Is Debugging? 195

What Causes Errors? 196

Weapons of Mass Debugging 198

The Debugging Toolbar 198

Trapping Errors 207

Error Handler 207

Bypassing Errors 208

Try It 210

Lesson Requirements 210

Step-by-Step 210

Section IV: Advanced Programm ing Techniques

Lesson 18: Creating UserForms 215

What Is a UserForm? 215

Creating a UserForm 216

Designing a UserForm 218

Showing a UserForm 225

Where Does the UserForm’s Code Go? 225

Closing a UserForm 226

Unloading a UserForm 226

Hiding a UserForm 227

Try It 228

Lesson Requirements 228

Step-by-Step 228

Lesson 19: nd Their Fu nctions 231

Understanding the Frequently Used UserForm Controls 231

CommandButtons 232

Labels 232

TextBoxes 234

ListBoxes 236

ComboBoxes 238

CheckBoxes 240

OptionButtons 241

Frames 243

MultiPages 245

Try It 246

Lesson Requirements 246

Step-by-Step 246

Lesson 20: Advanced UserForms 249

The UserForm Toolbar 249

Modal versus Modeless 250

Disabling the UserForm’s Close Button 250

Maximizing Your UserForm’s Size 252

Selecting and Displaying Photographs on a UserForm 252

Unloading a UserForm Automatically 253

Pre-Sorting the ListBox and ComboBox Items 253

Populating ListBoxes and ComboBoxes with Unique Items 255

Display a Real-Time Chart in a UserForm 258

Try It 259

Lesson Requirements 259

Step-by-Step 259

Lesson 21: Class Modul es 263

What Is a Class? 263

What Is a Class Module? 264

Creating Your Own Objects 265

An Important Benefit of Class Modules 266

Creating Collections 268

Class Modules for Embedded Objects 269

Try It 272

Lesson Requirements 272

Step-by-Step 272

Lesson 22: Add-Ins 279

What Is an Excel Add-In? 279

Creating an Add-In 280

Converting a File to an Add-In 284

Installing an Add-In 286

Creating a User Interface for Your Add-In 288

Changing the Add-In’s Code 290

Closing Add-Ins 290

Removing an Add-In from the Add-Ins List 291

Try It 291

Lesson Requirements 291

Step-by-Step 291

Lesson 23: Managing External Data 295

Creating QueryTables from Web Queries 295

Creating a QueryTable for Access 299

Using Text Files to Store External Data 301

Try It 304

Lesson Requirements 304

Step-by-Step 304

Lesson 24: Data Acc ess with Ac tiveX Data Ob jects 307

Introducing ADO 307

The Connection Object 309

The Recordset Object 309

The Command Object 310

An Introduction to Structured Query Language (SQL) 310

The SELECT Statement 311

The INSERT Statement 311

The UPDATE Statement 312

The DELETE Statement 312

Try It 313

Lesson 25: Not Gone, Not Forgotten 315

Using Dialog Sheets 315

What Does a Dialog Sheet Look Like? 316

Option to Show Message Only Once 318

Using XLM Get.Cell Functions 321

Using the SendKeys Method 322

Try It 323

Lesson Requirements 323

Step-by-Step 323

Interacting with Other Section V: Office Applications

Lesson 26: Ov erview of Office Au tomation from Excel 327

Why Automate Another Application? 327

Understanding Office Automation 328

Early Binding 328

Late Binding 329

Which One Is Better? 330

Try It 330

Lesson Requirements 330

Step-by-Step 330

Lesson 27: Working with Word from Excel 333

Activating a Word Document 333

Activating the Word Application 334

Opening and Activating a Word Document 334

Creating a New Word Document 336

Copying an Excel Range to a Word Document 337

Printing a Word Document from Excel 337

Importing a Word Document to Excel 338

Try It 339

Lesson Requirements 339

Step-by-Step 339

Lesson 28: Working with Ou tlook from Excel 343

Opening Outlook 343

Composing an E-mail in Outlook from Excel 344

Creating a MailItem Object 344

Transferring an Excel Range to the Body of Your E-mail 345

Putting It All Together 346

E-mailing a Single Worksheet 348

Try It 348

Lesson Requirements 348

Step-by-Step 348

Lesson 29: ith Acc ess from Excel 353

Adding a Record to an Access Table 353

Exporting an Access Table to an Excel Spreadsheet 356

Creating a New Table in Access 358

Try It 359

Lesson Requirements 359

Step-by-Step 360

Lesson 30: Working with PowerPoint from Excel 363

Creating a New PowerPoint Presentation 363

Copying a Worksheet Range to a PowerPoint Slide 364

Copying Chart Sheets to PowerPoint Slides 365

Running a PowerPoint Presentation from Excel 367

Try It 368

Lesson Requirements 368

Step-by-Step 368

Appendix: What’s on the DVD? 371

Index 375