Getting The Maximum Of An Excel Chart's Y Axis (When There Are Two)

I would like to know how to programmatically find and the Y axis 开发者_运维知识库maximum of an excel chart when there is more than one available.

My end goal is to find the max y-axis values, compare them, and set them both to the greater of the two.

VBA similar to this will retrieve scale value

    With ActiveChart.Axes(xlValue, xlPrimary)
        ActiveSheet.Range("A1").Value = .MaximumScale
    End With

For more detail on how to link chart axis scale parameters to values in cells check out

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html#ixzz0r8qN248l

If you want both axes to automatically show the same min and max values, you need both to plot the same spread of data.

In this simple example, I used the following data to create a chart, with "primary" plotted on the primary axis and "secondary" plotted, well, you know.

In the next table, I've calculated the min and max of all the data. I calculated min and max twice and staggered them to clearly show what I'm doing. I copied the shaded range, celected the chart, and used Paste Special to add the data as new series, in columns, series names in first row.

The resulting chart is shown below left. I've ensured the the new series "pri" is on the primary axis and "sec" is on the secondary axis. Since both axes are using the same min and max data to autoscale, both have the same scales. Below right I've hidden the dummy series by formatting them with no lines and no markers. I've hidden each unwanted legend entry by first clicking on the legend, then clicking on the individual legend entry, then pressing Delete.

This technique can also be used to synchronize the axes of multiple charts. Below are three small charts with different scales (top row). In the middle row, the calculated min and max have been added to each chart, causing their axes to autoscale on the same min and max. In the bottom row, the dummy series have been hidden, leaving behind uniform axis scales.