Writing Excel Macros with VBA, 2nd Edition

Preface

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

We should begin by addressing the question, “Why would anyone want to program Microsoft Excel?” The answer is simple: to get more power out of this formidable application. As you will see, there are many things that you can do at the programming level that you cannot do at the user-interface level—that is, with the menus and dialog boxes of Excel. Chapter 1 provides some concrete examples of this.

This book provides an introduction to programming the Excel object model using Visual Basic for Applications (VBA). However, it is not intended to be an encyclopedia of Excel programming. The goal here is to acquaint you with the main points of Excel programming—enough so that you can continue your education (as we all do) on your own. The goal is that after reading this book you should not need to rely on any source other than the Excel VBA Help file 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 of handholding. They cover concepts at a very slow pace by padding them heavily with overblown examples and irrelevant anecdotes that only the author could conceivably find amusing, making it difficult to ferret out the facts. Frankly, I find such unprofessionalism incredibly infuriating. In my opinion, it does the reader a great disservice to take perhaps 400 pages of information and pad it with another 600 pages of junk.

There is no doubt in my mind that we need more professionalism from our authors, but it is not easy to find writers who have both the knowledge to write about a subject and the training (or talent) to do so in a pedagogical manner. (I should hasten to add that there are a number of excellent authors in this area—it’s just that there are not nearly enough of them.) Moreover, publishers tend to encourage the creation of 1000-plus page tomes because of the general feeling among the publishers that a book must be physically wide enough to stand out on the bookshelf! I shudder to think that this might, in fact, be true. (I am happy to say that O’Reilly has not succumbed to this opinion.)

By contrast, Writing Excel Macros with VBA is not a book in which you will find much handholding (nor will you find much handholding in any of my books). The book proceeds at a relatively rapid pace from a general introduction to programming through an examination of the Visual Basic for Applications programming language to an overview of the Excel object model. Given the enormity of the subject, not everything is covered, nor should it be. Nevertheless, the essentials of both the VBA language and the Excel object model are covered so that, when you have finished the book, you will know enough about Excel VBA to begin creating effective working programs.

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

Preface to the Second Edition

With the recent release of Excel 10 (also called Excel XP), it was necessary to update my book. Excel XP is mostly an evolutionary step forward from Excel 2000, but does have some interesting new features worth special attention, such as support for text-to-speech and smart tags.

