Mastering Excel 2003 Programming with VBA

Steven M. Hansen, Sybex9780782142815, 0782142818

Excel 2003 is an ideal application development platform for all levels of business needs. If you’re an advanced Excel user looking to gain programming experience, or a skilled developer new to Excel or the Excel Object Model, this no-nonsense book teaches you how to build custom applications that can generate substantial time and cost savings for you, your employer, and your customers. Written by a professional with nearly a decade of experience producing Excel/VBA solutions, Mastering Excel 2003 Programming with VBA conveys the precise knowledge and techniques you need to be highly productive. You’ll master the most critical Excel objects and development practices necessary to create a complete solution, including working with XML data, Smart Document technology, and database integration with ADO. And you can readily apply the practical advice and reusable code examples to your own projects. In his friendly style, author Steven Hansen makes the information easy to grasp and entertaining to read, and he does not shy away from challenging material. You’ll learn all about: * Ramping up with VBA * Debugging tools and tactics that work * Mastering the Excel Object Model * Developing class modules * Customizing Excel UserForms * Integrating Excel with other applications * Incorporating text files in your solution * Coding solutions that leverage a database * Using XML in Excel * Designing user-friendly toolbars and menus * Building Excel 2003 Smart Documents * Deploying your solution

---

Table of contents :
Team DDU……Page 1
Contents……Page 10
Introduction……Page 18
Part 1 Introduction to Excel Development……Page 22
Who Develops in Excel?……Page 24
What Is an Excel Application?……Page 25
A Short Survey of Excel Applications……Page 26
Millions of Potential Users……Page 27
Rapid Development Really……Page 28
Stop When You See Red……Page 29
Summary……Page 30
One Exceptional Editor……Page 32
Navigating Projects with the Project Explorer Window……Page 34
The Versatile Properties Window……Page 36
Adding New Components to Your Project……Page 37
Importing and Exporting Components……Page 38
Give Syntax Highlighting a Try……Page 39
Turn On All of the Code Settings……Page 40
Helpful Editor Features……Page 41
Understated Features of the Code Window……Page 43
Object Inspection Using the Object Browser……Page 45
Obtaining Help Has Never Been So Easy……Page 47
Securing Your Project……Page 50
Quick Code Navigation……Page 51
Summary……Page 52
Thinking Like a Computer……Page 54
Modules — A Home for Your Code……Page 56
Procedures……Page 57
Variables Are the Elements You Interact With……Page 61
Data Types……Page 62
Variable Scope and Lifetime……Page 66
Operators……Page 69
Implementing Loops……Page 70
Implementing Branching with If Then……Page 74
Thinking Like a Computer Revisited……Page 77
Choosing Actions with Select Case……Page 78
Basic Array Usage……Page 80
Specifying the Index Range of an Array……Page 81
Methods and Properties……Page 82
Summary……Page 83
Syntax Errors Are Your Friend……Page 86
Run-Time Embarrassments……Page 88
Logical Errors Cause Gray Hair……Page 89
Break Mode: For Fixing Things that Are Broken……Page 90
Reconnaissance Operations and Tools……Page 94
Zero In on Problem Areas with Watches……Page 96
Clarify the Muddle with the Call Stack……Page 99
Correcting Run-Time Errors……Page 100
Debugging Logical Errors……Page 102
Implementing Simple Error Handling……Page 103
Summary……Page 105
Part 2 Mastering the Excel Object Model……Page 106
A Bird’s-Eye View of the Application Object……Page 108
Gaining Performance and Polish with ScreenUpdating……Page 109
Keeping End Users Informed with the Status Bar……Page 111
Display-Oriented Features That Are Nice to Know……Page 114
Convenient Excel Object Properties……Page 116
Obtaining Filenames from a User……Page 117
Pick the Perfect Location with GetSaveAsFilename……Page 121
Inspecting Your Operating Environment……Page 126
Two Useful Bonus Members……Page 127
Summary……Page 128
Walk before You Run: Opening and Closing Workbooks……Page 130
Is That Workbook Open?……Page 135
Specifying Specific Collection Objects……Page 137
Untangle Links Programmatically (Part I)……Page 138
Plain Vanilla Workbook Properties……Page 144
Respond to User Actions with Events……Page 146
Summary……Page 151
Setting the Stage……Page 154
Validating Your Worksheets before Using Them……Page 157
Now You See It, Now You Don’t……Page 159
Lock Up Your Valuables……Page 161
Adding and Deleting Worksheets……Page 163
Moving and Copying Worksheets……Page 167
Expounding on Worksheet Events……Page 169
Summary……Page 171
Referring to Ranges……Page 174
Cells and Ranges with the Worksheet Object……Page 176
Referring to Named Ranges Can Be Tricky……Page 179
Finding My Way……Page 184
Offset Is for Relative Navigation……Page 185
Last but Not Least — Finding the End……Page 188
Input Easy; Output Easier……Page 192
Output Strategies……Page 193
Accepting Worksheet Input……Page 199
Summary……Page 200
Data Mobility with Cut, Copy, and Paste……Page 202
Find What You Are Seeking……Page 204
Don’t Like It? Change It with Replace……Page 210
Would You Like Special Sauce with That?……Page 214
CurrentRegion: A Useful Shortcut……Page 217
Sorting Lists Simplified……Page 220
Summary……Page 224
Coloring Your World……Page 226
Fiddling with Fonts……Page 229
Interior Decorating……Page 232
No Visa Required For These Borders……Page 235
Formatting Those Figures……Page 238
Chart Manipulation……Page 244
Creating a Chart from Scratch……Page 245
Chart Hunting……Page 249
Summary……Page 253
Part 3 Advanced Excel Programming Techniques……Page 254
Class Modules Are for Creating Objects……Page 256
A Linguistic Foundation……Page 257
Classes Unite……Page 258
Creating a Simple Object……Page 259
Using Your Object……Page 261
A Better, Smarter Object……Page 263
An Object Showdown……Page 267
Collecting Your Objects……Page 269
Implementing More Accurate Properties……Page 273
Summary……Page 279
The Need for Persistence……Page 280
Class Semi-Specification……Page 281
Planning the Plumbing……Page 282
The Setting Class……Page 283
Fake Is Fine……Page 291
Collect Those Setting Objects with Settings……Page 292
Pseudo? Says Who?……Page 297
Put Those Settings to Work……Page 298
Summary……Page 300
Deactivate Activate; Don’t Select Select……Page 302
Manage the Display……Page 305
Design for Portability……Page 306
Remember Your Math……Page 307
Think Like an Environmentalist……Page 308
Use Syntax Highlighting……Page 309
Manage Literal Values Centrally……Page 310
Smart Workbook Design……Page 316
Summary……Page 319
Part 4 Working with External Data……Page 320
A Primer to Office Automation……Page 322
Expounding On Compound Documents……Page 324
Crafting Compound Documents Programmatically……Page 326
OLE Is Great; Automation Is Better……Page 329
Binding to a Class Library……Page 330
An Automation Example: Presentation Automation……Page 333
Summary……Page 340
Versatile Simplicity……Page 342
Opening Text Files in Excel……Page 344
Importing Text Data onto a Worksheet……Page 347
Automatic Text Files……Page 349
The Old Standby Copy/Paste……Page 355
Open for Business……Page 358
File I/O……Page 360
String Functions Offering Utility……Page 365
Summary……Page 368
Database Basics……Page 370
Developing Your Skills……Page 372
Native Excel Database Integration……Page 373
Excel, Meet My Database Database, This Is Excel……Page 374
You Are an Advanced Player……Page 376
Turbo Charge Your Data Range……Page 382
Work Magic with ADO……Page 384
Make the Connection……Page 386
Ready, Set, Query!……Page 388
It’t Not Just about Retrieving……Page 392
I Like Treats……Page 395
Summary……Page 401
Y XML?……Page 404
Easy XL XML……Page 407
XL XML Maps……Page 409
4X3M2L3……Page 414
XML VBA Style……Page 415
A List Object Primer……Page 424
Summary……Page 429
Part 5 Enhancing the End User Experience……Page 430
User Interfaces in Excel……Page 432
Paleozoic Controls……Page 433
The Ubiquitous Button……Page 434
Free to Choose……Page 436
Makin?a List……Page 440
Scrolling and Spinning……Page 442
Like a Kid in a Candy Store……Page 444
The Ubiquitous Button Redux……Page 445
Combo Box Capabilities……Page 448
Judge a Book by Its Cover……Page 450
Summary……Page 453
Taking Inventory with CommandBars……Page 456
Reflecting on a CommandBar……Page 460
The Bendy CommandBarControl Object……Page 467
Finding Controls……Page 469
Crafting Custom Command Bars……Page 471
Building a New Command Bar……Page 480
Summary……Page 483
User Form Development Features……Page 484
A Simple Form Walkthrough……Page 486
Show First, Ask Questions Later……Page 490
Load and Show……Page 491
Classy Forms……Page 492
The Form Lifecycle……Page 495
User Friendly Settings……Page 502
Primitive Password Collection……Page 510
Summary……Page 512
Smart Document Basics……Page 514
Being Smart Has Benefits……Page 515
Key Components of Smart Document……Page 517
Smart Document Security……Page 518
A Not So Smart Document (with Lots of Potential)……Page 519
XML Helps Make You Smart……Page 520
Implementing ISmartDocument Makes You Smarter……Page 522
Connect the Dots with a Manifest……Page 544
See How Smart It Is……Page 545
Troubleshooting Smart Documents……Page 546
Summary……Page 547
Choose the Form……Page 550
Repeat Your Success with Templates……Page 551
Blend in with Add-Ins……Page 556
Employing Centralized Template Deployment……Page 560
Implementing Version Awareness……Page 562
Fear Not When Things Go Wrong……Page 571
Summary……Page 575
Index……Page 578