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