Very slow Visual Basic code execution in new Excel 2007

[Guest]

I have a Visual Basic procedure in the Excel of my Microsoft Office 2003 that
I've run hundreds of times and it finishes in 2 minutes.
Yesterday, I installed my new Microsoft Office Home & Student 2007 on the
same computer (and told it to remove the old Office) and it installed with no
errors or problems. But when I ran the same Visual Basic procedure that took
2 minutes to complete in my old Office 2003, now in the new Office 2007,
Excel, it took 6 and a half minutes to finish (with no errors).
The computer is a Toshiba laptop with 512 meg memory, 100 gig hard drive and
Windows XP Professional and is only a year old.
Could someone what's wrong. Why does the identical VB code take so much
longer to run.

Thanks ...
John

 

[Tim Williams]

What does your code do ?
Is all VBA slower on this new PC ?

 

[Guest]

Tim Williams has asked a key question: what does your code do?

I've found that Excel 2007 generally performs as fast as 2003 for most
operations; sometimes a bit slower, sometimes a bit faster, but any
difference was almost negligable, UNLESS I was dealing with
charting/graphing or graphic objects. At that point 2007 suffers serious
slowdowns. It's a known issue and MSFT is working on making it better.
Example: I have an application that graphs 8800 data points in an X-Y
scattercharts (51 charts in all). In Excel 2003 (on an older single core
system with 1GB RAM) the whole process takes about 2.5 minutes to read the
data (actually 1/2 million rows of it from a .txt file) and then create the
graphs. The same process in Excel 2007 takes about 12 minutes.

 

[Guest]

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...

 

[Guest]

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.

Today, I purchased a new Toshiba laptop with 2 gig memory and an Intel
Pentium dual core T2060 processor with Office 2007 and Vista Home Premium. On
the new laptop, the code that runs on my old laptop with an Intel Celeron M
380 processor and 512 meg. ram, Win XP and Office 2002 in 2 minutes, still
takes 3 min. and 50 seconds to run on the new laptop in the Office 2007.

Without trying to be a wiseguy, if this is the case with Office 2007,
running Visual Basic code - slower - the Office 2002, then I'm astonished. Am
I missing something here. Why would a programmer upgrade?

John

 

[Guest]

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

 

[Guest]

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.
You will lose nothing but learn more about Office 2007.
Good luck.

 

[laura_in_abq]

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?

 

[Martin Brown]

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.
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.

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.

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

Regards,
Martin Brown