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

Practical Example

In this section we begin building a real-world Excel application that illustrates the points made in the chapter text. Our application will be a time-tracking system that will start as a simple, no-frills timesheet and work its way up to being a full-featured Excel application as we progress through the book. 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 by perusing the finished sample of the application that is available on the accompanying CD. This timesheet application will henceforth be referred to by its acronym PETRAS, which stands for Professional Excel Timesheet Reporting and Analysis System.

The first version of PETRAS will be a simple workbook containing a time-entry table on one worksheet and data-validation lists on a second hidden worksheet. The user will be expected to complete the time-entry table each week and manually copy the workbook to a central location for consolidation. You can find this version of PETRAS on the accompanying CD in the \Application\Ch04-Worksheet Design\ folder. It is displayed in Figure 4-33.

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

[View full size image]

Most of the user interface design techniques that have been discussed in this chapter have been used in the PETRAS application, including all variations of defined names, styles to differentiate areas by purpose, table formatting techniques, use of comments for help text, data validation and conditional formatting. Let's quickly cover examples of how each of these 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 workbook.

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

Chapter 5 Function, General and Application-Specific Add-ins, we will 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-33 is calculated using a named formula 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. Listing 4-2 shows this defined formula.

Listing 4-2. 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-relative defined names that refer to fixed columns on the TimeEntry worksheet, as follows:

inpEntryRow = TimeEntry!$F3:$K3

inpStart = TimeEntry!$J3

inpStop = TimeEntry!$K3

If there are fewer than six entries in the current row, the formula simply returns an empty string. We cannot allow total hours to be calculated for a row that has not been completed. After all of 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 decimal values less than or equal to 1 that have no indication of the date worked. We set up the timesheet 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 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 designed to be protected, and once protected, the only cells that can be modified by the user are cells 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-33, we've used borders to give the time-entry table a 3D appearance and a simulated grid to help guide the user. We've also provided cell comments to answer the most common questions the user may have about the user interface. Figure 4-35 shows the cell comment describing the Day column.

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

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-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 Formatting

In Figure 4-33, you can see that conditional formatting has been used to provide a clear visual indication of time entries that were 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 cascading validation list pair if the second entry has already 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. Conditional Formatting Notifies the User of a Data-Entry Error

[View full size image]