Bizarre chart behaviour when changing axis cross at points

[Mr.Frog.to.you]

Hi Everyone,

I am building a chart that uses a type of quadrant background, where
the intersection of the quadrants is calculated. I have generated the
necessary data arrays with VBA, and place the data into the chart as
series in the seriescollection. So far so good. All works perfectly
until I hit one line of code in the following:

With c.Axes(xlCategory, xlPrimary) 'set the primary axes
correctly
.CategoryType = xlTimeScale
.Crosses = xlMaximum
End With
c.Axes(xlValue, xlPrimary).Crosses = xlMaximum

The last line of this code shifts the value axis to the top of the
chart, but in doing so seems to drop the first data series plot from
the graph. It is there right up to this line of code. When I hover the
mouse over this area where the plotted data used to be it shows as
Plot Area. There shouldnt be any plot are as the chart is a 100%
stacked column chart, and if you can believe this there is data ABOVE
where the first series should be plotted (the second data series), so
there is effectively a hole in the graph where the first data series
should show.

I have no idea how this came to be, but I can reproduce it at will. I
have tried deleting the series and re-adding them - no luck there. I
changed the order of the statements in the code above, and no luck
there either. Interestingly if you handle the value axis before you
handle the category axis you can observe the first data series
literally changing the location it is plotted against (this is
obscured when you change the category axis to timescale). The first
data series 'jumps' to the top of the chart, which is totally
unexpected, while the others remain where they are.

The data looks like the following:
x values: 0,0,34,34,34,100,100
series 1: 0,24,24,0,0,0,0
series 2: 0,76,76,0,0,0,0
series 3: 0,0,0,0,24,24,0
series 4: 0,0,0,0,76,76,0

Add the data to a chart, change the chart type to stacked 100% area
chart. Change the category axis to timescale and you should see a
'shifted' quadrant. If you use the code above step through it and you
will see the odd behaviour. When I did this by hand I got the right
result (although I had other data on that graph using the secondary
axes at the same time)

Any help or guidance would be greatly appreciated. I am using win Xp
SP3, and Office 2003 SP2.

Cheers

The Frog

 

[Jon Peltier]

If the disappearing series is a column or area type, keep in mind that
these types stretch from the axis to the value. Move the axis to the
top, and the baseline of the series moves to the top. If the series are
stacked, it is the bottom one in the stack that starts from the axis.

- Jon

 

[Mr.Frog.to.you]

Hi Jon,

Thanks for responding. What you have ssaid is most definitely true.
The issue here is that the position of the axis has changed but not
the order of the numbers on that axis. If I move the cross point for
the axis the scale and position on the values on that axis dont change
too. The values of Y are still the same, as are those for X. If the
problem you describe were happening it would apply to all the data
series and not just one of them. Only the first data series is
affected by this, and there are two columns as you can seee if you use
the data provided above as an example, and only the first column is
affected. If the stacking was going to behave diffferently then it
should affect all data series and both columns, but it does not! I am
chalking this up to a bug in the Excel chart object / object model. I
am having to work around it to solve the problem and although the
graph now looks right it is not the ideal solution to have the data
arranged this way.

Thanks for having a go though

Cheers

The Frog