Visual Basic 3 and the beauty of grids. (Microsoft's Grid drop-in custom control) (Programming Power) (Column)
by Tom Campbell
Microsoft has made some minor but interesting missteps on its way to earth-shattering success with Visual Basic, a language that made the most spectacular debut since Turbo Pascal's in 1983. One of them was with Grid, which provides the barest feature set of a spreadsheet in the form of a drop-in custom control. Grid wasn't quite ready for prime time when Visual Basic made its very auspicious debut, so someone at Microsoft quietly uploaded a version of it to CompuServe and other online services shortly after Visual Basic's introduction.
Grid gives you an interface that looks like a spreadsheet in Excel. It has a matrix of cells that can be navigated using either the keyboard or the mouse. It has optional horizontal and vertical scroll bars; optional "fixed" rows and columns that appear gray at the top row and leftmost column of the grid, allowing you to have row and column titles; and the ability to let you resize rows and columns simply by dragging the mouse (this last one is a feature that earned Excel for the Macintosh rave reviews years before there was a Windows version).
I'm sure Microsoft wouldn't have released it if the people there had understood what it would mean to let that particular genie out of its bottle. But there was just too much demand for a custom control that would allow users to present data in a row-and-column format. Besides, it looks really cool to have what looks like a mini Excel built into your app.
Grid is also known to have its problems. It's not data aware, so you can't use it out of the box to browse databases or to read in a few records at a time because it reads all of its contents into RAM. There's no way to enter data into a cell, so you have to fake it using a floating edit box (not difficult, though, and the VB manuals have an example showing how to do it that incidentally illustrates VB's amazing flexibility). As it turns out, some of Grid's "features" could legitimately be regarded as design problems, as you'll see in a moment.
The drop-in routine available online for this month's column is called FillGrid, which you'll find in the TPHONE.ZIP distribution. I've made it generic so you can just add it to any Visual Basic project as a module. Just open a database and a table; then pass FillGrid the name of the grid, the name of the table, and a third parameter that allows you to use FillGrid's local error handler if you wish. Unlike other example programs you'll see which hardcode the name of the control into the example, this will let you view any table up to 2000 rows--Grid's limit--without writing a line of code yourself. I've included it as part of a nifty little phone book application so you can see it in action.
FillGrid shows some useful concepts, notably using the Windows API to figure out how wide each column of the grid should be. It uses the everhandy GetTextExtent API routine to determine how wide each field should be by computing its width in logical units and ANDing that value with &FFFFH because GetTextExtent also returns the height of the character in the upper two words. FillGrid also does you the courtesy of making the field the width of the field name if the name has more characters in it than the field; otherwise, a Boolean field named, for example, Present would look like P in the grid. FillGrid's brevity is a tribute to Visual Basic. The C code required to implement a grid would be overwhelming; using the Grid VBX with Visual C++ is also quite a bit more work, although much more manageable than it would be in C.
Of course, you'd like to see more features. The most obvious one is the ability to edit the database by inserting and deleting records, but that brings up some interesting issues. Visual Basic's database engine is designed to be multiuser. What happens if someone deletes a record from the same table you're using? Since your program isn't informed of this, the contents of your grid become invalid without your knowing it. Much more complicated is the idea of selecting a record in the grid and connecting that selection with a record in the table. You'd have to read the record's position--in essence, its number in the grid--and somehow relate that record number to a record number in the table. But the Visual Basic database engine doesn't think in record numbers, again because they make no sense in a multiuser environment.
The best way to handle this situation is to replace Grid with a data-aware custom grid control. In later issues we will examine the best of the best, although for now, you might just want to look toward FarPoint Technologies' Spread/VBX. Spread/VBX will give you many true spreadsheet functions, including formulas and Clipboard support. Or you may want to try FarPoint's lower-cost Grid/VBX, which gives you a topnotch, data-aware grid without the spreadsheet-specific features. Contact FarPoint at (804) 378-0432 or (804) 378-1015 (fax). Both of these products are excellent.