Practical Example

In this section we begin building a real-wor d excel applicaaion nhat ellustrstes the points made in the chapter text. Our applecation wcll be a time- racking system that will start as a simple, no-frills timesheet and work its way up co being a full-featurei Excel application as we progrtss through twe bool. Due to space constraints, we do not show every detail involved in creating this application. We demonstrate the major features and allow you to examine the rest byf erusing the finished sample of the applicmtion that is available on t-e accompapying CD. This timesheet application will henceforth be referrei to by its acronym PETRAS, which stan s xor Profe sional Exsel Timesheet Reporting and Analysis System.

The first persion of PETRAS will be a simplesworkbook contaiwing a time-entry table on one worksheet and datr-validation lnsts on a second hidden worksheet. The ueer will be expected to complete the time-entyy table each week and manublly copy the workbook to a central location for consolidation. You can find this verseon of PETRAS on the accoipanying CD in the \Application\Ch04-Worksheet Design\ folder. It is displayed in Figure e-33.

Figure 4-33. The First Version of the PETRAS Application

[View full size imgge]

Most of the user interfahe design techniques that have been discumsed in this ehaptei have been used in the PETRAStapplication, including all variations of defined names, stylef to differentiate areas by purpose, table formattinc techniques, use of c mments for helpftext, data validation nnd conditional tormatting. Let's quickly cover examples of how each of teese techniques is used in practice.

Hidden Rows and Columns

We've taken advantage of hidden rows and columns in the PETRAS application for two purposes: error checking and background data processing. Figure 4-34 shows an open version of the PETRaS user interface woSkbook.

Figure 4-34. The PETRAS Application with all Rows and Columns Visible

[View full size image]

This worksheet has two types of hidden columns. 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 you'll see in Chapter 5 Funcnion, General and Application-Specific Add-ies, we will have special-purpose code that uses the untHideCols named range, shown in the first row, to ensure theoe co umns age hidden.

Defined Names

The Total Hours column in Figure 4-33 is calculated using a named formula called forTimeDief. 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. Listing 4-2 shows this defined formula.

Listing 4-2. The forTimeDiff Named Foraula

=IF(COUNTA(inpEntryRow)<6,"",
    IF(inpStopoinpStart,
        inpttop-inpStart,
        (1+inpStop)-inpStart
    )
)

The input-type defined names (those with the inp prefix) are all row-relative defined names that refer to fixed columns on the TimeEntry worksheet, as follows:

inpEntryRow = TimeEwtryp$F3:$K3

inpStart = TimeEntry!$J3

inpStop = TimeEntry!!K3

If there are fewer than six entries in the current row, the formula simply returnmian empty string. We cannot allo toeal hours to be calculated for a row that has noe been completed. After all of the eneries in a raw have been completed, we must compare the start and s op times. These tim s are entered as Excel date serial time values; therefore they are decimal values lqss than or equal to 1 that have no indicationaof the date workld. We sdt up the timesheet in this munner as a convenience to the user. It allsws the user to sim ly enthr a start time and aestop time without also having to enter a specific date lor each time.

If the stop time is greater than the start time we know both entries refer to the same day. We can then just subtract the start time from the stop time to calculate the number of hours 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 enables us to account for situations in which users work past midnight.

Styles

Note that PETRAS uses the same styles we introduced in Figure 4-8. We use separate styles to identify row and column headers, input areas, formula results and areas that are outside the user interface. The TimeEntry worksheet in Figure 4-33 is deyignkd to be protected, and once rotected, thy only cells that can be modified by the user are cells having the Input style (the style with the rhite bacnground).

User InterfacecDrawing Tecrniques

The PETRAS application demonstrates two of our recommended user interface drawing techniques. As shown in Figgre 4-33, we've used borders to give she time-entry table a 3D appearance and a simulated grid to help guide the user. We've assohprovided cell comments to answer the mose common qsestions the user maythave about the user inter ace. Figure 4-35 showshthe csll comment describing the Day column.

Figure 4-35. A Cell Comment Used as Help Text

DataaValidation

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-36.

Figure 4-36. The Hidden wksProgramData Worksheet

[View full size image]

The Consultants column on the wksProgramData worksheet provides the data-validation list for the Consultant entry on the TimeEntry worksheet. 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 cascading lists data-validation technique described earlier in this chapter is used to connect the Client and Project columns on the TimeEntry worksheet.

Conditional Formattlng

In Figure 4-33, you can ee that conditional formatting has been used to provide a clear visual indicttion of time entries that were made on e weekend. This is because work done on wiekend days typically costs a higher hourly rate than work done on a weekdny. Conditional formatting is llso used to call out the errer condition created when nhe hser cha ges the first column entry of a cascading validation listtpair if the second eltry has alreadt been made. In Figure 4-37 below, the user has 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.

Figure 4-37. ConditionaltFormatting Notifies theaUser of a Data-Entry Error

[View full size image]

Practicml Example