Databases Reference
In-Depth Information
End Function
Function RowNoInRange(RefEditTxt) As Single
'Pass the Text within the RefEdit Control. Will Return the
Column Number
'Set CtrlRange Range object to the range specified in the
RefEdit control.
Set CtrlRange = Range(RefEditTxt)
RowNoInRange = CtrlRange.row
End Function
The last and most important phase of this example is the methodology required to actually
implement the color mapping scheme. The data that will be mapped will encompass a window,
which will have a minimum and a maximum value. The window will also obviously have a center
point, which, in this macro, is stored in the variable
CtrPt
. The closer a discrete value is to this
center point, the closer its shade will match the ideal (or initial) color. The closer a discrete value
is to either extreme (the maximum or minimum value of the window), the closer its shade will
match the final color. Recall that all possible colors in the color step range are stored in the
ColorRange()
array. What is required now is a formula that calculates the deviation from the
ideal value (or center point
CtrPt
), which can be applied in a meaningful way to the color step
range to determine what color should be assigned to a particular discrete point. Such a formula
has been derived in Equation 5.3.
Abs CtrPt current value
IRange
(
−
)
deviation from ideal
=
(5.3)
(
/ )
2
To demonstrate how to apply Formula 5.3, an example case will be shown. Suppose the Interval
Range
(IR)
is from 80 to 120, in which case the center point
(CtrPt)
would be 100 and the
Interval Range
(IRange)
would be 40 (the width of the interval or 120-80).
Table 5.7 can very easily be constructed by simply constructing a Worksheet with the numbers
80-120 in column A and then applying the following formula to the first row of column B and
dragging it down.
=
ABS(((MEDIAN($A$2:$A$42)-A2)/((MAX($A$2:$A$42)-MIN($A$2:$A$42))/2)))
Upon careful inspection, note that this formula is, in reality, identical to that of (5.3). (Remember
that the $ notation indicates that cell references will be fixed when dragged. In this instance, the only
reference that will vary is one that references the current value.) Also notice that, in Table 5.7, the
numbers closest to the center point (100) are green, and as values progress toward either extreme,
they change toward red (i.e., green->yellow->orange->red). Further notice in Table 5.7 that the
formula returns a value of 0 at the ideal value (or center of the window) and that this value
incrementally increases and reaches a limit of 1 at either extreme of the window spectrum. A value
of 0 therefore denotes a 0% deviation from the ideal value, whereas a value of 1 indicates a 100%
deviation (within the allowable window) from the ideal value.
Although Formula 5.3 works fine for a linear stepwise progression of values over the designated
window, a more complicated formula must be devised for logarithmic treatment of data. Often,
what is desired is to maintain the ideal colors over a wider interval around the center point and
then proceed toward the nonideal colors quite rapidly at the two extreme edges of the window (as
shown in the top part of Figure 5.28).
Looking at Figure 5.30, notice that taking the log of numbers 1-10 produces a scale from 0-1
with the spacing desired. What is required for this application is an algorithm to convert the linear
