Microsoft Excel -- A BetterVisual Basic than Visual Basic

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 elements—such as pushbuttons—is 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.

click to view at full size.

Figure 25-1. An Excel spreadsheet that uses VBA code.

 

click to view at full size.

Figure 25-2. The VBA code for the Excel spreadsheet.

 

If you want to create the example yourself, follow these steps:

  1. Start Excel with a new workbook, press Alt-F11, and then double-click Sheet1 in the top left window.

  2. Type in the macro code shown in Figure 25-2.

  3. Return to the Excel window by choosing Close And Return To Microsoft Excel from the File menu. Choose Toolbars from the View menu. Check Forms to display the Forms toolbar. (You can also access the list of toolbars by right-clicking on any existing toolbar.)

  4. Click the Button control, and then create the pushbutton by dragging the mouse in the upper-left corner of the worksheet. Assign the button to the Sheet1.ProcessColumn macro.

  5. Size the pushbutton, and type the caption Process Col, as shown in Figure 25-1.

  6. Type some numbers in the column starting at cell A4. Select the cells containing these numbers, and then click the button to test the program.

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.



Programming Visual C++
Advanced 3ds max 5 Modeling & Animating
ISBN: 1572318570
EAN: 2147483647
Year: 1997
Pages: 331
Authors: Boris Kulagin

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net