9.1 Fundamentals


9.1 Fundamentals

In principle, "smart" forms are simply normal Excel tables in which some values have been left open for later input. A key feature is simplicity of operation. That is, it must be clear where input can be made, where not, and what meaning the various inputs have. To ensure against faulty use of the form, all other (noninput) cells must be protected against erroneous changes.

Excel form templates are thus " smarter " than the forms produced by any number of beaurocracies: The user does not have to input results that are a consequence of input already present; these will be calculated and displayed automatically. Perhaps as well, the user will be immediately notified of input errors (such as the input of a number when a date is called for).

In addition to embedded formulas, "smart" forms can have other aids, such as a button to print out and save the form or perhaps other controls that simplify the selection of various options. It is even possible to create a link between the form and a database application.

For creating smart forms it is useful to have templates. Here we are talking about a particular Excel file type (identifier *.xlt ) that is designed for saving files that are to serve as templates for future Excel files.

In order for templates to be available to the FileNew command, *.xlt files must be stored in one of the following three directories (see Chapter 5 for details):

Userprofile\Application Data\Microsoft\Templates

personal

Office2000\Office\Xlstart

global

alternative startup file location

set in TOOLSOPTIONSGENERAL

Tip  

A so-called template wizard makes it possible to turn a form template into a simple database application without a great deal of programming effort. Below we describe how to use this template wizard and indicate as well some of its limitations.

Overview

An introductory example on the subject of smart forms can be found way back in Chapter 1. There you will find a form for calculating interest on a savings account with monthly compounding ( Intro5.xlt ). The form is simply structured and does not even use any macros.

In the next subsection we collect information on some typical Excel functions that are useful in creating smart forms: cell protection, IF formulas, printing worksheets, and so on. Then we go on to describe the use of the new template wizard and explain how forms are handled internally.

Two more complex examples ”the invoice form of the fictive mail-order business "Speedy" and the balance sheet for a car-sharing club ”stand at the center of the next two sections. These examples demonstrate the principal representations of smart forms. One could also consider forms for simplifying complex financial calculations (credit repayment, savings accounts, insurance models), for the analysis of measurement data, for evaluating examinations or questionnaires, for organizing data sets, and so on. The templates provided within Excel demonstrate further application possibilities.

The chapter ends with some critical observations, combined with constructive criticism and suggestions for improvement.

Design Elements for Smart Forms

Templates

In principle, templates are normal Excel files that are specified as file type "Template" when saved under the Save As protocol. Templates have the identifier *.xlt (instead of *.xls ). In the Save dialog it is automatically suggested that such files be saved in the folder Userprofile\Application Data\Microsoft\Templates . If you use this directory, then the template can be used only by you (and not by other users with different log-in names ).

To open an empty template, the menu command FileNew must be used. In the dialog that appears all existing templates from the three template folders mentioned above are displayed. When you attempt to save the new file, Excel forces you to specify a new file name . In this way you are prevented from accidentally overwritng the template.

Tip  

Open templates with FileOpen only if you wish to edit the actual template. Only FileNew prevents you from accidentally altering the template and saving it as a template file (instead of a creating a normal Excel file based on the template).

You may not use the button New on the standard toolbar in place of FileNew . This button creates a new, empty workbook without presenting any templates for your selection.

Templates are generally used in one of two variants:

  • One may create empty worksheets in which merely certain formats are specified. Such templates have the purpose of saving the user effort in creating formats that are used over and over, such as the setting of print parameters and window options or the definition of frequently used styles. In this way it is possible to promote a uniform layout of all Excel worksheets produced by, say, an office or business: company logo on the first page, standardized headers and footers, uniform font, and so on.

  • It is such more or less intelligent form templates with integrated VBA code that are the subject of this chapter.

Cell Protection

With ToolsProtectionProtect Sheet you can protect all the cells of a worksheet from being changed. You may generally do without a password, unless you suspect that the user will intentionally make changes to the worksheet. Since it does not make sense to protect all the cells, since then no input could be given at all, before executing the protection command it is necessary to remove protection from the input cells. The dialog for this purpose is invoked with FormatCellsProtection or the pop-up menu Format CellsProtection. There you deactivate the option "Locked."

If you frequently change the protection of individual cells, you can add the tool for this to the toolbar. This tool is located under the category "Format." Its image is a padlock.

Tip  

Further information on the protection functions available in Excel and the associated properties and methods can be found in Chapter 5.

Validation of Input Cells

Since Excel 97 you can use Data Validation to formulate validation rules for individual cells or entire ranges of cells (see also the introductory example in Chapter 1). This lets you restrict the format (integer, floating point number, date, text) and the permissible range of values of the input. If you specify text in the dialog pages Input Message and Error Message (see Figure 9-1), an infotext or error text is automatically displayed whenever the rules for the input are violated. With validation rules you thus ensure the form against incorrect input, and you give the user useful additional information. Validation rules cannot be altered in protected worksheets.

