Setting Chart MajorUnit for Clean lookng Chart

Hi,

I'm hoping a chart guru can help me solve this pesky problem. I have load
of charts that are dynamic (mostly single series) and I'm trying to finish my
code that will adjust the Y-axis and major unit so that the data will
maximize the chart area as best as possible and have clean gridlines of
approx. 5 per chart (including the zero line if both + & - values)

I have been able to read/write the chart's max/min values as well as set the
scaling, but my problem lies in the fact that I can't seem to adjust these
values and then set a MajorUnit so that the gridlines all appear nice and
neat , especially at the Max Scale and around 0 (if both pos and neg).

For example, my code will round Max/Min values up by the nearest 5,10,20 etc
depending on the max level in the chart, so in my example a series with a max
value of 29.1, the max scale is set at 40 (nearest 10, plus 10). THe series
min value is -42.3 and the Min scale gets set to -50.

In order to have around 5 gridlines the majorunit was set at 20. My current
non-working method has been to take the following:

MajUnit = (Abs(Newmax) + Abs(Newmin)) / 5

which I have tried to round to a more suitable number. However, the chart
in this example shows major units at -50, -30, -10, 10, 30 and then there is
space above the y-axis above the last gridline. A more suitable scaling
would really be min = -50, max = 50 and major unit = 25. Not totally maxing
out the space, but close enough.

There are so many charts that fine tuning this by hand is a nightmare. I
just can't get the code to set a max/min scale that will have the clean grids
I'm looking for.

I know this is too long, but if anyone out there has any guidance I would
greatly appreciate it.

Regards,
jbl

Good morning jbl,


to adjust the y-axis within a chart you can use the
following code:

With Sheets("xyz").ChartObjects("Chart 1").Axes(xlValue)
.MinimumScale = minvalue
.MaximumScale = maxvalue
.MinorUnit = minunit
.MajorUnit = majunit
.Crosses = xlCustom
.CrossesAt = crosspoint
End With

all variables used above need to be defined before...
to get the integer part of a multiplication or division use

Int((Newmax + Newmin) / 5)


Hope that helps a bit.

Best

Markus

>-----Original Message-----
>Hi,
>
>I'm hoping a chart guru can help me solve this pesky
problem. I have load
>of charts that are dynamic (mostly single series) and I'm
trying to finish my
>code that will adjust the Y-axis and major unit so that
the data will
>maximize the chart area as best as possible and have
clean gridlines of
>approx. 5 per chart (including the zero line if both + & -
values)
>
>I have been able to read/write the chart's max/min values
as well as set the
>scaling, but my problem lies in the fact that I can't
seem to adjust these
>values and then set a MajorUnit so that the gridlines all
appear nice and
>neat , especially at the Max Scale and around 0 (if both
pos and neg).
>
>For example, my code will round Max/Min values up by the
nearest 5,10,20 etc
>depending on the max level in the chart, so in my example
a series with a max
>value of 29.1, the max scale is set at 40 (nearest 10,
plus 10). THe series
>min value is -42.3 and the Min scale gets set to -50.
>
>In order to have around 5 gridlines the majorunit was set
at 20. My current
>non-working method has been to take the following:
>
>MajUnit = (Abs(Newmax) + Abs(Newmin)) / 5
>
>which I have tried to round to a more suitable number.
However, the chart
>in this example shows major units at -50, -30, -10, 10,
30 and then there is
>space above the y-axis above the last gridline. A more
suitable scaling
>would really be min = -50, max = 50 and major unit = 25.
Not totally maxing
>out the space, but close enough.
>
>There are so many charts that fine tuning this by hand is
a nightmare. I
>just can't get the code to set a max/min scale that will
have the clean grids
>I'm looking for.
>
>I know this is too long, but if anyone out there has any
guidance I would
>greatly appreciate it.
>
>Regards,
>jbl
>.
>