How Visual Basic for Applications Talks to Excel
The first spreadsheet macro languages mimicked the user interface. For example, if you typed R (for 'Range'), N (for 'Name'), and C (for 'Create') in the user interface, you would enter RNC into the macro to automate the process. This approach had inherent weaknesses. Not only were keystroke macros difficult to read, but they also didn't adapt well to the graphical user interface. What keystrokes would you use to represent dragging a rectangle with the mouse?
To solve these problems, the early versions of Excel contained a new type of macro language that made the macro commands independent of the user interface. For example, in Excel version 4 you could copy a range at least three different ways: press Ctrl+C, click the Copy toolbar button, or click Copy on the Edit menu. All those user interface sequences translated to a single macro function, =COPY(). These function-based macros had two major drawbacks: First, Excel macros were very specific to Excel; the language couldn't be adapted to other applications. Second, the number of functions kept increasing with each new version, and there was no good way to organize or group the thousands of possibilities.
Excel with VBA incorporates Automation (once known as OLE Automation), a powerful way of automating applications. Excel was the first major application to take advantage of this concept. In this approach, VBA acts as a general-purpose language that's independent of the application. Suddenly, anyone who knows how to work with any version of Visual Basic has a big head start in automating Excel, and anyone who learns how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming.
Even though Excel hosts VBA, VBA doesn't have any special hooks into the internals of Excel. Rather, Excel exposes its capabilities to VBA by means of a special set of commands known as an object library. VBA talks to the Excel object library.
VBA can control not only Excel, but also any application that provides an object library. All Microsoft Office applications provide object libraries, and several other Microsoft and non-Microsoft applications also do.
The VBA that comes with Excel isn't the only language that can communicate with the object library. Any language that supports Automation can control Excel. You can control Excel not only with the VBA hosted by Excel, but also with a VBA project hosted by Microsoft Word, with the stand-alone version of Visual Basic, or even with C++ or Inprise Corporation's Delphi program.
Excel Objects and You
Not only does the object library expose Excel's capabilities to VBA, but more importantly, it also exposes Excel's capabilities to you. Once you know how to read and interpret an object library, you can discover new features and quickly figure out how to put them to work. The best way to learn how VBA communicates with Excel objects is to record some simple macros. Eventually, however, you'll want to move beyond the limitations of the macro recorder.
If you work through this book sequentially, you'll first learn how to record and modify simple macros. Then you'll learn how to use the VBA tools for exploring objects as you learn how to use some of the most important objects in Excel. Next you'll learn how to use VBA features to make your applications more powerful. And finally you'll learn how to make a macro easy to use.
On the CD This chapter uses the practice file Budget.xls that you installed from the book's CD-ROM. For details about installing the practice files, see 'Using the Book's CD-ROM' at the beginning of this book.
The Chapter01 workbook contains a single worksheet, named Budget. This worksheet includes a projected month-to-month budget for the year 2002.