hello,

I am looking forward coding a macro autoscaling a two vertical axis chart,
with matching major units.

This macro will be linked to the chart by an "OnClick" event

My questions in this context a

- How do you get the maximum and minimum values of a serie from a chart in
VBA, without referring to any sheet range ?

- How do you calculate the average and std dev of this serie? (to get the
major unit)

I already had a look at tons of forums and websites you suggested but nobody
developped that kind of macro, except in AddIns...but I do not want to use
AddIns ;-)

Thanks in advance

Use an approach like this.

Dim Xvals as Variant
Dim Yvals as Variant

Dim Xmax as Double
Dim Xmin as Double
Dim Xmean as Double
Dim Xdev as Double

Xvals = ActiveChart.SeriesCollection(i).XValues
Yvals = ActiveChart.SeriesCollection(i).Values

Xmax = WorksheetFunction.Max(Xvals)
Xmin = WorksheetFunction.Min(Xvals)
Xmean = WorksheetFunction.Average(Xvals)
Xdev = WorksheetFunction.StDev(Xvals)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Patachoup" wrote in message
...
hello,

I am looking forward coding a macro autoscaling a two vertical axis chart,
with matching major units.

This macro will be linked to the chart by an "OnClick" event

My questions in this context a

- How do you get the maximum and minimum values of a serie from a chart in
VBA, without referring to any sheet range ?

- How do you calculate the average and std dev of this serie? (to get the
major unit)

I already had a look at tons of forums and websites you suggested but
nobody
developped that kind of macro, except in AddIns...but I do not want to use
AddIns ;-)

Thanks in advance

Thx Jon,

I experience a conflict using a TWO vertical axis chart, I want to get the
following to correctly set the scales:

- Maximum value of ALL the series relative to the First Vertical Axis
- Minimum value of ALL the series relative to the Second Vertical Axis

I already use a test like: "If prim_sr.AxisGroup = xlPrimary Then..." to
check the corresponding axis to the serie...but I might not be the right code
:(

To sum up, I need to code:

For each Chartobject in activesheet
For each serie relative to the first vertical axis
Get the max value
Get the min value
For each serie relative to the second vertical axis
Get the max value
Get the min value



Thx in advance for giving me a hand ;-)


"Jon Peltier" wrote:

Use an approach like this.

Dim Xvals as Variant
Dim Yvals as Variant

Dim Xmax as Double
Dim Xmin as Double
Dim Xmean as Double
Dim Xdev as Double

Xvals = ActiveChart.SeriesCollection(i).XValues
Yvals = ActiveChart.SeriesCollection(i).Values

Xmax = WorksheetFunction.Max(Xvals)
Xmin = WorksheetFunction.Min(Xvals)
Xmean = WorksheetFunction.Average(Xvals)
Xdev = WorksheetFunction.StDev(Xvals)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Patachoup" wrote in message
...
hello,

I am looking forward coding a macro autoscaling a two vertical axis chart,
with matching major units.

This macro will be linked to the chart by an "OnClick" event

My questions in this context a

- How do you get the maximum and minimum values of a serie from a chart in
VBA, without referring to any sheet range ?

- How do you calculate the average and std dev of this serie? (to get the
major unit)

I already had a look at tons of forums and websites you suggested but
nobody
developped that kind of macro, except in AddIns...but I do not want to use
AddIns ;-)

Thanks in advance

Okay, you need to do a little looping:


Sub MinMaxPriSec()

Dim Yvals As Variant
Dim nSrs As Long
Dim iSrs As Long

Dim Ymax() As Double
Dim Ymin() As Double

Dim Ymax1 As Double
Dim Ymax2 As Double
Dim Ymin1 As Double
Dim Ymin2 As Double

Dim bPrimary As Boolean
Dim bSecondary As Boolean

Dim sMsg As String

nSrs = ActiveChart.SeriesCollection.Count

ReDim Ymax(1 To nSrs) As Double
ReDim Ymin(1 To nSrs) As Double

For iSrs = 1 To nSrs
Yvals = ActiveChart.SeriesCollection(iSrs).Values

