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 2 ways to go about this:

i) Understand how excel calculates the maximum Y axis value and match
this value. (Currently I am using MAX function)
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

 

[Jon Peltier]

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).

Does it make sense to try a stacked 100% area on the secondary Y axis? Then
you will always match. Even if you make the value match exactly, Excel will
then increase the axis maximum, so your regular stacked area series won't
match it any more. Of course, the problem becomes having to choose
percentages for the area chart values (on the secondary axis) that will line
up with the primary axis.

I assume that you can't impose your own axis limits, because different
periods have values that are too different to use the same limits.

I have 2 ways to go about this:

i) Understand how excel calculates the maximum Y axis value and match
this value. (Currently I am using MAX function)

Microsoft published an article that explains how the axis limits are
selected, based on multiples of the major tick spacing. It never reveals how
this tick spacing is arrived at, so the article is of limited use.

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.

No, you don't have to activate anything. You can refer to an axis on a chart
on a different workbook's sheets without making it the active workbook. You
only (only!) need to know how to qualify the chart:

Workbooks("Book1.xls").Worksheets("Sheet 2").ChartObjects("Chart
3").Chart.Axes(xlCategory, xlPrimary)

iii) Define your own values for min and max, based on any algorithm you
want, and apply them to the chart. For a decent algorithm, check Stephen
Bullen's old post:

http://groups.google.com/group/microsoft.public.excel.charting/msg/8a13d5257af6a8c7?hl=en&lr=

I've pinched this technique and applied it to worksheet formulas, rather
than a VBA UDF. For the ability to apply cell values to chart axis scales,
read this article:

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

This procedure to change the axis scale can be linked to a Worksheet_Change
or Worksheet_Calculate event, so it will behave like Excel's built in
dynamic axis scales. Except of course that you get to choose the limits.

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