Code about setting Y Axes scale.

[fujing1003]

I couldn't understand the lines between 35-42, why he thought the
dbMinScale after the change is the one he want. What the logic of
this code section. Any one who could understand please help me. thanks
in advance!


Private Sub USP_SetYAxesScal(ByVal i_strWSName As String, ByVal
i_strChartName As String, Optional ByVal i_intDecimal As Integer = 2)

Dim dbMaxScale As Double, dbMinScale As Double, i As Integer, j
As Integer
Dim xTargetChart As Chart, vTemp() As Variant, blnInit As
Boolean, dbMax As Double, dbMin As Double
1 On Error Resume Next

'get max/min of chart-values
2 dbMax = 0: dbMin = 0: blnInit = False
3 Set xTargetChart =
Sheets(i_strWSName).ChartObjects(i_strChartName).Chart
4 For i = 1 To xTargetChart.SeriesCollection.Count

'************* ERR ***************
5 vTemp = xTargetChart.SeriesCollection(i).Values


'xTargetChart.SeriesCollection(i).Points
6 If UFG_IsArray(vTemp) Then
7 If blnInit = False Then
8 For j = LBound(vTemp) To UBound(vTemp)
9 If IsNumeric(vTemp(j)) Then
10 dbMax = vTemp(j)
11 dbMin = vTemp(j)
12 blnInit = True
13 Exit For
14 End If
15 Next j
16 End If
17 For j = LBound(vTemp) To UBound(vTemp)
18 If IsNumeric(vTemp(j)) Then
19 If vTemp(j) > dbMax Then
20 dbMax = vTemp(j)
21 End If
22 If vTemp(j) < dbMin Then
23 dbMin = vTemp(j)
24 End If
25 End If
26 Next j
27 End If
28 Next i

'if data err... eg.min or max <0
29 If dbMin < 0 Then
30 dbMin = 0
31 End If
32 If dbMin >= dbMax Then
33 dbMax = dbMin + 1
34 End If

'get min/max of Yscale
35 dbMaxScale = Round(dbMax * 1.1) + 1
36 dbMinScale = dbMin - dbMax * 0.1
37 If dbMinScale < 0 Then
38 dbMinScale = 0
39 Else
40 dbMinScale = (dbMinScale \ 5) * 5
41 End If
42 dbMinScale = Round(dbMinScale, i_intDecimal)

'set chart
43 With xTargetChart.Axes(xlValue)
44 .MinimumScale = dbMinScale
'.MaximumScale = dbMaxScale
45 .MaximumScaleIsAuto = True
46 .MinorUnitIsAuto = True
47 .MajorUnitIsAuto = True
48 .Crosses = xlCustom
49 .CrossesAt = dbMinScale
50 .ReversePlotOrder = False
51 .ScaleType = xlLinear
52 .DisplayUnit = xlNone
53 End With

54 Set xTargetChart = Nothing
55 Erase vTemp

56 On Error GoTo 0
57 Exit Sub
USP_SetYAxesScal_Error:
58 UFG_ErrHandler ERR_PROGRAMMERR,
"clsCP_WorkBookBuilder.USP_SetYAxesScal", ERR_PROGRAMMERR_textID
'USG_TraceError uvg_xOwnTrace,
"clsCP_WorkBookBuilder","USP_SetYAxesScal"
End Sub

 

[Bob Phillips]

Not sure that I really understand your question, but it seems to be just a
simple bit of code to work out the min and max axis scales.

Line 42 does seem odd though

42 dbMinScale = Round(dbMinScale, i_intDecimal)

as it sets the Min scale to a parameterised number of decimal places, but
this

37 If dbMinScale < 0 Then
38 dbMinScale = 0
39 Else
40 dbMinScale = (dbMinScale \ 5) * 5
41 End If

integerises it, to the nearest 5.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[fujing1003]

Not sure that I really understand your question, but it seems to be just a
simple bit of code to work out the min and max axis scales.

Thanks for your answer. line 42 seems to be redundant. line 40 always
result out an integer by use"\".
Yes, its purpose is to get the appropriate min axis scale. the code is
simple,
but there some logic hide behind hide the code, or what's logic the
code rely on? and maybe some knowledge about axis scale modify
principle.
'--------------------------------------------------------------------

35 dbMaxScale = Round(dbMax * 1.1) + 1
36 dbMinScale = dbMin - dbMax * 0.1
37 If dbMinScale < 0 Then
38 dbMinScale = 0
39 Else
40 dbMinScale = (dbMinScale \ 5) * 5
41 End If
42 dbMinScale = Round(dbMinScale, i_intDecimal)
'--------------------------------------------------------------------

 

[Bob Phillips]

I agree, that is what I said in my response.

I just don't understand this bit

.... but there some logic hide behind hide the code, or what's logic the code
rely on? and maybe some knowledge about axis scale modify principle.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)