Ymax(iSrs) = WorksheetFunction.Max(Yvals)
Ymin(iSrs) = WorksheetFunction.Min(Yvals)
Next

For iSrs = 1 To nSrs
Select Case ActiveChart.SeriesCollection(iSrs).AxisGroup
Case xlPrimary
If bPrimary Then
If Ymax(iSrs) Ymax1 Then
Ymax1 = Ymax(iSrs)
End If
If Ymin(iSrs) < Ymin1 Then
Ymin1 = Ymin(iSrs)
End If
Else
Ymax1 = Ymax(iSrs)
Ymin1 = Ymin(iSrs)
bPrimary = True
End If
Case xlSecondary
If bSecondary Then
If Ymax(iSrs) Ymax2 Then
Ymax2 = Ymax(iSrs)
End If
If Ymin(iSrs) < Ymin2 Then
Ymin2 = Ymin(iSrs)
End If
Else
Ymax2 = Ymax(iSrs)
Ymin2 = Ymin(iSrs)
bSecondary = True
End If
End Select
Next

If bPrimary Then
sMsg = "Primary Axis:" & vbNewLine
sMsg = sMsg & " Max = " & Ymax1 & vbNewLine
sMsg = sMsg & " Min = " & Ymin1 & vbNewLine
End If
If bSecondary Then
sMsg = sMsg & "Secondary Axis:" & vbNewLine
sMsg = sMsg & " Max = " & Ymax2 & vbNewLine
sMsg = sMsg & " Min = " & Ymin2 & vbNewLine
End If
sMsg = Left$(sMsg, Len(sMsg) - 1)

MsgBox sMsg

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Patachoup" wrote in message
...
Thx Jon,

I experience a conflict using a TWO vertical axis chart, I want to get the
following to correctly set the scales:

- Maximum value of ALL the series relative to the First Vertical Axis
- Minimum value of ALL the series relative to the Second Vertical Axis

I already use a test like: "If prim_sr.AxisGroup = xlPrimary Then..." to
check the corresponding axis to the serie...but I might not be the right
code
:(

To sum up, I need to code:

For each Chartobject in activesheet
For each serie relative to the first vertical axis
Get the max value
Get the min value
For each serie relative to the second vertical axis
Get the max value
Get the min value



Thx in advance for giving me a hand ;-)


"Jon Peltier" wrote:

Use an approach like this.

Dim Xvals as Variant
Dim Yvals as Variant

Dim Xmax as Double
Dim Xmin as Double
Dim Xmean as Double
Dim Xdev as Double

Xvals = ActiveChart.SeriesCollection(i).XValues
Yvals = ActiveChart.SeriesCollection(i).Values

Xmax = WorksheetFunction.Max(Xvals)
Xmin = WorksheetFunction.Min(Xvals)
Xmean = WorksheetFunction.Average(Xvals)
Xdev = WorksheetFunction.StDev(Xvals)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Patachoup" wrote in message
...
hello,

I am looking forward coding a macro autoscaling a two vertical axis
chart,
with matching major units.

This macro will be linked to the chart by an "OnClick" event

My questions in this context a

- How do you get the maximum and minimum values of a serie from a chart
in
VBA, without referring to any sheet range ?

- How do you calculate the average and std dev of this serie? (to get
the
major unit)

I already had a look at tons of forums and websites you suggested but
nobody
developped that kind of macro, except in AddIns...but I do not want to
use
AddIns ;-)

Thanks in advance

Thx Jon, it works fine.

I tried to make it easier for my CPU avoiding loops (I have hundreds of
charts to scale :)

