Databases Reference
In-Depth Information
frm5Window.LabelMin = "Min: " & str(IRMin)
IRange = IRMax - IRMin
'Determine Center Point
CtrPt = ((IRMax - IRMin) / 2) + IRMin
frm5Window.LabelCtr = "Center: " & str(CtrPt)
End Sub
Notice that, if the range is to span the entire data range of the selected column, it is necessary
to have a means to find both the minimum and maximum values present within that column. This
can easily be done utilizing the following two functions.
Function MaxValinColX(Workbook, Worksheet, columnX As Integer)
MaxValinColX =
Application.Max(Workbooks(Workbook).Worksheets(Worksheet).Ran
ge(Cells(1, columnX), Cells(Rows.Count, columnX)))
End Function
Function MinValinColX(Workbook, Worksheet, columnX As Integer)
MinValinColX =
Application.Min(Workbooks(Workbook).Worksheets(Worksheet).Ran
ge(Cells(1, columnX), Cells(Rows.Count, columnX)))
End Function
The CalcWindow() subroutine will only be called once if the window is to be centered around
a static data point. If the window is to be centered around a dynamic data point that will change
with every row in the column, a means must be put in place to change the center point for each
successive value in the column. For dynamic data pointing, the row and column the starting point
pointed to by the RefEdit control must be determined. For both the static and dynamic cases, the
following snippet of code is utilized:
'Center Value (CVal) will Change!
If OBDynamic.Value = True Then
RefEdCol =
ColNoInRange
(RefEdit_Cell.Text)
RefEdRow =
RowNoInRange
(RefEdit_Cell.Text)
Else
'Static, Calculate Parameters Only Once
Call CalcWindow(CVal, RVal, IRMax, IRMin, IRange, CtrPt)
End If
Notice that, for the static case, the
CalcWindow
subroutine is called once to establish the
parameters
IRMax,
IRMin,
IRange,
and
CtrPt
. In the dynamic instance, the
CalcWindow
subroutine must be called multiple times for each successive change in the center value. In order
to accomplish this, the macro needs to determine the starting row and column of the initial data
point. The following two functions are able to return this information.
Function ColNoInRange(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)
ColNoInRange = CtrlRange.Column
