Steven Roman - Writing Excel Macros with VBA

As the title suggests, this book is for those who want to learn howto program Microsoft Excel Version 8 (for Office 97) and Version 9(for Office 2000).

We should begin by addressing the question, "Whywould anyone want to program Microsoft Excel?" Theanswer is simple: to get more power out of this formidableapplication. As you will see, there are many things that you can doat the programming level that you cannot do at the user-interfacelevelthat is, with the menus and dialog boxes of Excel. provides some concrete examples of this.

This book provides an introduction to programming the Excel objectmodel using Visual Basic for Applications (VBA). However, it is notintended to be an encyclopedia of Excel programming. The goal here isto acquaint you with the main points of Excelprogrammingenough so that you can continue your education (aswe all do) on your own. The goal is that after reading this book youshould not need to rely on any source other than the Excel VBA Helpfile or a good Excel VBA reference book and a nice object browser(such as my Enhanced Object Browser).

It has been my experience that introductory programming books (and,sadly, most trade computer books) tend to do a great deal ofhandholding. They cover concepts at a very slow pace by padding themheavily with overblown examples and irrelevant anecdotes that onlythe author could conceivably find amusing, making it difficult toferret out the facts. Frankly, I find such unprofessionalismincredibly infuriating. In my opinion, it does the reader a greatdisservice to take perhaps 400 pages of information and pad it withanother 600 pages of junk.

There is no doubt in my mind that we need more professionalism fromour authors, but it is not easy to find writers who have both theknowledge to write about a subject and the training (or talent) to doso in a pedagogical manner. (I should hasten to add that there are anumber of excellent authors in this areait'sjust that there are not nearly enough of them.) Moreover, publisherstend to encourage the creation of 1000-plus page tomes because of thegeneral feeling among the publishers that a book must be physicallywide enough to stand out on the bookshelf! I shudder to think thatthis might, in fact, be true. (I am happy to say thatO'Reilly has not succumbed to this opinion.)

By contrast, Writing Excel Macros with VBA isnot a book in which you will find much handholding (nor will you findmuch handholding in any of my books). The book proceeds at arelatively rapid pace from a general introduction to programmingthrough an examination of the Visual Basic for Applicationsprogramming language to an overview of the Excel object model. Giventhe enormity of the subject, not everything is covered, nor should itbe. Nevertheless, the essentials of both the VBA language and theExcel object model are covered so that, when you have finished thebook, you will know enough about Excel VBA to begin creatingeffective working programs.

I have tried to put my experience as a professor (about 20 years) andmy experience writing books (about 30 of them) to work here to createa true learning tool for my readers. Hopefully, this is a book thatcan be read, perhaps more than once, and can also serve as a usefulreference.

Preface to the Second Edition

With the recent release of Excel 10 (also called Excel XP), it wasnecessary to update my book. Excel XP is mostly an evolutionary stepforward from Excel 2000, but does have some interesting new featuresworth special attention, such as support for text-to-speech and smarttags.

The Excel object model has 37 new objects, containing 266 newmembers. There are also 180 new members of preexisting objects. Inthis book, I cover most of the central objects..)

Figure P-1. New objects in the Excel XP object hierarchy