9.4 The Limits of Smart Forms

9.4 The Limits of "Smart Forms"

Simplifying Use

The two example forms are relatively simple to use, but there is room for improvement. For example, a menu bar that is reduced to only those commands needed to run the form might be useful.

The forms are not well protected against invalid input. If input in incorrect format occurs (for instance, a number instead of a date or time, text instead of a number), the result is incomprehensible error messages, or even completely erroneous results.

A protection against errors in the direct input of values in worksheets (as opposed in forms) is relatively difficult. In general, there are two possibilities: Either have critical values input through a dialog (form), where immediate checking is possible (and the dialog can be terminated only when all input values are syntactically correct), or expand the code of the Print or Save button with program code that checks over all the input cells of the form to make sure that they are correctly formulated.

Overhead in Saving

The car-sharing smart form requires about 50 kilobytes of storage space. But the significant data can be reduced to ten cells ( name , date, type of auto, start time, end time, start date, end date, mileage, gasoline cost, total). All other data are redundant. That is, it would be sufficient to save these data only once, and not in each form. There are several ways of solving this overhead problem:

• One variant consists in separating the template and code into two files. The template needs merely a Workbook_Open procedure to be added, in which the code file is loaded and then linked to the template with OnEvent procedures. The drawback is that it cannot be avoided that the data file might be transmitted to another computer without the code file. The result would be incomprehensible error messages.

• A second variant was introduced in the Speedy example template. There, a separate "save" macro makes sure that at least only the worksheet (and not the entire VBA code) is saved. The drawback of this way of doing things is that if a file is created on the basis of this template and then later edited, the features of the template that provide ease of input are no longer available.

• The third variant consists in saving only those data that are truly relevant. In principle, this idea is similar to that associated with templates with database linkage via the template wizard; the difference is that the form need no longer be saved in a separate file. For this to happen there must be commands to read previously saved data from the database into the form template for renewed editing. The drawback of this solution is that the template has metamorphosed into a full-fledged database application with all the programming effort involved.

The Danger in Isolated Solutions

Our smart form for creating an invoice is a nice thing to have, but in practice, it is insufficient for a real-world office or business. Normally, invoices from one division are entered in another division, one responsible for entering payments and sending out reminders of overdue payments. Furthermore, orders must be correlated with stock management. Therefore, a clear interface among divisions must be created.

For such complex and interwoven applications it is necessary at least that input data be effortlessly available for further application. For many applications the further processing must happen fully automatically.

Such difficult situations are beyond the capacity of Excel. To be sure, Excel offers a rather extensive suite of database commands, but for full-blown database applications a database program is more suitable.

Pointer  

The subject of databases, separate from that of form templates, is discussed in considerable detail in Chapters 11 through 13. The car-sharing template will be expanded into a database application in Chapter 11.