automatic scale a chart

[Claus Haslauer]

Hey,
I automatically want the range of a chart to be adjusted to the max and
min values of Series1 (only one series, not all of them).
I adapted the ms template (http://support.microsoft.com/?kbid=213644) to
work for both x and y axis, but it still draws the max and min from ALL
series.

VBA doesn't like
For Each X In .SeriesCollection(1)
And if I specify:
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
I'm running into problems in the For Each X In ... line

Thanks for your help,
Claus

Here's the code:

Private Sub SetScale_Click()
'Dim Xs
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer

'Dim Ys
Dim ValuesArrayY(), SeriesValuesY As Variant
Dim CtrY As Integer, TotCtrY As Integer


' Uses the first chart on the active worksheet.
With ActiveSheet.ChartObjects(1).Chart
' Note: Instead of the preceding line, you could use this line:
' With ActiveChart
' if you wanted to be able to run this macro on a chart sheet.
' Loops through all of the Series and retrieves the values
' and places them into an array named ValuesArray.
For Each X In .SeriesCollection
SeriesValues = X.Values
ReDim Preserve ValuesArray(1 To TotCtr + Ubound(SeriesValues))
For Ctr = 1 To UBound(SeriesValues)
ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
Next
TotCtr = TotCtr + UBound(SeriesValues)
Next

For Each Y In .SeriesCollection
SeriesValuesY = Y.XValues
ReDim Preserve ValuesArrayY(1 To TotCtrY +
UBound(SeriesValuesY))
For CtrY = 1 To UBound(SeriesValuesY)
ValuesArrayY(CtrY + TotCtrY) = SeriesValuesY(CtrY)
Next
TotCtrY = TotCtrY + UBound(SeriesValuesY)
Next


' Reset the minimum and maximum scale to the minimum and
' maximum values in the ValuesArray.
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
.Axes(xlValue).MaximumScale = Application.Max(ValuesArray)

.Axes(xlCategory).MinimumScaleIsAuto = True
.Axes(xlCategory).MaximumScaleIsAuto = True
.Axes(xlCategory).MinimumScale = Application.Min(ValuesArrayY)
.Axes(xlCategory).MaximumScale = Application.Max(ValuesArrayY)
End With
End Sub

 

[Jon Peltier]

No offense, but that's some ugly code. I mean, who uses a variable called
SeriesValuesY to hold X values??

SeriesValuesY = Y.XValues

This works on the first chart, based on the first series:

Private Sub SetScale_Click()

Dim Srs As Series


Dim SeriesValues As Variant

Dim SeriesXValues As Variant



' Uses the first chart on the active worksheet.

With ActiveSheet.ChartObjects(1).Chart



Set Srs =.SeriesCollection(1)

SeriesValues = Srs.Values

SeriesXValues = Srs.XValues



.Axes(xlValue).MinimumScale = Application.Min(SeriesValues)

.Axes(xlValue).MaximumScale = Application.Max(SeriesValues)



.Axes(xlCategory).MinimumScale = Application.Min(SeriesXValues)

.Axes(xlCategory).MaximumScale = Application.Max(SeriesXValues)

End With

End Sub



To work on the active chart (a selected chart in a worksheet, not just a
chart sheet as the code comment helpfully but inaccurately says), replace
this line:



With ActiveSheet.ChartObjects(1).Chart



With this line:



With ActiveChart



- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______