Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Program Rows and Columns: The Fundamental UI Design Technique

When you design a user interface on an Excel worksheet, one of the first things you should do is leave row 1 and column A empty. This section of the worksheet will be hidden from the user and will allow your application to perform many tasks associated with an advanced Excel UI, including error checking, storing validation lists and calculating intermediate values. In very complex worksheet user interfaces, it is not uncommon to have several initial rows and/or columns used as hidden work areas. These are called program rows and program columns.

An Excel worksheet user interface is typically laid out in a table format, left to right, top to bottom. Implementing design principle 6 described above is most easily accomplished if you have a hidden area you can use to automatically examine each of the user's entries and determine whether they meet all the criteria that are enforceable using worksheet-based constructs. The result of these tests can then be used by conditional formatting and/or VBA-based validation to signal users when they have entered data incorrectly.

In the simple timesheet example shown in Figure 4-1, the user completes the first three columns of the table. The last column of the table is calculated by the worksheet. The first column of the worksheet itself is designed to be a hidden column. It performs a simple validation check on each row of the timesheet table. It counts the number of entries made by the user in each row and returns True if the number of entries is incorrect (which is to say the user has not completed all of the required entries for 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.