At the time that the first three editions of this book were written, Visual Basic worked as an Automation client, but you couldn't use it to create an Automation component. Since version 5.0, Visual Basic lets you write components too, even ActiveX controls. We originally used Excel instead of VB because Excel was the first Microsoft application to support VBA syntax and it could serve as both a client and a component. We decided to stick with Excel because C++ programmers who look down their noses at Visual Basic might be inclined to buy Excel (if only to track their software royalties).
We strongly recommend that you get a copy of Excel 97 (or a later version). This is a true 32-bit application and a part of the Microsoft Office suite. With this version of Excel, you can write VBA code in a separate location that accesses worksheet cells in an object-oriented manner. Adding visual programming elementssuch as pushbuttonsis easy. Forget all you ever knew about the old spreadsheet programs that forced you to wedge macro code inside cells.
This chapter isn't meant to be an Excel tutorial, but we've included a simple Excel workbook. (A workbook is a file that can contain multiple worksheets plus separate VBA code.) This workbook demonstrates a VBA macro that executes from a pushbutton. You can use Excel to load Demo.xls from the \vcpp32\ex25a subdirectory, or you can key in the example from scratch. Figure 25-1 shows the actual spreadsheet with the button and sample data.
In this spreadsheet, you highlight cells A4 through A9 and click the Process Col button. A VBA program iterates down the column and draws a hatched pattern on cells with numeric values greater than 10.
Figure 25-2 shows the macro code itself, which is "behind" the worksheet. In Excel 97, choose Macro from the Tools menu, and then choose Visual Basic Editor. (Alt-F11 is the shortcut.) As you can see, you're working in the standard VBA 5.0 environment at this point.
Figure 25-1. An Excel spreadsheet that uses VBA code.
Figure 25-2. The VBA code for the Excel spreadsheet.
If you want to create the example yourself, follow these steps:
Pretty easy, isn't it?
Let's analyze an Excel VBA statement from the macro above:
Selection.Offset(1, 0).Range("A1").Select
The first element, Selection, is a property of an implied object, the Excel application. The Selection property in this case is assumed to be a Range object that represents a rectangular array of cells. The second element, Offset, is a property of the Range object that returns another Range object based on the two parameters. In this case, the returned Range object is the one-cell range that begins one row down from the original range. The third element, Range, is a property of the Range object that returns yet another range. This time it's the upper-left cell in the second range. Finally, the Select method causes Excel to highlight the selected cell and makes it the new Selection property of the application.
As the program iterates through the loop, the preceding statement moves the selected cell down the worksheet one row at a time. This style of programming takes some getting used to, but you can't afford to ignore it. The real value here is that you now have all the capabilities of the Excel spreadsheet and graphics engine available to you in a seamless programming environment.