WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Learning how to use a new component is typically a mix of research, trial, and error. Some read the docs thoroughly first, some feel that Intellisense provides all the docs they need and dive right in. The most efficient mix depends, of course, on the programnmer and complexity of the component.
Some components can be mastered in a matter of minutes if you have a conceptual grasp, case in point, the Timer control in VB—there's just not that much to it, and if you've already worked with Win32 WM_TIMER messages, you won't be finding any intellectual challenge in the Timer component's three or four properties and one event.
The Grid might be considered a poster child for the term "mixed blessing." On one hand, it makes displaying your ad hoc data in a grid childishly easy. Just connect up a data source, bind it, and shazam: data in a box. On the the other hand, trying to make the grid behave intuitively, and getting it to do what you need it to do can be nothing short of nightmarish. Vague, ambiguous, and downright fictitious sections of the documentation only serve to intensify the frustration.
Late one night not long ago, weary from battling the Grid's quirkiness, I conceived a tool that has helped me to tame the Grid, and greatly reduce my frustration in the process. That tool, called GridSpy, is the subject of this article.
The essential part of GridSpy is a single, self-sufficient Form object (intuitively named GridSpy.) As long as the project contains references to ADODB, DataList, and DataGrid, GridSpy can be dropped right in; no other code needs knowledge of GridSpy to make it fly. Simply call the standard Form.Show method to display it. (GridSpy should always be opened "modelessly" because its feature set would be worthless in a modal form.)
To really have any fun with GridSpy, your project will need at least one other form, with at least one DataGrid in it and bound to data. A list of loaded forms is provided in a combo box (upper-left); when a form is selected, all DataGrids in that form (if any) are listed in the listbox below (middle-left.) To spy on a grid, double-click its corresponding item in the listbox.
The checkbox list (bottom-left) shows the events that have been generated so far, events are added to this list as they occur. If the checkbox is set for an item, the event it represents is logged to the textbox at right; if clear, the event is filtered from output to the log.
By default, all events except for one are added to this list with checkbox set, the exception being MouseMove (because I found myself clearing that item virtually every time I used GridSpy).
The Refresh button (top-left) re-enumerates the Forms collection, to add any forms that may have been loaded/shown after GridSpy was opened.
As you can see above, GridSpy logs events with parameter values passed to the event handler included. The output to the log window constitutes the definitive documentation of order of events, and indeed, shows us much more than we could ever hope to be documented.
Of course, we are still left to make good use out of what it gives us, some of which, imho, is pure insanity! Case in point: the event sequence and parameter values generated when the Grid is set to allow new records to be inserted, and the user scrolls to the bottom of the grid. I'll spare you an analysis, but for me, to look at it is to wonder what in hell they were thinking. A way to use it to my advantage escapes me.
Nuts and Bolts
At the heart of this code is a global variable of type DataGrid, declared WithEvents, and a tiny event handler for every event, that does nothing more than log occurrence with parameters. The rest of GridSpy concerns itself with enumerating object collections and reflecting their contents in the UI.
Option Explicit Private FormsArray() As Form Private GridsArray() As DataGrid Private stm As ADODB.Stream Public WithEvents Grid As DataGrid Private Sub Form_Load() Dim i EventList.Clear ReDim FormsArray(0) ReDim GridsArray(0) RefreshForms Set stm = New ADODB.Stream stm.Mode = adModeReadWrite stm.Type = adTypeText stm.Charset = "unicode" stm.Open End Sub
This function refreshes the forms combo box, an array of associated forms maintained by this class. Prior to repopulating its array, it releases all references previously stored.
Private Sub RefreshForms() On Error Resume Next Dim i For i = 0 To UBound(FormsArray) Set FormsArray(i) = Nothing Next ReDim FormsArray(Forms.Count - 1) FormsCombo.Clear For i = 0 To Forms.Count - 1 FormsCombo.AddItem Forms(i).Name Set FormsArray(FormsCombo.NewIndex) = Forms(i) Next FormsCombo.ListIndex = 0 End Sub
This is the combo box change event handler, which in turn refreshes the list of grids, and associated object array (again releasing references previously stored first).
Private Sub FormsCombo_Change() Dim i 'Bail if no matching selection was made If FormsCombo.ListIndex < 0 Then Exit Sub ' Free resources used by previously selected form For i = 0 To UBound(GridsArray) Set GridsArray(i) = Nothing Next ReDim GridsArray(0) ' Cache a reference to the newly selected form Dim f As Form Set f = FormsArray(FormsCombo.ListIndex) GridList.Clear ' Enumerate controls in form, copying a reference to any DataGrid ' objects ' to an array, and adding an item to the listbox. For i = 0 To f.Controls.Count - 1 If TypeOf f.Controls(i) Is DataGrid Then Set GridsArray(UBound(GridsArray)) = f.Controls(i) GridList.AddItem GridsArray(UBound(GridsArray)).Name, - UBound(GridsArray) ReDim Preserve GridsArray(UBound(GridsArray) + 1) End If Next Set f = Nothing End Sub
Double-click the event handler for a list of grids. This action selects a given grid object as the target of the spy.
Private Sub GridList_DblClick() ' Set a reference to the selected grid on the object declared ' WithEvents ' Set Grid = GridsArray(GridList.ListIndex) AppendOutput "(Attached to " & Grid.Parent.Name & "." _ & Grid.Name & ")" End Sub
(Please see the project provided for a full listing of the source.)
The demo project I've included to showcase GridSpy has an added bonus: It includes a DataGrid with a small XML-persisted recordset that it's bound to, plus it implements an Access-style lookup column (where the cells in one column turn into a combobox when focused, allowing the user to pick from pre-determined values).
What's more, the Grid is inside a container nested inside containers to four levels, to demonstrate resolving a parent-relative position from inside of a container.
As such, it demonstrates using XML-persisted data, runtime binding, advanced position resolution, attaching to the event chain of an object in another form... and beside all that, it's a useful tool, all inside of 1000 lines of code. (A companion article explains the techniques involved.)