click to expand
Figure 9-1: Two dialog sheets for data validation

Template Preview

In the dialog FileNew there is a place reserved for a preview of the file to be opened. This document preview works only if before the template is saved, FilePropertiesSummary is used to activate the Save Preview Picture. Note, however, that this causes not only the template but all files based on it to increase in size by a certain number of kilobytes.

IF Formulas

In order to make a formula template more or less generally usable, the calculational model behind it almost always makes case distinctions. The formulas in a table must determine whether the input is valid. If it is, then there is a result to be calculated. But if not, then a value of 0 or even an empty cell should be displayed. Sometimes, different formulas are required depending on the input values, such as a discount that goes into effect only on a minimum order of goods.

The basis of such case distinctions is the IF formulas. The construction of such formulas is simple in principle:

 =IF(condition, result1, result2) 

Excel tests in its calculation of the cells in which the IF formula is located whether condition is satisfied. If it is, then the formula in result1 is employed for the calculation, and otherwise , the formula in result2 .

Let us consider an example: Suppose that cell A1 contains a unit price and B1 the number of units. In C1 is to appear the product of the amounts in A1 and B1, with a five percent discount if the number of units is 10 or more. Then the formula in C1 might be as follows :

 =IF(B1<10, A1*B1, A1*B1*0.95). 

Nested IF Formula

IF formulas begin to become unreadable when several cases are considered at once or if the conditions are complex.

We first consider the distinction among several cases: In this case several IF functions must be nested one within the other; that is, the result of the first IF formula is again an IF formula.

Here is an example: The situation is like that above (contents of A1, B1, C1), but now we wish to consider the additional case that B1 is empty, in which case the assumption is to be made that the number of units is 1. When A1 is also empty, then no result at all is to be shown (not even 0). The resulting formula might look like this:

 =IF(A1="", "", IF(B1="", A1, IF(B1<10, A1*B1, A1*B1*0.95))). 

The contents of the formula can still be easily understood : If A1 if empty, then no result is shown (that is, an empty character string ""). If B1 is empty (and if we have gotten this far, it has been established that A1 is not empty), then the result is simply the contents of A1, which corresponds to a unit count of 1. The third IF function comes into play only once it has been established that both A1 and B1 are not empty, and this formula corresponds to that of the first example.

Compound Conditionals

In the foregoing examples the conditions were extremely simple. But frequently, several conditions must be simultaneously fulfilled or perhaps only at least one of several conditions must be satisfied. To this end several conditions can be given within an AND or OR function. Of course, these functions can be nested as well. The general syntax is as follows:

 =AND(condition1, condition2, condition3, ...), =OR(condition1, condition2, condition3, ...). 

AND returns the Boolean value TRUE if all the listed conditions are satisfied (and there can be arbitrarily many of them). OR returns TRUE if at least one of the conditions is satisfied.

An example of a compound conditional is the following: Values between 0 and 1 should appear in cells A1 and B1. If that is indeed the case, then the following formula computes their product. Otherwise, an error text is displayed:

 =IF(AND(A1>=0, A1<=1, B1>=0, B1<=1), A1*B1, "incorrect initial values") 
Tip  

With Shift+Ctrl+A you can insert the appropriate parentheses and arguments after typing the function name. This input assistance is to be recommended when you are uncertain as to the precise order of the arguments. But you can just as well do without the complicated input assistance provided by Excel (the former function assistant).

Pointer  

Various advanced worksheet functions are described in Chapters 5 (calculations with date and time) and 11 (database worksheet functions). In the case of complex tasks , worksheet formulas can become extremely unreadable. For such situations Excel offers the possibility of defining functions in VBA. Details on creating user-defined functions can be found in Chapter 5.

Labeling Colored (Red) Error Texts

There is is no way within an IF formula to influence the output format (number format, color , alignment, border, and so on) of a cell. By taking the detour of number formatting you can nevertheless manage to get numbers in normal (black) type to be represented in another color.

 Standard;Standard;Standard;[Red]Standard 

As an example, with the number format shown above you can display positive and negative numbers as well as 0 in standard format, with texts, however, in red (but otherwise in standard format). In addition to red you can provide in square brackets an additional seven colors: black, blue, cyan, green, magenta , white, and yellow.

Pointer  

Of course, you can also define special formats for each of these four cases (number positive, negative, 0 text). A considerable amount of background information can be found in Chapter 5.

Conditional Formatting

