Program Excel''s newest chart and pivot table object models
Table of Contents
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
