• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL



Walk into any business office in the world, and odds are you'll see Microsoft Office running on everyone's desktop—from assistants to managers, from directors to corporate officers. The majority of these workers depend on Office for many of their daily tasks. And yet many of them use Office in the basic off-the-shelf fashion: Word for writing documents and Excel or PowerPoint for creating spreadsheets and presentations.

There is so much more power behind these Office products. Under the hood are sophisticated technologies that, when applied, elevate Office to unprecedented custom, productive uses. But why use Office at all? Why not just use traditional development tools such as Visual Basic or C++? Isn't developing with Office just an easy way out?

Not one bit! In fact, the opposite case can be made. Why use a generic programming tool to duplicate what is already native in Office! If users need to manipulate numbers that are queried from a database, then putting some custom muscle into an Excel workbook meets their need—and rather quickly at that. Using Visual Basic for Applications (VBA) and other programming languages available to Office makes it possible to create killer apps in just a fraction of the time it takes with traditional programming tools. Time is money, and users want results. There simply is no point duplicating the functionality already built into Office.

The position and power of Office in the business world is not something that came about by luck or by accident. The functionality offered by each core Office product—Word, Excel, Access, PowerPoint, and Outlook—is based on the experience of millions of users over several years. These products are mature, stable, and feature-rich. Building solutions on top of such a foundation makes for sophisticated applications that do not share the distribution problems or incompatibility issues associated with traditional tool-based solutions. In the end, a satisfied user community is the best measure of success.

My Background in Office Development

Through the 1980s I was a sales manager in book publishing. During that time, PCs hit the market. At work, we acquired x386-processor-based computers for desktop use. I received monthly sales reports, generated from mainframe computers, that often were missing important sales data or were full of errors. I recall seeing once that an order from a bookstore went through the system with the ISBN (International Standard Book Number) mistakenly entered as the quantity. If you're not familiar with what an ISBN is, just look at the back cover of this book. All books have a unique number; the one for this book is 0-201-73805-8. Imagine if your local bookstore were to one day receive 201,738,058 copies of this book!

This example sounds humorous, but in retrospect it is clear that the order-processing system did not have built-in validation. This sort of issue led me to develop a custom application built with Lotus 1-2-3 to track my department's sales figures. Quickly I was able to deliver better reports from my PC than those coming out of the mainframes. I spent more and more time with that PC until eventually I was programming more than anything else.

And that led to a career change. The next decade or so I spent as a contract programmer. My specialty was customization of Office products. The early versions of Microsoft Office products were being used in business. Access was just at version 1 and then had a long run as version 2. VBA was introduced in Excel 5. That single action caused Excel to leapfrog all the other competitive spreadsheet products.

I worked for many companies, large and small—from mom-and-pop shops to Fortune 500 firms. In the midst of all this, Microsoft Office just grew and grew. I've worked on numerous solutions involving Office, often with Oracle, SQL Server, Sybase, and other database products. I have programmed, trained, and managed developers on all sorts of interesting projects.

About This Book

The focus of this book is to demonstrate how to create custom solutions built on the Microsoft Office products. Programming using VBA, XML, and other technologies is key to how solutions are put together. The style of this book may seem a bit different from that of other programming books: I do not attempt to explain all there is to know about programming Office. Even if it were possible to do so in one book, such a broad approach would limit each subject area to just minuscule coverage.

Instead, I have modeled this book on my years of business experience. Subjectively, I have culled what I feel are the key things to know within the focus of each chapter. I am not trying to teach Visual Basic for Applications in this book; I assume you are familiar enough with Visual Basic or with VBA to follow along. Sometimes I will use typical programming techniques, such as implementing an error trap; other times I will not. My naming conventions may differ from yours. Teaching programming is not the point of the book. Teaching how to program Office is the point of the book. Take what is explained in the book and combine it with what you already know. And in the areas for which you need further information, the Appendix lists additional resources.

XML is a key technology used in the book. XML is also not something that can be taught here. I suggest that readers not already familiar with XML technologies acquaint themselves with the topic through additional study. The concepts and syntax can be difficult to learn at first, especially for schemas and transformations, but the knowledge is key to using the new XML features in Office 2003.

