ChartArea.left reporting incorrectly?

  • Thread starter Thread starter Matt McQueen
  • Start date Start date
M

Matt McQueen

Folks,

A few moons ago I posted this query:

http://www.pcreview.co.uk/forums/thread-3767566.php

Eric/egun's response allowed me to accomplish what I was after using the code:

..Parent.Left = xLoc - ActiveChart.ChartArea.Left -
ActiveChart.PlotArea.InsideLeft

However I've now run into an issue whereby the activechart.chartarea.left
report is incorrect. After creating 9 plots with .chartarea.left = 3, it then
produces two plots with .chartarea.left = 118. Since my goal is to align my
primary y-axis to the right-hand edge of column B, this poses a problem. The
effective code becomes:

..Parent.Left = 96 - 118 - ActiveChart.PlotArea.InsideLeft

which is less than zero. The chart is still created, just shifted to the far
left of the screen.

Interestingly, if I break the code and step through - voila, no problem!

Anybody seen this one before?

Cheers,

Matt
 
"Interestingly, if I break the code and step through - voila, no problem!"

When this happens it often means Excel gets ahead of itself, and really
needs to let Windows do some cleaning up before it executes that statement.

You can make this happen by inserting this line before any command that
works only when stepping through:

DoEvents

- Jon
 
Interesting tip Jon, thanks for that. I employed it prior to the With - End
With statement I was using on each successive plot to format and orient it.
Previously it was misalinging the final two plots created... with DoEvents it
only misaligned the last plot. I added a DoEvents immediately after the
realignment as well, and Excel turned ScreenUpdating back on (!). Figured I
was in over my head at that point.

In the end I've hardcoded the numbers since they'll nearly always be
constant. Thanks anyway.
 
Matt, set Application.Screenupdating = True. I have seen a number of times
that Excel must update the chart before a property can be properly obtained.
By stepping through the code, you are in effect allowing the chart to
update, which is why it works when stepping through.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Back
Top