NIVEL DE SATISFACCION GENERAL

We’ve taken advantage of hidden rows and columns in the PETRAS appli- cation for two purposes: error checking and background data processing. An open version of the PETRAS user interface workbook is shown in Figure 4-35.

There are two types of hidden columns in this worksheet. The two initial hidden columns are what we called program columns early in the chapter. We also have two hidden columns in the middle of the user interface. These two columns are used to create a data table that makes the process of automatically consolidating data simpler, while not requiring the user to enter duplicate data for each row. As we soon see in Chapter 5, we have special purpose code that uses the setHideCols named range, shown in the first row, to ensure these columns are hidden.

Defined Names

The Total Hours column in Figure 4-34 is calculated using a named for- mula called forTimeDiff. We used a defined formula for this purpose

because the logic required is complex and therefore it makes sense to encapsulate it. The forTimeDiff named formula makes use of relative

defined names to reference each part of the row from which it needs to gather the data required to perform its calculation. This defined formula is shown in Listing 4-1.

4.

W

ORKSHEET

D

ESIGN

Listing 4-1 The forTimeDiff Named Formula

=IF(COUNTA(inpEntryRow)<6,””, IF(inpStop>inpStart, inpStop-inpStart, (1+inpStop)-inpStart ) )

The input-type defined names (those with the “inp” prefix) are all row rel- ative defined names that refer to fixed columns on the TimeEntry work- sheet, as follows:

■ inpEntryRow = TimeEntry!$F3:$K3

■ inpStart = TimeEntry!$J3

■ inpStop = TimeEntry!$K3

If the number of entries in the current row is fewer than six, the formula simply returns an empty string. We cannot allow total hours to be calculat- ed for a row that has not been completed. Once all the entries in a row have been completed, we must compare the start and stop times. These times are entered as Excel date serial time values; therefore, they are dec- imal values less than or equal to 1 that have no indication of the date worked. We set up the time sheet in this manner as a convenience to the user. It allows the user to simply enter a start time and a stop time without also having to enter a specific date for each time.

If the stop time is greater than the start time we know both entries refer to the same day. We can then simply subtract the start time from the stop time to calculate the amount of time worked. If the stop time is less than or equal to the start time, we know the user began working prior to midnight on one day and finished working after midnight on the next day. In this case we add 1 to the stop time, which is equivalent to adding one day in the Excel date serial format, to force it to be greater than the start time. We then subtract the start time from the result. This allows us to account for situations in which users work over midnight.

Styles

Note that PETRAS uses the same styles we introduced previously in Figure 4-8. We use separate styles to identify row and column headers, input areas, formula results, and areas outside the user interface. The

TimeEntry worksheet shown back in Figure 4-34 is designed to be pro- tected, and once protected, the only cells that can be modified by the user are those having the Input style (the style with the white background).

User Interface Drawing Techniques

The PETRAS application demonstrates two of our recommended user interface drawing techniques. As shown in Figure 4-34, we used borders to give the time entry table a 3D appearance and a simulated grid to help guide the user. We also provided cell comments to answer the most com- mon questions the user may have about the user interface. The cell com- ment describing the Day column is shown in Figure 4-36.

Data Validation

Data validation has been used in every input cell in the PETRAS user interface. Most of the data validation derives from dynamic lists stored on the hidden wksProgramData worksheet, part of which is shown in Figure 4-37.

The Consultants column on the wksProgramData worksheet provides the data validation list for the Consultant entry on the TimeEntry work- sheet. Similarly, the Activities column on the wksProgramData worksheet provides the data validation list for the Activity column on the TimeEntry worksheet and so on. A complete picture of the various data validation techniques used on the TimeEntry worksheet can be gained by examining the sample application. Note that a more complex example of the cascad- ing lists data validation technique described earlier in this chapter is used to connect the Client and Project columns on the TimeEntry worksheet. FIGURE4-36 A cell comment used as help text

4.

W

ORKSHEET

D

ESIGN

FIGURE4-37 The hidden wksProgramData worksheet

Conditional Formatting

In Figure 4-34, you can see that conditional formatting was used to provide a clear visual indication of time entries made on a weekend. This is because work done on weekend days typically costs a higher hourly rate than work done on a weekday. Conditional formatting is also used to call out the error condition created when the user changes the first column entry of a cas- cading validation list pair if the second entry has already been made. In Figure 4-38, the user mistakenly changed a Client entry to a client that

does not match the Project entry previously made. Note how conditional formatting makes it instantly recognizable that changing the client entry was the wrong thing to do in this case.

Summary

We discussed many user-interface building techniques in this chapter; all of them implemented using Excel’s built-in features. However, don’t lose sight of the fact that the most important thing about an Excel user interface is not how many cool techniques you’ve used. Users don’t care about cool tech- niques. They want an intuitive user interface that makes it easy for them to get their job done. Ideally your user interface should not draw attention to itself at all. It should simply be so well designed and constructed that users can dive right in and start working without having to spend any significant time figuring things out. Adhering to the principles described at the begin- ning of this chapter can help you design user interfaces that do their job so well no one notices them. This is the best user interface design of all.

107

F

UNCTION

, G

ENERAL

,

AND

A

PPLICATION

-S

PECIFIC

A

DD

-

INS

Add-ins are the primary constituents of well-designed Excel applications. In this chapter we discuss the most important duties add-ins perform with- in an Excel application, as well as details about the activities various types of add-ins must perform. This chapter does not cover dictator applications, as that is the subject of the next chapter.