laura_in_abq wrote:
Bin, you're comment "re-write your macro based on recorded macro in Office
2007", I've found the macro recorder ignores (doesn't record) in some casees,
for instance a manipulation of a chart objects, for instance text box. Your
thoughts?

XL2007 macro recorder is completely f*cked.

There was a time when you could tell people to capture a macro and they
would have a sporting chance of getting something useful to them with
just a few minor tweaks. But not any more :( That is progress for you.

MS MVPs are strangely silent on these problems.

"Bin" wrote:

You are not only one to have this problem. It is caused by Chart. I have a
similar Macro that divided 60K+ rows data to create 60+ charts. In Office
2003, it takes about 2-3 minutes. But in Office 2007, take me 2 more hours.
The only thing you can do is to re-write your macro based on recorded macro
in Office 2007. You will find there are many differences. I did it to
modified my macro and speed it up to almost same as in Office 2003. Try it.

Not true. Even when optimised charts in XL2007 are typically one or two
*orders of magnitude* slower than in XL2003/2. They have improved a bit
from being glacially slow in the original out of the box version.

By default they look like they were drawn by a 3 year old with a thick
wax crayon. Change the default line settings and it gets even slower. It
really only hurts on moderate to large datasets.

There are also curious race conditions where if you are out of luck it
will try to adjust the axes before it has drawn them. I reckon at least
some of the glacial slowness can be traced to bodge around code that
adds fixed delays in the hope that things will be ready. Seems to be
more of an issue on quad CPUs with large datasets.

You will lose nothing but learn more about Office 2007.
Good luck.

"JohnJohn" wrote:

Thanks for providing me with an idea of where the bottleneck is (charts).
Since analysis results are much easier to understand when presented in a
visual form like a chart, its too bad the 2007 has this problem. I certainly
hope Microsoft addresses the issue. In the meantime, maybe some ingenuity in
our code might get around it. Thanks again ...

John

"JLatham" wrote:

It's not that the VB runs slower - my ad hoc 'tests' are inconclusive on
that. It's the charting engine that is the giant killer. Quite frankly, the
client I was working with went back to 2003 after we discovered all of this
(and he'd already spent the money for 2007 at my recommendation - I felt
really bad about that). In his case, he had data files approaching 1/2
million lines of code and wanted to pull it all in and, like you, divide it
up into groups and chart it and we thought this would be an excellent
opportunity/reason to go to Excel 2007 with its 2^20 rows ability. That side
of things worked as advertised - then we started graphing it...

Ooops! There have been plenty of threads about charting problems in the
past. Some of my Excel sheets now have to display an estimated time to
completion in XL2007 to stop the users from aborting them.

Things that were completed in a few seconds in 2003 now take minutes.

"JohnJohn" wrote:

To answer Tim's question: The Visual Basic code breaks down a column of 10k
integers into subgroups of the numbers and then does statistical comparisons
of the subgroups with a reference area on a sheet.

To reply to Jlatham: yes, the code - does - create 8-12 charts, each of
which can have up to 10k data points. So maybe, what you suggest (charting)
is the problem.

10k data points will fit into XL2003 and be a lot faster.

Regards,
Martin Brown