Function to retrieve maximum value of Y axis scale in chart object

[Lawrence.Colombo]

Hello All

Ok. So the problem is the following.

I have a simple line chart with 4 series. One of them is the actual
data series and the other 3 series represent the target areas
(formatted as stacked area type).

Now I have created dynamic ranges so that users may select a period and
the graph only displays the selected period. However I cant seem to get
the toped stacked layer (i.e. Bad performance area series) to match the
maximum value within the Y axis. (It's always sightly less).

I have to ways to go about this:

i) Understand how excel calculates the maximum Y axis value and match
this value
OR
ii) Retrieve the maximum value by creating a UDF in vba, however this
seems to be tricky as in vba I have to first activate the ChartObject
and then the Chart ... etc.

Any suggestions or code or full answer would be greatly appreciated.

Thanks

L

 

[Guest]

Unless you manually set the value axis maximum you will still have problems,
otherwise here is what happens:
1) your data in chart is updated (including the top of your bad performance
series)
2) Excel looks at the new data and rescales the chart
3) Your formula updates to calculate or read the maximum y value from the
rescaled chart
4) The top of your bad performance series gets recalculated
5) Excel sees the change in the chart data and rescales the chart
6) Go back to step 3 and repeat ad infinitum...

So actually it would be better to set your values (i.e the top of the bad
performance series) the way you want it and then manually scale the chart.
You can do that automatically through code as shown below in SetMaxYScale(),
but just in case you need it I am including ReadMaxYScale() that shows how
you can fid what the max Y scale is:

Public Function ReadMaxYScale(SheetName As String, ChartName As String)
' SheetName is the name of the worksheet the chart is on
' use the ChartObject name, e.g. "Chart1", as ChartName
Dim MyChart As Chart
On Error GoTo Err
Set MyChart = Worksheets(SheetName).ChartObjects(ChartName).Chart
ReadMaxYScale = MyChart.Axes(xlValue).MaximumScale
Exit Function
Err:
Err.Raise 9000, "ReadMaxYScale()"
End Function

Public Sub SetMaxYScale(SheetName As String, ChartName As String, YValue As
Variant)
Dim MyChart As Chart
On Error GoTo Err
Set MyChart = Worksheets(SheetName).ChartObjects(ChartName).Chart
MyChart.Axes(xlValue).MaximumScale = YValue
Exit Sub
Err:
Err.Raise 9000, "SetMaxYScale()"
End Sub