I experience an error code 2042: t_prim_max = WorksheetFunction.max(sr.Values)
( see line ' ERROR bcs error 2042 )

I now need to use the Excel autoscale capability ONLY IF the above function
returns an error 2042 (because of #N/A in series, data is missing). to get BY
DEFAULT a more or less properly scaled chart.

My problem is that WorksheetFunction.max(sr.Values) returns Empty so my code
uses the last value given to t_prim_max :(

Ho do I avoid that without looping ? :-)

Thx a lot !



below is my code (novice code!) :
----------------------------

Sub scale_all()

step = 5 'entrer ici la Major Unit
Application.ScreenUpdating = False

For Each cht_obj In ActiveSheet.ChartObjects
cht_obj.Activate

On Error Resume Next

prim_max = -1000
prim_min = 1000
sec_max = -1000
sec_min = 1000

Set cht = ActiveChart

For Each sr In cht.SeriesCollection
If sr.AxisGroup = xlPrimary Then
t_prim_max = WorksheetFunction.max(sr.Values) ' ERROR bcs error 2042
t_prim_min = WorksheetFunction.min(sr.Values)
If t_prim_max prim_max Then
prim_max = t_prim_max
End If
If t_prim_min < prim_min Then
prim_min = t_prim_min
End If
With ActiveChart.Axes(xlValue)
.MinimumScale = prim_min
.MaximumScale = prim_max
.MinorUnitIsAuto = True
.MajorUnit = (prim_max - prim_min) / step
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End If
Next

For Each sr In cht.SeriesCollection
If sr.AxisGroup = xlSecondary Then
t_sec_max = WorksheetFunction.max(sr.Values)
t_sec_min = WorksheetFunction.min(sr.Values)
End If
If t_sec_max sec_max Then
sec_max = t_sec_max
End If
If t_sec_min < sec_min Then
sec_min = t_sec_min
End If
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = sec_min
.MaximumScale = sec_max
.MinorUnitIsAuto = True
.MajorUnit = (sec_max - sec_min) / step
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Next

Next
ActiveChart.Deselect
Application.ScreenUpdating = True

End Sub

-------------------

Your attempt to help your CPU by avoiding loops is misguided. The looping in
the macro I posted is insignificant.

If you have errors in the data, you should loop through the array, and skip
points with error values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Patachoup" wrote in message
...
Thx Jon, it works fine.

I tried to make it easier for my CPU avoiding loops (I have hundreds of
charts to scale :)

I experience an error code 2042: t_prim_max =
WorksheetFunction.max(sr.Values)
( see line ' ERROR bcs error 2042 )

I now need to use the Excel autoscale capability ONLY IF the above
function
returns an error 2042 (because of #N/A in series, data is missing). to get
BY
DEFAULT a more or less properly scaled chart.

My problem is that WorksheetFunction.max(sr.Values) returns Empty so my
code
uses the last value given to t_prim_max :(

Ho do I avoid that without looping ? :-)

Thx a lot !



below is my code (novice code!) :
----------------------------

Sub scale_all()

step = 5 'entrer ici la Major Unit
Application.ScreenUpdating = False

For Each cht_obj In ActiveSheet.ChartObjects
cht_obj.Activate

On Error Resume Next

prim_max = -1000
prim_min = 1000
sec_max = -1000
sec_min = 1000

Set cht = ActiveChart

For Each sr In cht.SeriesCollection
If sr.AxisGroup = xlPrimary Then
t_prim_max = WorksheetFunction.max(sr.Values) ' ERROR bcs error
2042
t_prim_min = WorksheetFunction.min(sr.Values)
If t_prim_max prim_max Then
prim_max = t_prim_max
End If
If t_prim_min < prim_min Then
prim_min = t_prim_min
End If
With ActiveChart.Axes(xlValue)
.MinimumScale = prim_min
.MaximumScale = prim_max
.MinorUnitIsAuto = True
.MajorUnit = (prim_max - prim_min) / step
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End If
Next

For Each sr In cht.SeriesCollection
If sr.AxisGroup = xlSecondary Then
t_sec_max = WorksheetFunction.max(sr.Values)
t_sec_min = WorksheetFunction.min(sr.Values)
End If
If t_sec_max sec_max Then
sec_max = t_sec_max
End If
If t_sec_min < sec_min Then
sec_min = t_sec_min
End If
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = sec_min
.MaximumScale = sec_max
.MinorUnitIsAuto = True
.MajorUnit = (sec_max - sec_min) / step
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Next

Next
ActiveChart.Deselect
Application.ScreenUpdating = True

End Sub

-------------------