38.

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.

Important 

VBA is a version of Visual Basic that's hosted by an application, such as Microsoft Excel. A VBA macro can't run independently of its host application. VBA and the stand-alone version of Visual Basic both use the same language engine, editor, and most supporting tools. In this book, I'll use 'VBA' to refer specifically to the macro language in Excel, and 'Visual Basic' to refer to anything that's shared by all versions of Visual Basic.

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.

Getting Started

Open

1. Start Excel.

2. On the Standard toolbar, click the Open button, and then in the Open dialog box, click the Favorites button.

3. Double-click the folder that contains the practice files installed from the companion CD, and then double-click the Budget workbook.

4. Save the Budget file as Chapter01.

The Chapter01 workbook contains a single worksheet, named Budget. This worksheet includes a projected month-to-month budget for the year 2002.