The Excel object model has 37 new objects, containing 266 new members. There are also 180 new members of preexisting objects. In this book, I cover most of the central objects. Figure P-1 shows most of the new objects in the Excel XP object hierarchy and where these objects occur in the Excel XP object model. (This figure is taken from my program Object Model Browser. For more information on this program, please visit my web site at http://www.romanpress.com.)

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

The Book’s Audience

As an introduction to programming in Excel VBA, the book is primarily addressed to two groups of readers:

• Excel users who are not programmers but who would like to be. If you fall into this category, it is probably because you have begun to appreciate the power of Excel and want to take advantage of its more advanced features or just accomplish certain tasks more easily.

• Excel users who are programmers (in virtually any language—Visual Basic, Visual Basic for Applications, BASIC, C, C++, and so on) but who are not familiar with the Excel object model. In this case, you can use Writing Excel Macros to brush up on some of the details of the VBA language and learn about the Excel object model and how to program it.

Organization of This Book

Writing Excel Macros consists of 21 chapters that can informally be divided into four parts (excluding the introductory chapter). In addition, there are five appendixes.

Chapter 1 examines why you might want to learn programming and provides a few examples of the kinds of problems that can best be solved through programming. Chapter 2 introduces programming and the Visual Basic for Applications language.

Chapter 2 through Chapter 4 form the first part of the book. Chapter 3 and Chapter 4 examine the Visual Basic Integrated Development Environment (IDE), which is the programming environment used to develop Excel VBA applications.

The second part of the book consists of Chapter 5 through Chapter 8, which form an introduction to the VBA language, the language component that is common to Microsoft Visual Basic and to many of Microsoft’s major applications, including Word, Excel, PowerPoint, and Access, as well as to software from some other publishers. Individual chapters survey VBA’s variables, data types, and constants (Chapter 5), functions and subroutines (Chapter 6), intrinsic functions and statements (Chapter 7), and control statements (Chapter 8).

The third part of the book is devoted to some general topics that are needed to create usable examples of Excel applications and to the Excel object model itself. We begin with a discussion of object models in general (Chapter 9). The succeeding chapters discuss what constitutes an Excel application (Chapter 10), Excel events (Chapter 11), Excel menus and toolbars (Chapter 12), and Excel dialog boxes, both built-in and custom (Chapter 13 and Chapter 14). (Those who have read my book Learning Word Programming might notice that these topics came at the end of that book. While I would have preferred this organization here as well, I could not construct meaningful Excel examples without covering this material before discussing the Excel object model.)

The last chapters of the book are devoted to the Excel object model itself. This model determines which elements of Excel (workbooks, worksheets, charts, cells, and so on) are accessible through code and how they can be controlled programmatically. Chapter 15 gives an overview of the Excel object model. Subsequent chapters are devoted to taking a closer look at some of the main objects in the Excel object model, such as the Application object (Chapter 16), which represents the Excel application itself; the Workbook object (Chapter 17), which represents an Excel workbook; the Worksheet object (Chapter 18), which represents an Excel worksheet; the Range object (Chapter 19), which represent a collection of cells in a workbook; the PivotTable object (Chapter 20); and the Chart object (Chapter 21). Chapter 22 covers Smart Tags. I have tried to include useful examples at the end of most of these chapters.

The appendixes provide a diverse collection of supplementary material, including a discussion of the Shape object, which can be used to add some interesting artwork to Excel sheets, determining what printers are available on a user’s system (this is not quite as easy as you might think), and how to program Excel from other applications (such as Word, Access, or PowerPoint). There is also an appendix containing a very brief overview of programming languages that is designed to give you a perspective on where VBA fits into the great scheme of things.

The Book’s Text and Sample Code

When reading this book, you will encounter many small programming examples to illustrate the concepts. I prefer to use small coding examples, hopefully, just a few lines, to illustrate a point.

Personally, I seem to learn much more quickly and easily by tinkering with and tracing through short program segments than by studying a long, detailed example. The difficulty in tinkering with a long program is that changing a few lines can affect other portions of the code, to the point where the program will no longer run. Then you have to waste time trying to figure out why it won’t run.

I encourage you to follow along with the code examples by typing them in yourself. (Nevertheless, if you’d rather save yourself the typing, sample programs are available online; see Section P.7 later in this Preface.) Also, I encourage you to experiment -- it is definitely the best way to learn. However, to protect yourself, I strongly suggest that you use a throw-away workbook for your experimenting.

One final comment about the sample code is worth making, particularly since this book and its coding examples are intended to teach you how to write VBA programs for Microsoft Excel. Generally speaking, there is somewhat of a horse-before-the-cart problem in trying to write about a complicated object model, since it is almost impossible to give examples of one object and its properties and methods without referring to other objects that may not yet have been discussed. Frankly, I don’t see any way to avoid this problem completely, so rather than try to rearrange the material in an unnatural way, it seems better to simply proceed in an orderly fashion. Occasionally, we will need to refer to objects that we have not yet discussed, but this should not cause any serious problems, since most of these forward references are fairly obvious.

About the Code

The code in this book has been carefully tested by at least three individuals—myself, my editor Ron Petrusha, and the technical reviewer, Matt Childs. Indeed, I have tested the code on more than one machine (with different operating systems) and at more than one time (at least during the writing of the book and during the final preparation for book production).

Unfortunately, all three of us have run into some deviations from expected behavior (that is, the code doesn’t seem to work as advertised, or work at all) as well as some inconsistencies in code behavior (that is, it works differently on different systems or at different times). Indeed, there have been occasions when one of us did not get the same results as the others with the same code and the same data. Moreover, I have personally had trouble on occasion duplicating my own results after a significant span of time!

I suppose that this shouldn’t be entirely surprising considering the complexity of a program like Excel and the fallibility of us all, but the number of such peccadilloes has prompted me to add this caveat.

Offhand, I can think of two reasons for this behavior—whether it be real or just apparent—neither of which is by any means an excuse:

• The state of documentation being what it is, there may be additional unmentioned requirements or restrictions for some code to work properly, or even at all. As an example, nowhere in the vast documentation—at least that I could find—does it say that we cannot use the HasAxis method to put an axis on a chart before we have set the location of the data for that axis! (This seems to me to be putting the cart before the horse, but that is not the issue.) If we try to do so, the resulting error message simply says “Method ‘HasAxis’ of object '_Chart’ has failed.” This is not much help in pinpointing the problem. Of course, without being privy to this kind of information from the source, we must resort to experimentation and guesswork. If this does not reveal the situation, it will appear that the code simply does not work.

• Computers are not static. Whenever we install a new application, whether it be related to Excel or not, there is a chance that a DLL or other system file will be replaced by a newer file. Sadly, newer files are not always better. This could be the cause, but certainly not the excuse, for inconsistent behavior over time.

The reason that I am bringing this up is to let you know that you may run into some inconsistencies or deviations from expected behavior as well. I have tried to point out some of these problems when they occur, but you may encounter others. Of course, one of our biggest challenges (yours and mine) is to determine whether it is we who are making the mistake and not the program. I will hasten to add that when I encounter a problem with code behavior, I am usually (but not always) the one who is at fault. In fact, sometimes I must remind myself of my students, who constantly say to me, “There is an error in the answers in the back of the textbook.” I have learned over 20 years of teaching that 99% of the time (but not 100% of the time), the error is not in the book! Would that the software industry had this good a record!

I hope you enjoy this book. Please feel free to check out my web site at http://www.romanpress.com.

Conventions in this Book

Throughout this book, we have used the following typographic conventions:

Constant width

indicates a language construct such as a language statement, a constant, or an expression. Lines of code also appear in constant width, as do functions and method prototypes.

Italic

represents intrinsic and application-defined functions, the names of system elements such as directories and files, and Internet resources such as web documents and email addresses. New terms are also italicized when they are first introduced.

Constant width italic

in prototypes or command syntax indicates replaceable parameter names, and in body text indicates variable and parameter names.

Obtaining the Sample Programs

The sample programs presented in the book are available online from the Internet and can be freely downloaded from our web site at http://www.oreilly.com/catalog/exlmacro2.

How to Contact Us

We have tested and verified all the information in this book to the best of our ability, but you may find that features have changed (or even that we have made mistakes!). Please let us know about any errors you find, as well as your suggestions for future editions, by writing to:

O’Reilly & Associates

1005 Gravenstein Highway North

Sebastopol, CA 95472

[phone] (in the U.S. or Canada)

[phone] (international/local)

[phone] (fax)

There is a web page for this book, where we list any errata, examples, and additional information. You can access this page at:

http://www.oreilly.com/catalog/exlmacro2

To ask technical questions or comment on the book, send email to:

[e-mail]

For more information about our books, conferences, software, Resource Centers, and the O’Reilly Network, see our web site at:

http://www.oreilly.com

Acknowledgments

I would like to express my sincerest thanks to Ron Petrusha, my editor at O’Reilly. As with my other books, Ron has been of considerable help. He is one of the best editors that I have worked with over the last 17 years of book writing.

Also, I would like to thank Matt Childs for doing an all-important technical review of the book.