Even more formatting options are offered by so-called conditional formatting (see Figure 9-2). With it three conditions can be formulated that are checked one after the other when the content of a cell changes. Each condition is associated to a format, which in addition to color and font can include border and patterns. The first condition that is satisfied determines the format for the cell.

click to expand
Figure 9-2: Conditional formatting

With conditional formatting you can arrange things, for example, so that numbers exceeding a certain value are highlighted by having their color change. In VBA programming conditional formats can be defined via the object FormatCondition (using the Range property FormatConditions(n) ).

Printing the Form

Smart forms are generally conceived in such a way that they can be easily printed. You should plan for this eventuality when you are planning your form and order the document in such a way that it corresponds in a sensible way to the print area of one or more pages.

Headers and Footers

Excel makes provision for worksheets to have no header and footer. For forms these settings are generally not particularly suitable and can be changed or deleted with FilePage SetupHeader/Footer.

Current Date

If the smart form is to contain the current date, you can specify that it appear in any given cell with the formula = TODAY() . Alternatively, you can insert the date into the header or footer. In the latter case, however, you have fewer formatting choices in the placement of the date.

With both variants you must beware that when the form is saved and then opened and printed at a later date, the date will have changed (because once again the current date will be inserted). If that is not the desired effect, then a macro must be used to ensure that the date is frozen in time, so to speak, before the file is closed. To this end either the cell with the Today formula can be changed (EditPaste Special, with option Values), or else a text string with the current date can be written into the header or footer.

Serial Number

A more challenging task is to equip your smart form with sequential serial numbers, which would be useful, for example, in preparing and printing invoices. A solution to this problem is available only by means of a macro that is executed when the document is printed or immediately before printing. This macro must be able to access the number of the previously printed form. An example is presented in the next section.

Layout of the Form

If you are not satisfied with the layout options offered by Excel, say for the appearance of your firm's logo, you can use an OLE program (such as PaintBrush, WordArt (supplied with Word), or Corel Draw). Graphical objects can be integrated into the worksheet with InsertObject or InsertPicture. If you want to draw a narrow frame around the object, this can easily be done in the pop-up menu for the object. Choose Format Picture Colors And Lines.

Color

Colored text, and cells with a colored background, present problems when a document is printed. On the other hand, colors can make the use of a smart form much more user friendly. A way out of this dilemma is to carry out the printing with a macro designed to solve this problem. This macro (which can be easily recorded) eliminates from the printing all background colors and then restores the original format. If this restoration is computationally expensive, you can simply copy the entire worksheet to the clipboard, insert a new worksheet, and there insert the contents of the clipboard, eliminate the background colors, print the worksheet, and then delete it. In this way the original is left untouched. An example of this method appears in the section after next.

Integrating Controls into the Form

The same controls that can be placed in forms (dialogs) (see Chapter 7) can also be placed in worksheets (thus in "smart" forms ”note the two unrelated uses of the word "form"): text boxes, buttons, check boxes, option buttons, listboxes, scroll bars, and so on. In practice, the most frequently used of these are the buttons , which can be placed in the worksheet to allow various macros to be run "at the touch of a button."

However, the range of application is much broader. Thus you can insert scroll bars to enable the user to insert a numerical value with the mouse within a prescribed range. Or you can simplify the task of input by using listboxes, by means of which the user can choose an input value from a predefined list of options. With option buttons the user can choose from among various calculational or application variants. An introductory example appears in Chapter 7.

Of particular practicality in connection with controls is the possibility of using the property PrintObject to determine whether the particular control will be printed out. The default setting is True . If you set the property to False , then controls can be placed in a form to facilitate input without ruining the desired appearance of a printout.

Charts in Smart Forms

Form templates can also be used in combination with charts. If the range of a data set is fixed at the start of operations, then a completed chart can be integrated into the worksheet when the form is created. It will then change its appearance as data are entered. On the other hand, if the appearance and data range need to be flexible, then a macro must be used to create a new chart. Programming of charts is discussed in Chapter 10.

Templates Linked to Databases

Excel comes fitted out with a template wizard (command DataTemplate Wizard; if this item does not appear in your menu, you must activate this extension with the add-in manager via ToolsAdd-Ins; if the add-in manager seems not to know about the template wizard, then you will have to reinstall the wizard). The template wizard creates a link between a template and a database file.

Hint  

The template wizard is no longer included with Excel 2002. It is, however, available over the Internet, but Microsoft apparently no longer wishes to support it, perhaps because Excel files processed by wizards are based on Excel 4 macro code. The information in this section refers to the version of the wizard supplied with Excel 2000.

Most recently, information about the template wizard for Excel 2002 could be found at the following address:

 http://office.microsoft.com/assistance/2002/articles/ xlTemplateWizardAndDatabases.aspx 

The wizard itself can be downloaded from http://office.microsoft.com/downloads/2002/tmplwiz.aspx

