Thread: automatic scale a chart

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

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
_______

"Claus Haslauer" <chaslaue@scimail.uwaterloo.ca> wrote in message
news:du7d2j$rcd$1@rumours.uwaterloo.ca...
> 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