Excel 2007 - Legend.Position

[Barb Reinhardt]

I have the following code that sets the Legend position for a chart object

For Each objCht In WS.ChartObjects
With objCht.Chart
.Legend.Position = xlLegendPositionBottom
End With
Next

It stops on the .legend.position line with the error

Run-time error '-217467259 (80004005)':

Method 'Position' of object 'Legend' failed.

But then I can step right through the code without a problem. What gives?

Thanks,
Barb Reinhardt

 

[Jon Peltier]

Excel 2007 seems to have to think a bit when working on charts. Stepping
through gives it plenty of time. Insert DoEvents above the offending line of
code.

- Jon

 

[Bill Fazakerly]

Excel 2007 has to think a bit on everything. If you are automating, you should disable screen updating and calculation until every cell has been entered, then enable calculation, calculate, and make the app visible.



Jon Peltier wrote:

Excel 2007 seems to have to think a bit when working on charts.
19-Sep-08

Excel 2007 seems to have to think a bit when working on charts. Stepping
through gives it plenty of time. Insert DoEvents above the offending line of
code

- Jo
------
Jon Peltier, Microsoft Excel MV
Tutorials and Custom Solution
Peltier Technical Services, Inc. - http://PeltierTech.co
______


Previous Posts In This Thread:

Excel 2007 - Legend.Position
I have the following code that sets the Legend position for a chart objec

For Each objCht In WS.ChartObject
With objCht.Char
.Legend.Position = xlLegendPositionBottom
End Wit
Nex

It stops on the .legend.position line with the erro

Run-time error '-217467259 (80004005)'

Method 'Position' of object 'Legend' failed.

But then I can step right through the code without a problem. What gives

Thanks
Barb Reinhardt

Excel 2007 seems to have to think a bit when working on charts.
Excel 2007 seems to have to think a bit when working on charts. Stepping
through gives it plenty of time. Insert DoEvents above the offending line of
code

- Jo
------
Jon Peltier, Microsoft Excel MV
Tutorials and Custom Solution
Peltier Technical Services, Inc. - http://PeltierTech.co
______



Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorial...-a2da-88dde2e6d891/book-review-c-40-in-a.aspx

 

[Martin Brown]

Excel 2007 has to think a bit on everything. If you are automating, you should disable screen updating and calculation until every cell has been entered, then enable calculation, calculate, and make the app visible.

Excel2007 is very slow on charts. You have to be exceedingly careful to
give it enough time to instantiate objects before referring to them. I
usually see it with axes or legend manipulation after chart creation.

This might be me being superstitious, but it seems to be worse on quad
core systems than on dual core (but CPU speeds are also different).

Essentially what Barb describes is a race condition where the code is OK
in the debugger but will not work at full speed because some object or
other isn't yet initialised when the main thread reaches it. DoEvents or
a humble waste time delay loop will usually make these work. In some
cases you can single step debug on from the notional point of failure
since the object will have had plenty of time to initiallise by then.

You can guess that the Charting code is liberally sprinkled with such
incantations based on its glacial slowness with moderate size datasets.

Regards,
Martin Brown

Jon Peltier wrote:

Excel 2007 seems to have to think a bit when working on charts.
19-Sep-08

Excel 2007 seems to have to think a bit when working on charts. Stepping
through gives it plenty of time. Insert DoEvents above the offending line of
code.

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



Previous Posts In This Thread:

Excel 2007 - Legend.Position
I have the following code that sets the Legend position for a chart object

For Each objCht In WS.ChartObjects
With objCht.Chart
.Legend.Position = xlLegendPositionBottom
End With
Next

It stops on the .legend.position line with the error

Run-time error '-217467259 (80004005)':

Method 'Position' of object 'Legend' failed.

But then I can step right through the code without a problem. What gives?

Thanks,
Barb Reinhardt

Excel 2007 seems to have to think a bit when working on charts.
Excel 2007 seems to have to think a bit when working on charts. Stepping
through gives it plenty of time. Insert DoEvents above the offending line of
code.

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




Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorial...-a2da-88dde2e6d891/book-review-c-40-in-a.aspx