Contents

Introduction xvii

Part 1 • Introduction to Excel Development 1

Chapter 1 • Excel as a Development Platform 3

Who Develops in Excel? 3

What Is an Excel Application? 4

A Short Survey of Excel Applications 5

Why Use Excel? 6

Extend a Great Product 6

Millions of Potential Users 6

Exploit the Knowledge Base 7

Rapid Development. Really 7

It's Not Rocket Science 8

Stop When You See Red 8

A Reflection on the Learning Curve 9

Summary 9

Chapter 2 • Getting to Know Your Environment 11

One Exceptional Editor 11

Navigating Projects with the Project Explorer Window 13

The Versatile Properties Window 15

Capabilities of the Immediate Window 16

Managing Your Modules 16

Adding New Components to Your Project 16

Removing Components from Your Project 17

Importing and Exporting Components 17

Optimizing Your Editor 18

Give Syntax Highlighting a Try 18

Turn On All of the Code Settings 19

Helpful Editor Features 20

Understated Features of the Code Window 22

Object Inspection Using the Object Browser 24

Locating Objects with the Object Browser 26

Obtaining Help Has Never Been So Easy 26

Securing Your Project 29

Quick Code Navigation 30

Summary 31

Chapter 3 • Getting Started with VBA 33

Thinking Like a Computer 33

VBA Building Blocks 35

Modules—A Home for Your Code 35

Procedures 36

Variables Are the Elements You Interact With 40

Data Types 41

Declaring Variables 45

Variable Scope and Lifetime 45

Constants 48

Operators 48

Directing Your Program with Statements 49

Implementing Loops 49

Implementing Branching with If.. .Then 53

Thinking Like a Computer Revisited 56

Choosing Actions with Select Case 57

Basic Array Usage 59

Specifying the Index Range of an Array 60

Objects—A Human-Friendly Way to Think of Bits and Bytes 61

Methods and Properties 61

Summary 62

Chapter 4 • Debugging Tactics that Work 65

A Bug Epidemic 65

Syntax Errors Are Your Friend 65

Run-Time Embarrassments 67

Logical Errors Cause Gray Hair 68

Debugging Weapons in the VBE 69

Break Mode: For Fixing Things that Are Broken 69

Reconnaissance Operations and Tools 73

Zero In on Problem Areas with Watches 75

Clarify the Muddle with the Call Stack 78

A Simple Debugging Methodology 79

Correcting Run-Time Errors 79

Debugging Logical Errors 81

Implementing Simple Error Handling 82

Summary 84

Part 2 • Mastering the Excel Object Model 85

Chapter 5 • Exploring the Application Object 87

A Bird's-Eye View of the Application Object 87

Display-Oriented Features You Have to Know 88

Gaining Performance and Polish with ScreenUpdating 88

Keeping End Users Informed with the Status Bar 90

Display-Oriented Features That Are Nice to Know 93

Convenient Excel Object Properties 95

Common File Operations Simplified 96

Obtaining Filenames from a User 96

Pick the Perfect Location with GetSaveAsFilename 100

Inspecting Your Operating Environment 105

Two Useful Bonus Members 106

Summary 107

Chapter 6 • Working with the Workbook Object 109

Walk before You Run: Opening and Closing Workbooks 109

Is That Workbook Open? 114

Specifying Specific Collection Objects 116

Untangle Links Programmatically (Part I) 117

Plain Vanilla Workbook Properties 123

Respond to User Actions with Events 125

Summary 130

Chapter 7 • Winning with Worksheets 133

Setting the Stage 133

Validating Your Worksheets before Using Them 136

Now You See It, Now You Don't 138

Lock Up Your Valuables 140

Managing Workbook Worksheets 142

Adding and Deleting Worksheets 142

Moving and Copying Worksheets 146

Expounding on Worksheet Events 148

Summary 150

Chapter 8 • The Most Important Object 153

Referring to Ranges 153

Cells and Ranges with the Worksheet Object 155

Referring to Named Ranges Can Be Tricky 158

Finding My Way 163

Offset Is for Relative Navigation 164

Last but Not Least—Finding the End 167

Input Easy; Output Easier 171

Output Strategies 172

Accepting Worksheet Input 178

Summary 179

Chapter 9 • Practical Range Operations 181

Data Mobility with Cut, Copy, and Paste 181

Find What You Are Seeking 183

Don't Like It? Change It with Replace 189

Would You Like Special Sauce with That? 193

CurrentRegion: A Useful Shortcut 196

Sorting Lists Simplified 199

Summary 203

Chapter 10 • Exploring Other Excel Objects 205

Make a Good First Impression 205

Coloring Your World 205

Fiddling with Fonts 208

Interior Decorating 211

No Visa Required For These Borders 214

Formatting Those Figures 217

Chart Manipulation 223

