In the previous post, I introduced the idea of a cursor object that allows you to navigate around a graph of component-part relationships:
Navigation could be:
- down the ‘contains’ relationships
- along the ‘used in’ relationships
- back through the history of visited records.
We’ll also have a Reset operation, which jumps back to the start of the table, and clears the history.
The navigation is done using keyboard shortcuts (but could be done via a form).
The core of the design is a Class Module GraphCursor. This provides our four operations: CursorDown, CursorNextUse, CursorBack and CursorReset. When an instance of this class initializes, it points itself at ListObjects(1) on the ActiveSheet (there is only one sheet, to keep things simple), and does a CursorReset.
A GraphCursor maintains a history of visted components using a linked List class (a simple chain of ListItem objects – nothing to do with ListObject a.k.a. Table).
CursorDown and CursorNextUse use Range.Find with the currently selected cell value. I assume this is pretty efficient – and in any case is neater in code terms than iterating through rows explicitly. The Range for CursorDown is just the first column (Component); the Range for CursorNextUse is the Part columns below the row of the current selection.
Something needs to create and hold on to an instance of GraphCursor – this is a standard module Client. This also provides procedures that are called via the keyboard shortcuts.
Public gc As New GraphCursor Sub GCDown() gc.CursorDown End Sub 'similarly for the other three operations
The keyboard shortcuts are set up on Workbook_Open:
Private Sub Workbook_Open() Application.OnKey "^+d", "GCDown" Application.OnKey "^+n", "GCNextUse" Application.OnKey "^+r", "GCReset" Application.OnKey "^+b", "GCBack" GCReset End Sub
Here’s the workbook (Excel 2007).
Since each navigation step is worked out dynamically, we can insert or delete records from our table as we like. This would not be the case for an indexed solution (maintaining a map of Component to row number).
You could argue that each Component-Part relationship should be a separate record – for example, [A, B], [A, C]. This would allow us to associate quantities or other information with each relationship. In this case, we would also need a CursorNextPart operation.