About the Authors v
Acknowledgments ix
Introduction xxiii
Chapter 1: Primer in Excel VBA 1
Using the Macro Recorder 2
Recording Macros 2
Running Macros 5
The Visual Basic Editor 8
Other Ways to Run Macros 11
User Defined Functions 18
Creating a UDF 18
What UDFs Cannot Do 22
The Excel Object Model 22
Objects 23
Getting Help 29
Experimenting in the Immediate Window 30
The VBA Language 32
Basic Input and Output 32
Calling Functions and Subroutines 37
Variable Declaration 38
Scope and Lifetime of Variables 40
Variable Type 42
Object Variables 45
Making Decisions 47
Looping 50
Arrays 55
Runtime Error Handling 58
Summary 62
Chapter 2: Programming in the VBE 63
Writing Code 63
Programming for People 63
Writing Code 64
Where Does My Code Go? 65
Contents
Managing a Project 65
Adding Classes 67
Modifying Properties 68
Importing and Exporting Visual Basic Code 69
Editing 70
Managing Editor Options 70
Running and Debugging Code 71
Using Watches 71
Using the Object Browser 72
Summary 73
Chapter 3: The Application Object 75
Globals 75
The Active Properties 76
Display Alerts 77
Screen Updating 77
Evaluate 78
InputBox 80
StatusBar 81
SendKeys 82
OnTime 83
OnKey 84
Worksheet Functions 85
Caller 87
Summary 88
Chapter 4: Object-Oriented Theory and VBA 89
Comparing Classes and Interfaces 89
Defining an Interface 90
Implementing an Interface 91
Defining Methods 92
Parameters 93
Implementing Recursive Methods 94
Eliminating Recursion with Loops 94
Defining Fields 95
Defining Properties 95
Read-Only Properties 96
Write-Only Properties 97
Defining Events 97
Defining Events in Classes 97
xii
Contents
Raising Events 98
Handling Events 99
Information Hiding and Access Modifiers 100
Encapsulation, Aggregation, and References 100
Summary 101
Chapter 5: Event Procedures 103
Worksheet Events 103
Enable Events 104
Worksheet Calculate 105
Chart Events 106
Before Double Click 106
Workbook Events 108
Save Changes 110
Headers and Footers 111
Summary 112
Chapter 6: Class Modules 113
Creating Your Own Objects 113
Using Collections 115
Class Module Collection 116
Trapping Application Events 118
Embedded Chart Events 120
A Collection of UserForm Controls 122
Referencing Classes Across Projects 124
Summary 125
Chapter 7: Writing Bulletproof Code 127
Using Debug.Print 127
Using Debug.Assert 128
A Brief Exemplar of PC Debugging 129
Creating Reusable Tools with the Debug Object 133
Tracing Code Execution 133
Trapping Code Execution Paths 135
Asserting Application Invariants 137
Raising Errors 140
Writing Error Handlers 142
On Error Goto Line Number 142
On Error Resume Next 143
xiii
Contents
On Error GoTo 0 145
Using the Err Object 145
Scaffolding 145
Writing to the EventLog 147
Summary 149
Chapter 8: Debugging and Testing 151
Stepping Through Code 151
Running Your Code 152
Stepping into Your Code 153
Step Over 153
Step Out 153
Run to Cursor 154
Set Next Statement 154
Show Next Statement 155
Using Breakpoints 155
Using Watches 155
Add Watch 156
Edit Watch 158
Quick Watch 158
Locals Windows 158
Testing an Expression in the Immediate Window 159
Resources for Finding Definitions 160
Edit ➪Quick Info 160
Edit ➪Parameter Info 161
Edit ➪Complete Word 161
Edit ➪List Properties/Methods 161
Edit ➪List Constants 162
Edit ➪Bookmarks 162
View ➪Definition 162
View ➪Object Browser 163
Viewing the Call Stack 163
Asserting Application Invariants 164
Summary 165
Chapter 9: UserForms 167
Displaying a UserForm 167
Creating a UserForm 169
Directly Accessing Controls in UserForms 171
Stopping the Close Button 174
xiv
Contents
Maintaining a Data List 175
Modeless UserForms 181
Summary 181
Chapter 10: Adding Controls 183
The Toolbars 183
ActiveX Controls 184
Scrollbar Control 185
Spin Button Control 186
CheckBox Control 186
Option Button Controls 187
Forms Toolbar Controls 188
Dynamic ActiveX Controls 191
Controls on Charts 194
Summary 195
Chapter 11: Data Access with ADO 197
An Introduction to Structured Query Language (SQL) 197
The SELECT Statement 198
The INSERT Statement 200
The UPDATE Statement 201
The CREATE TABLE Statement 202
The DROP TABLE Statement 203
An Overview of ADO 203
The Connection Object 204
The Recordset Object 212
The Command Class 218
Using ADO in Microsoft Excel Applications 222
Using ADO with Microsoft Access 223
Using ADO with Microsoft SQL Server 230
Using ADO with Non-Standard Data Sources 239
Summary 244
Chapter 12: Creating and Using Add-ins 245
Hiding the Code 245
Converting the Workbook to an Add-in 247
Closing Add-ins 247
Code Changes 248
Saving Changes 249
xv
Contents
Installing an Add-in 250
Add-in Install Event 251
Removing an Add-in from the Add-ins List 252
Summary 252
Chapter 13: Automation Addins and COM Addins 253
Automation Addins 253
Creating a Simple Addin 254
Registering Automation Addins with Excel 255
Using Automation Addins 257
An Introduction to the IDTExtensibility2 Interface 259
A Complex Addin—Generating a Unique Random Number 262
COM Addins 268
IDTExtensibility2 Interface Continued 268
Summary 279
Chapter 14: Customizing the VBE 281
Identifying VBE Objects in Code 281
The VBE Object 282
The VBProject Object 283
The VBComponent Object 283
The CodeModule Object 284
The CodePane Object 285
The Designer Object 285
Starting Up 285
Adding Menu Items to the VBE 286
Table-Driven Menu Creation 288
Displaying Built-In Dialogs, UserForms, and Messages 296
Working with Code 301
Working with UserForms 305
Working with References 310
Summary 311
Chapter 15: Interacting with Other Office Applications 313
Establishing the Connection 314
Late Binding 314
Early Binding 315
Opening a Document in Word 317
Accessing an Active Word Document 318
Creating a New Word Document 319
xvi
Contents
Access and DAO 320
Access, Excel and, Outlook 321
When Is a Virus not a Virus? 323
Summary 324
Chapter 16: Programming with the Windows API 327
Anatomy of an API Call 328
Interpreting C-Style Declarations 329
Constants, Structures, Handles, and Classes 332
What if Something Goes Wrong? 335
Wrapping API Calls in Class Modules 336
Some Example Classes 341
A High-Resolution Timer Class 341
Freeze a UserForm 342
A System Info Class 344
Modifying UserForm Styles 346
Resizable Userforms 350
Other Examples 356
Summary 358
Chapter 17: International Issues 359
Changing Windows Regional Settings and the Office XP UI
Language 359
Responding to Regional Settings and the Windows Language 360
Identifying the User’s Regional Settings and
Windows Language 360
VBA Conversion Functions from an International Perspective 361
Interacting with Excel 366
Sending Data to Excel 367
Reading Data from Excel 369
Rules for Working with Excel 370
Interacting with Users 370
The Rules for Working with Your Users 373
Excel 2003’s International Options 373
Features That Don’t Play by the Rules 375
Responding to Office XP Language Settings 382
Where Does the Text Come From? 382
Identifying the Office UI Language Settings 383
Creating a Multilingual Application 384
Working in a Multilingual Environment 386
The Rules for Developing a Multilingual Application 388
xvii
Contents
Some Helpful Functions 388
Implementing WinToNum Function 388
Implementing WinToDate Function 389
Implementing FormatDate Function 390
Implementing ReplaceHolders Function 390
Summary 391
Chapter 18: Workbooks and Worksheets 393
Using the Workbooks Collection 393
Creating a New Workbook 393
Saving the ActiveWorkbook 394
Activating a Workbook 394
Getting a FileName from a Path 395
Files in the Same Directory 397
Overwriting an Existing Workbook 398
Saving Changes 399
The Sheets Collection 400
Worksheets 400
Copy and Move 402
Grouping Worksheets 403
The Window Object 405
Synchronizing Worksheets 406
Summary 407
Chapter 19: Using Ranges 409
Activate and Select 409
Range Property 411
Shortcut Range References 412
Ranges on Inactive Worksheets 412
Range Property of a Range Object 413
Cells Property 413
Cells used in Range 414
Ranges of Inactive Worksheets 414
More on the Cells Property of the Range Object 415
Single-Parameter Range Reference 417
Offset Property 418
Resize Property 420
SpecialCells Method 420
CurrentRegion Property 424
xviii
Contents
End Property 426
Referring to Ranges with End 426
Summing a Range 427
Columns and Rows Properties 428
Areas 429
Union and Intersect Methods 431
Empty Cells 432
Transferring Values between Arrays and Ranges 434
Deleting Rows 436
Summary 438
Chapter 20: Using Names 441
Naming Ranges 442
Using the Name Property of the Range Object 443
Special Names 443
Storing Values in Names 444
Storing Arrays 445
Hiding Names 446
Working with Named Ranges 446
Searching for a Name 447
Searching for the Name of a Range 449
Determining which Names Overlap a Range 450
Summary 452
Chapter 21: Working with Lists 453
Creating a List 453
Shortcut Options for Lists 454
Sorting and Filtering a List 454
Creating a UserForm from a List 455
Resizing Lists 456
Dragging the resize handle in the bottom corner of the list 456
Totaling Rows 456
Converting Lists to a Range 456
Publishing Lists 457
Publishing Your List 459
Updating Changes to Your List 459
View a List on a SharePoint Server 460
Unlinking the List 461
Summary 461
xix
Contents
Chapter 22: PivotTables 463
Creating a PivotTable Report 464
PivotCaches 467
PivotTables Collection 467
PivotFields 467
CalculatedFields 470
PivotItems 473
Grouping 473
Visible Property 476
CalculatedItems 477
PivotCharts 478
External Data Sources 479
Summary 481
Chapter 23: Filtered Lists 483
Structuring the Data 483
Data Form 483
AutoFilter 485
Custom AutoFilter 485
Adding Combo Boxes 486
Copying the Visible Rows 490
Finding the Visible Rows 491
Advanced Filter 493
Summary 495
Chapter 24: Generating Charts 497
Chart Sheets 497
Embedded Charts 500
Editing Data Series 503
Defining Chart Series with Arrays 506
Converting a Chart to use Arrays 509
Determining the Ranges used in a Chart 509
Chart Labels 511
Summary 512
Chapter 25: Office Files and Folders 515
FileSearch 516
FoundFiles 518
PropertyTests 519
xx
Contents
FileTypes 520
SearchScopes 521
ScopeFolder 522
SearchFolders 523
FileDialog 525
FileDialogFilters 527
FileDialogSelectedItems 527
Dialog Types 527
Execute Method 528
MultiSelect 528
Summary 529
Chapter 26: Command Bars 531
Toolbars, Menu Bars, and Popups 531
Excel’s Built-In Commandbars 534
Controls at All Levels 537
FaceIds 540
Creating New Menus 542
The OnAction Macros 544
Passing Parameter Values 545
Deleting a Menu 545
Creating a Toolbar 546
Pop-Up Menus 551
Showing Pop-Up Command Bars 554
Disabling Commandbars 556
Disabling Shortcut Access to Customize 558
Table-Driven Command Bar Creation 559
Summary 569
Chapter 27: SmartTags 571
SmartTag Enhancements 572
Microsoft SmartTags 2.0 Type Library 573
The FileName SmartTag 574
Anatomy of a SmartTag 574
The SmartTag Unique Identifier 575
The SmartTag Recognizer Class 576
The SmartTag Actions Class 580
Implementing Office 2003 SmartTag Features 586
Registering SmartTags 589
Using the FileName SmartTag 591
Controlling SmartTags with VBA 592
xxi
Contents
The Problems with SmartTags 595
Summary 596
Chapter 28: Excel and the Internet 597
So What’s all the Hype About? 598
Using the Internet for Storing Workbooks 598
Using the Internet as a Data Source 599
Opening Web Pages as Workbooks 599
Using Web Queries 600
Parsing Web Pages for Specific Information 603
Using the Internet to Publish Results 605
Setting Up a Web Server 605
Saving Worksheets as Web Pages 605
Adding Interactivity with the Web Components 607
Using the Internet as a Communication Channel 608
Communicating with a Web Server 609
XML 618
The XML-SS Schema 620
Using XSLT to Transform XML 624
Summary 628
Chapter 29: XML and Excel 629
What Is XML? 629
What Is XSD? 630
What Is XMLSS? 631
Importing XML Data 633
BlackJack: Data Versatility 633
Importing an XML File 635
Exporting a Worksheet to an XML File 637
Summary 638
Appendix A: Excel 2003 Object Model 641
Appendix B: VBE Object Model 961
Appendix C: VBE Object Model 991
Index 1081
xxii