Creating a Chart from Scratch 224

Chart Hunting 228

Summary 232

Part 3 • Advanced Excel Programming Techniques 233

Chapter 11 • Developing Class Modules 235

Class Modules Are for Creating Objects 235

A Linguistic Foundation 236

What's the Big Deal? Why Use Objects? 237

Classes Unite 237

Classes Hide the Details 238

Classes Enable Collaboration 238

Creating a Simple Object 238

Using Your Object 240

A Better, Smarter Object 242

An Object Showdown 246

Collecting Your Objects 248

Implementing More Accurate Properties 252

Summary 258

Chapter 12 • Adding User Personalization to Your Application 259

The Need for Persistence 259

Class Semi-Specification 260

Planning the Plumbing 261

Security Considerations 262

The Setting Class 262

Fake Is Fine 270

Collect Those Setting Objects with Settings 271

Pseudo? Says Who? 276

Put Those Settings to Work 277

Summary 279

Chapter 13 • Excel Development Best Practices 281

Deactivate Activate; Don't Select Select 281

Manage the Display 284

Design for Portability 285

Tips for Creating Portable Applications 286

Test the Water before Jumping In 286

Remember Your Math 286

Think Like an Environmentalist 287

CONTENTS| xlll

Use Literal Values with Care 288

Use Syntax Highlighting 288

Manage Literal Values Centrally 289

Smart Workbook Design 295

Summary 298

Part 4 • Working with External Data 299

Chapter 14 • Integrating with Other Applications 301

A Primer to Office Automation 301

Expounding On Compound Documents 303

Crafting Compound Documents Programmatically 305

OLE Is Great; Automation Is Better 308

Binding to a Class Library 309

An Automation Example: Presentation Automation 312

Summary 319

Chapter 15 • Incorporating Text Files in Your Solution 321

Versatile Simplicity 321

Opening Text Files in Excel 323

Importing Text Data onto a Worksheet 326

Automatic Text Files 328

The Old Standby—Copy/Paste 334

Opening Files Under the Covers 337

Open for Business 337

File I/O 339

String Functions Offering Utility 344

Summary 347

Chapter 16 • Dealing with Databases 349

Database Basics 349

Developing Your Skills 351

Native Excel Database Integration 352

Excel, Meet My Database. Database, This Is Excel 353

You Are an Advanced Player 355

Turbo Charge Your Data Range 361

Work Magic with ADO 363

Make the Connection 365

It's Not Just about Retrieving 371

I Like Treats 374

Summary 380

Y XML? 383

XML in XL 386

Easy XL XML 386

XL XML Maps 388

4X3M2L3 393

XML VBA Style 394

A List Object Primer 403

Summary 408

Part 5 • Enhancing the End User Experience 409

Chapter 18 • Basic User Interfaces 411

User Interfaces in Excel 411

Paleozoic Controls 412

The Ubiquitous Button 413

Free to Choose 415

Makin' a List 419

Scrolling and Spinning 421

Like a Kid in a Candy Store 423

The Ubiquitous Button Redux 424

Combo Box Capabilities 427

Judge a Book by Its Cover 429

Summary 432

Chapter 19 • Taking Control of Command Bars 435

Taking Inventory with CommandBars 435

Reflecting on a CommandBar 439

The Bendy CommandBarControl Object 446

Visualization, Prioritization, and CommandBarControls 448

Finding Controls 448

Crafting Custom Command Bars 450

Building a New Command Bar 459

Summary 462

Chapter 20 • User Form Construction 463

User Form Development Features 463

A Simple Form Walkthrough 465

Forms Are Meant to Be Shown 469

Show First, Ask Questions Later 469

Load and Show 470

Classy Forms 471

The Form Lifecycle 474

User Friendly Settings 481

Primitive Password Collection 489

Summary 491

Chapter 21 • One Smart Cookie: Smart Documents with Excel 2003 493

Smart Document Basics 493

Being Smart Has Benefits 494

A Smart Prerequisite 496

CONTENTS| xv

Key Components of Smart Document 496

Smart Document Security 497

A Smart Document Walkthrough 498

A Not So Smart Document (with Lots of Potential) 498

XML Helps Make You Smart 499

Implementing ISmartDocument Makes You Smarter 501

Connect the Dots with a Manifest 523

See How Smart It Is 524

Troubleshooting Smart Documents 525

Smart Document Resources 526

Summary 526

Chapter 22 • Application Deployment Strategies 529

Choose the Form 529

I Like Vanilla 530

Repeat Your Success with Templates 530

Blend in with Add-Ins 535

Managing Change 539

Employing Centralized Template Deployment 539

Implementing Version Awareness 541

Fear Not When Things Go Wrong 550

Summary 554

Index 557

This page intentionally left blank

0 0

Post a comment