Auto Scaling Charts
 

I am trying (in Vain) to auto scale bar charts in excel. I have attempted a
macro in VB to do this, but it just seems to do nothing, does not fail or
succeeds. If anyone could take a look at the macro below and assist in where
it is going wrong would be much appreciated.
Thanks

Sub AutoScaleCharts()

Dim ws As Worksheet
Dim cht As ChartObject
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer

For Each ws In ThisWorkbook.Worksheets
For Each cht In ws.ChartObjects
For Each X In cht.Chart.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 X
' Reset the minimum and maximum scale to the minimum and
' maximum values in the ValuesArray.
If cht.Chart.HasAxis(xlValue, xlPrimary) Then
cht.Chart.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlPrimary).MaximumScale =
Application.Max(ValuesArray)
ElseIf cht.Chart.HasAxis(xlValue, xlSecondary) Then
cht.Chart.Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlSecondary).MaximumScale =
Application.Max(ValuesArray)
End If
Next cht
Next ws

End Sub

Auto Scaling Charts
 

Hi,

Setting the value turns off the Auto scaling on the axis.

So in your code,

' autoscaling ON
cht.Chart.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
' autoscaling OFF
cht.Chart.Axes(xlValue, xlPrimary).MinimumScale =
Application.Min(ValuesArray)

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"gibsol" wrote in message
...
I am trying (in Vain) to auto scale bar charts in excel. I have attempted a
macro in VB to do this, but it just seems to do nothing, does not fail or
succeeds. If anyone could take a look at the macro below and assist in
where
it is going wrong would be much appreciated.
Thanks

Sub AutoScaleCharts()

Dim ws As Worksheet
Dim cht As ChartObject
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer

For Each ws In ThisWorkbook.Worksheets
For Each cht In ws.ChartObjects
For Each X In cht.Chart.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 X
' Reset the minimum and maximum scale to the minimum and
' maximum values in the ValuesArray.
If cht.Chart.HasAxis(xlValue, xlPrimary) Then
cht.Chart.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlPrimary).MaximumScale =
Application.Max(ValuesArray)
ElseIf cht.Chart.HasAxis(xlValue, xlSecondary) Then
cht.Chart.Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlSecondary).MaximumScale =
Application.Max(ValuesArray)
End If
Next cht
Next ws

End Sub

Auto Scaling Charts
 

Clarification:

Are you trying to autoscale chart axes, that is, let Excel apply the scale
based on its internal algorithms? Or do you mean you want to automatically
set it using the VBA procedure?

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


"gibsol" wrote in message
...
I am trying (in Vain) to auto scale bar charts in excel. I have attempted a
macro in VB to do this, but it just seems to do nothing, does not fail or
succeeds. If anyone could take a look at the macro below and assist in
where
it is going wrong would be much appreciated.
Thanks

Sub AutoScaleCharts()

Dim ws As Worksheet
Dim cht As ChartObject
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer

For Each ws In ThisWorkbook.Worksheets
For Each cht In ws.ChartObjects
For Each X In cht.Chart.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 X
' Reset the minimum and maximum scale to the minimum and
' maximum values in the ValuesArray.
If cht.Chart.HasAxis(xlValue, xlPrimary) Then
cht.Chart.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlPrimary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlPrimary).MaximumScale =
Application.Max(ValuesArray)
ElseIf cht.Chart.HasAxis(xlValue, xlSecondary) Then
cht.Chart.Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
cht.Chart.Axes(xlValue, xlSecondary).MinimumScale =
Application.Min(ValuesArray)
cht.Chart.Axes(xlValue, xlSecondary).MaximumScale =
Application.Max(ValuesArray)
End If
Next cht
Next ws

End Sub