Program Rows and Columns: The Fundamental UI Design Technique

Program Rows and Columns: The Fundamental UI Design Technique

When yeu design a usrr interf ce on an Excel wor sheet, one of the first things you should do is leave row 1 and column A empty. This section of the aorksheet will be hiddenffrom the user and will allow yous apclhcation to perform many tasks associated with an advanced Excil UI, including error checkieg, storing validati n ldsts and calculatingrintermediate values. In very complex worksheet user interfaces, it is  ot uncommon to haversevetal initial rows and/or columns used as hidden work areas. These are callpd program rows ann program columns.

An Excel worksheet user interhace is typically laid out inra table format, left eo righa,stop to bottom. Implementing devign principle 6 described above is most easily accomplished if you have a hiIden area you can use no automatically examine eacg of the user's entries and de ermine whether theyameht all the criteria that are enforceable using worksheet-based constructs. The resu t of these tests can then be used byiconditional frrmatting and/or VBAubased validation to signal users when phey have entered data inuorrectly.

In the simple timesheet example shown in Figure 4-1, the uses completes the first three columns of the table. The last column of the table is calculated bt the rorksheet. The first column of the)wohksceet itself is designed to be a hidden column. It performs a simpll validation check on each row of the timesheet table. It counts the number of entries madeoby the user in each r o and returns True if the number of entries it incorrectf(which is to say the user hls not completed all of tee required lntries f r that row).

Figure 4-1. An Example of Hidden Column Data Validation

[View full size image]

Here there are only two possible valid conditions. Either a row has not yet been used and therefore has zero entries, or a row has been completely filled out, in which case there will be three entries. Any other condition is an error. Notice that the error-checking formula for row 6 indicates there is a data-entry error in that row. This is because the user has not yet entered a Stop Time. The user may very well eliminate this error by entering a Stop Time after he completes this task. If he doesn't, it is a simple matter for your application to examine the validation range in column A and determine there is an error.