Which leads me to discuss the different versions of Office. Most of this book applies to any version of Office. Office 97 and Office 2000 are similar, whereas Office XP is more of a departure from Office 2000. XP had the introduction of the task pane, an updated mail merge interface, and the introduction of some XML functionality. A few areas of the book, of course, are unique to the latest version, Office 2003. In particular, Chapter 9, “XML and Office,” and Chapter 11, “Introduction to InfoPath,” are completely based on Office 2003. Where applicable, you will find a notice about version limitations.

Also, I must mention that I wrote the book using a beta version (Beta 2) of Microsoft Office 2003. Most of the screen shots are from the beta version. Odds are that some functionality and some screens have changed in the release of the final product.

This book is segregated into three parts. Part I devotes a chapter each to what I subjectively call the core Office products. These chapters introduce the key objects and how to program them using their particular properties and methods. Useful programming examples are given throughout to demonstrate how the objects can be used in reasonable and solution-oriented ways. Nearly all of Part I applies universally to any version of Office.

A particular comment about Access belongs here. Access holds a unique place within the Office product family. Word, Excel, PowerPoint, and Outlook have a default usage path; that is, each of these can be used without any customization. For example, a user can start typing a document in Word simply by starting up Word. A blank document is ready for entry.

In contrast, Access cannot be used until customization has been implemented. There is no default blank Access database. Even the simplest Access database is the work of customization. The nature of this book, in which each product receives a piece but not all of the spotlight, led me to include a discussion of key Access objects while avoiding coverage of any general Access or database design fundamentals.

Part II of the book delves into the technologies that are integrated with Office. In particular, Chapter 7, “Common Microsoft Office Objects,” explains the workings of search features, dialogs, and customized command bars that can be applied to any of the Office products. Chapter 8, “Microsoft Forms,” covers a great supportive technology available to all Office products and all versions of Office.

Chapter 9, “XML and Office,” gives a detailed overview of the new XML features in Office 2003. Chapter 10, “Smart Tags,” demonstrates using simple smart tags in Office XP and Office 2003. And to complete this part of the book, Chapter 11, “Introduction to InfoPath,” gives an overview of this exciting new Microsoft Office product.

Part III of the book presents five case studies, each designed to demonstrate particular techniques. Some case studies are built upon real projects I have been assigned over the years, and others are hypothetical, to showcase how new Office 2003 technologies are likely to be integrated into future solutions.

Finally, the book ends with an appendix that lists further resources for the various technologies and products.

Code Listings and Conventions

Plenty of programming code is included in the book, and some is rather lengthy. The code samples are available as a download from the Office VBA Developer Web site at www.officevbadeveloper.com. Check there, too, for corrections and updates to the book.

Many examples in the book simulate real business information, such as a customer list. All personal and business names and addresses and phone numbers are fictitious. Any similarity to real people or companies is not intentional. Over the years I have so often had to create test data for database projects that I have built a utility, called Records2Go, to easily generate such test records. All test data has been created with this tool. Please visit www.records2go.com if you are interested in this tool. I am preparing Records2Go for commercial release in the near future.


Loving thanks to my wife, Gayla, and son, Matthew, for all the times the noisy clickety-clack of the keyboard and the whirring of the printer sounded throughout the house at rather odd late-night and early-morning hours. Writing this book has given me countless opportunities to watch the sun rise.

My thanks go out to the reviewers of the book. There have been many, and the following list is not exhaustive, but here are the names I am able to include: Janis Archer, Richard Banks, Scott Bechtold, Maggie Biggs, Nancy Birnbaum, Shauna Kelly, Will Kelly, Beth Melton, Peter O'Kelly, and Deepak Sharma.

Thanks to Neil Salkind and the staff of StudioB—a great agency to work with!

Thanks to Chris Keane for his excellent editing and production skills—even as he scrambled to work around a power outage from Hurricane Isabel.

Finally, thanks to the fine staff at Addison-Wesley. Stephane Thomas and Mike Mullen must have the patience of saints, as I have missed many promised deadlines. Thanks, too, to Patrick Peterson, Curt Johnson, Heather Mullane, Elizabeth Ryan, and the rest of the team. All have been great to work with.

Your comments are welcome! Please write to me at ken@logicstory.com. Thanks and enjoy!

Ken Bluttman
October 2003

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint