Databases Reference
In-Depth Information
The only disadvantage to utilizing such a procedure is that the range must be
selected
prior to
performing the action on the cells within the range, whereas in the previous instance, the range is
merely
specified,
and the actions are performed upon the
specified
range without the need for
selecting
it.
These two sample applications provide the building blocks required to develop more sophisti-
cated data mining procedures, which will be discussed in the subsequent sections of this chapter.
5.6
CREATING A HIGHLIGHT IF TOOL
The next logical step is to create some applications that will highlight entries in a Worksheet should
they meet a specified criteria. Some examples have been created that perform a variety of useful
functions. The next sample application will highlight the background of a cell to any color the user
specifies, provided certain criteria are met. The thought process that went on before a single line
of code was written to produce this application will now be discussed.
First, the user may not wish to perform the conditional highlighting over the entire Worksheet.
Therefore, a mechanism should be in place to let the user decide whether to format the entire sheet
or a portion of the sheet. Because most relational databases export data with each column containing
a single parameter, it would be wise to allow the user the option of limiting the conditional
highlighting to just one column.
Next comes what criteria should be checked. Probably, the often-sought comparisons would
be those that are numerical in nature. Therefore, it would make sense to compare the contents of
a cell to some value and highlight the cells' background if the cells' value were equal to, greater
than, less than, or some combination thereof of the selected value. Thus, the operators
=
,<> should be utilized.
This brings about the question: how should the value be selected for comparison to these operators?
Two possibilities come to mind for selection of a comparison value. The value for comparison could
exist within a Worksheet and thus be pointed to by means of a range. The value could also be a discrete
value that the user could type into a GUI. This application makes provisions for both instances.
With the foregoing considerations in mind, it is now possible to construct a GUI that will allow
the user to set the above parameters. Looking at Figure 5.14, the user is prompted as to whether the
formatting should be applied to the entire sheet or just a particular column. This is done by means
of the option buttons contained in the “Look In” frame. If the user elects to just apply the formatting
to a single column, the column to be acted on is specified by means of a spin button that increments
or decrements the column to be acted on in a textbox immediately adjacent to the spin button.
The color
to be utilized for highlighting the cells' background is specified in exactly the same
manner as in the previous highlight cell background sample application.
,>,>
=
,<,<
=
FIGURE 5.14
The Highlight If GUI.