The Template Wizard

To begin you will need a completed template ( *.xlt file). After having opened this template as a file (not with FileNew but with FileOpen), activate the template wizard with DataTemplate Wizard.

The format of the database file can be set in step 2 of the template wizard. Among the options are Excel worksheets and Access files. Excel worksheets have the advantage of being easily processed within Excel. Access files meet more stringent professional requirements and are particularly suitable for use with large data sets. Furthermore, Access need not be installed for you to be able to read and write Access files under Excel. (See also the next chapter, in which the database capacities and limitations of Excel are described.) After the database file has been defined, select and name in step 3 each input or result cell that you wish to be saved in the database.

In step 4 you are given the opportunity to add information from existing Excel workbooks whose format corresponds to the template to the database. This makes sense if the template has been used for a while and then later it has been decided to link the template to a database. In the case of new templates, simply answer No, Skip It.

Lastly, in step 5 you can specify e-mail addresses to be placed in a so-called routing list. Each time a new file that has been created based on the template is closed, you are asked whether this file should be sent to the addresses on the routing list. This system functions only if the e-mail client used is Outlook or a sufficiently current version of Outlook Express.

Tip  

If an existing file used as the basis of the template is changed, the changed file will not be sent again by e-mail. The changes will, however, certainly be entered in the template database.

Using a Database Template

Linked templates are used just like normal templates: The user opens the template, fills out the appropriate fields, and saves the file. At this point a dialog automatically appears (see Figure 9-3) in which the user is asked whether the data should be saved in the database file. If the user answers in the affirmative (it is hoped), all relevant cells (those previously selected in the template wizard) are entered into a new line or data record of the database.

click to expand
Figure 9-3: The two most important steps of the template wizard

Unfortunately, this dialog contains no information as to why this duplicate saving is useful or necessary or whether it obviates the need for normal saving. (See Figure 9-4.) A somewhat more informative text for this dialog and an associated help text would not be an extravagant luxury!

click to expand
Figure 9-4: Dialog for saving a new file based on a template

In principle, the new template is used as before: fill out, save, print. The only thing that is new is that the data in some selected cells are additionally saved in a special file.

Particularly attractive are templates with database linkages in networks. If all users have access to the same template, then all essential data from forms that are created from this template can be automatically recorded in a centrally managed file.

The evaluation of the database file ”whether in Excel, Access, or another file format ”is not regulated by the template wizard. If you wish, for example, to create monthly records or transfer data to an invoice database, you will have to write your own VBA macros. A template with database linkage is not a real database program, but at best, a step in this direction.

Internal Affairs

Perhaps you are interested in how a template enhanced by the template wizard functions internally. The template is extended to include two invisible sheets.

  • The Excel 4 macro sheet "AutoOpen Stub Data" contains the macro AutoOpen21 , which is automatically executed when the file is opened. This macro loads the add-in file Office2000\Office \Library\ Vlassist.xla . The add-in contains the actual VBA program code for the management of the database file. (The separation of template and code into two separate files has the advantage that Excel files resulting from templates are not made unnecessarily large.)

    You can look at the Excel 4 macro code if you first make it visible in the development environment:

      Sheets("AutoOpen Stub Data").Visible = True  
  • The worksheet with the name "TemplateInformation" can likewise be made visible in the development environment:

      Sheets("TemplateInformation").Visible = True  

    It contains information about which cells of which sheet of the template should be saved where. To prevent an intentional or unintentional manipulation by the user, all rows and columns are hidden. If you wish to have a look, select all cells with Ctrl+A and then execute FormatRowUnhide and FormatColumnUnhide.

Caution  

The program code in Vlassist.xla is based on the information in the two sheets described above and responds allergically to changes. Cell A1 of the TemplateInformation sheet contains, and with good reason, the text AutoTemplateWizardDONTMESSWITHIT.

Unfortunately, the source code of the add-in file Vlassist.xla is protected by a password and cannot be displayed. However, it is not difficult to guess that in this file an OnSave event procedure for templates has been developed such that on each save of a file based on the template, the procedure Vlassist.xla is automatically executed.

Deleting the Database Linkage

With DataTemplate Wizard you can create a linkage between a database and a template, but there is no option for breaking this linkage. Furthermore, you will find yourself in trouble if you later wish to change the type of linkage (for example, Access instead of an Excel file). The only solution consists in copying the new Excel file and saving it as a new template.

Templates Included with Excel

A number of templates are included in Excel, for example, for calculating the cost of a trip. These templates do not, alas, offer much in the way of an example for user friendliness and clarity. It is also not clear whether it makes sense to save a file of over 350 kilobytes (which happens each time you fill out a form and save it) every time you calculate the cost of a trip.




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net