2003 Excel VBA on XP runs very slow vs Win/Office 2000 - why?

[Quadra]

Using a 1.4 GHz laptop w/ Windows 2000 and Office 2000; the VBA in the Excel
takes roughly 10 minutes to run. Thought that was slow, with my boss'
blessing bought a new 3.6 GHz desktop (Intel 560). I couldn't get Windows
2000, so it's loaded with Windows XP Professional. Loaded up Office 2003 and
the same file in Excel 2003 takes 15 minutes!!!!!!! I expected it to take
around 3.5 minutes.

Very confused about this...

Benchmarked the new desktop with a couple utilities and it is running about
2.3x faster than the laptop. So why is the Excel VBA running so poorly??? I
have 2 GB of RAM in it.

Can I load Windows 2000 on this machine? If I do, will it run faster than
XP? I have to go buy a copy of Windows 2000, so I don't want to do this
unless I know it will work. The new desktop doesn't come with restore disks,
so setting it up for new OS is alot of work. I'll do it if Windows 2000 is
the answer.

Or, should a buy a copy of Office 2000 and load that onto XP? I'm thinking
the problem is with Office 2003, not XP since the computer benchmarked very
well.

Ideas, suggestions & commets are all welcome! Thank you.

 

[Charles Williams]

Hi Quadra,

this does not usually happen, so there must be something in the VBA that is
causing a problem.

Anyway 10 minutes or 3.5 minutes is a very long time for a VBA program.

What does the VBA program do? Did you write it? was it created using the
macro recorder?


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[boring99]

Hello,

I am having the same problem as Quadra, and I can't believe it. I
doesn't make any sense to me.

I have a very calculation intensive Excel VBA macro that previously ra
quite quickly on my Dell Dimension computer (Windows XP Professional
3.0 GHz w/hyperthreading, 1GB RAM) using Office 2000, but now run
approximately 10 times SLOWER once I upgraded to Office 2003 (sam
computer). My processing time slipped from slightly over 6 minutes t
slightly over 63 minutes!!!

In disbelief, I took the same file to an office laptop (IBM Thinkpad
P4 Mobile, 256MB RAM) running Windows 2000 Professional and Offic
2000, and the silly laptop processed the file in only 9:17! That'
still over 6 times faster than my desktop computer!

I have no idea why this is happening, but clearly something drastic ha
changed between Excel 2000 and Excel 2003 in terms of how it processe
data. Nearly all the calculations are done without interacting wit
the worksheets, as most are algebraic or statistical in nature. Onl
the results of the computations are presented to the worksheets.
have not changed the file at all, and it is saved in only the curren
version of Excel (Excel 2003). I am aware of some improvements wit
Excel 2003 in certain statistical functions that are used in selec
places throughout the macro, but clearly the majority of the function
have not been modified between Excel versions. All I can think of i
that perhaps since the macros were originally written in Excel 2000
Excel 2003 is able to use them but only after a very lengthy conversio
process. I find the extent of the delay, however, to be unbelievable.
Something else must be happening.

Has anyone else witnessed this, or better yet, found a way to mak
Excel 2003 operate at the speed of Excel 2000? I'm about ready t
re-install Office 2000 and forget about Office 2003 until the proble
is recognized and fixed through Microsoft Service Pack releases o
something.

Thanks,
Jerem

 

[Charles Williams]

Things to try:

- temporarily disable the antivirus office plugin.
- switch to manual calculation (if you have not already)
- measure the time spent in in your subs and functions to find out whats
running slow

The only things that I know of that are of any significance with respect to
speed between Excel 2000 and Excel2003 are
- change in excel's global calculation algorithm (but this should not affect
VBA unless it triggers recalcs)
- changed algorithms for a number of statistical functions in excel 2003. I
do not know if this has a speed effect but you should be able to test this
by dummying out the calls to these functions.

I just ran another test and on my systems I cannot detect any speed
difference between Excel 2000 VBA and Excel 2003 VBA using Windows XP.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[boring99]

Hello Charles,

Thank you for your reply and advice. I have good news to report, bu
also a potentially troubling finding.

I was able to successfully improve the speed of my macro tha
previously took 63 (!!!) minutes using Excel 2003 (but only 6 minute
in Excel 2000), and lower it down to slightly less than 2 minutes.
used your suggestion about turning off automatic calculation throughou
all portions of my macros that wrote output data to worksheets, the
re-enabled automatic calculation in the macros whenever I needed t
grab data generated through worksheet calculations.

That's the good news. The bad news is that all of the data wer
written to worksheets that had no formulas or links associated wit
them. I had intentionally divorced myself from this habit following
similar slow performance result with Excel 2000 where I had place
output data on one or more sheets that also had large quantities o
worksheet manipulation. What is interesting (and perhaps sad) is tha
it appears the "improved" global recalculation method employed in Exce
2003 for some reason doesn't properly identify worksheets that are no
impacted by data written on other worksheets. In other words, i
appears that Excel 2003 incorrectly assumes it must recalculate certai
worksheets when, in fact, the recalculation is unwarranted.

I suppose for most users this is a non-issue, but for folks who hav
workbooks with many sheets, some of which may contain massiv
quantities of data manipulation, then the interaction between macro
and processing times may be adversely impacted. My example, whic
perhaps is more extreme than most, resulted in an incredible almost 97
decrease in the processing time once the problem was identified an
"fixed" (a processing time decrease from 63 minutes down to ~
minutes). As mentioned earlier, I was successfully able to overcom
this "issue" through the workaround you identified, but it this was no
an "issue" in Excel 2000. (I never tested the macro in Excel 97). Thi
is unfortunate for scientists, mathematicians, engineers, and others wh
use Excel for large processing tasks that involve both workshee
manipulation and macros all within one workbook. Maybe Microsof
should re-evaluate their new global recalculation algorithm to see i
it performs as desired. It seems to me that perhaps some modificatio
to their algorithm might be necessary.

Thank you again for your assistance,
Jerem

 

[Charles Williams]

It may not be the global recalculation that is causing you the problem: that
does not make excel 2003 recalculate when previous versions did not.

There is one other change in Excel 2003: hiding or unhiding rows triggers a
recalculation, which is different behaviour to previous versions.

Is this the explanation?


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[boring99]

Hello again, Charles.

Unfortunately, my macros never hide or unhide any portion of th
workbook. The writing of data back to formula and link free worksheet
is causing the problem.

Here's an example: writing the ordered reversal of ~2,500 data point
back into a selected worksheet. The activity occurs on a workshee
that contains no formulas, and no other worksheet references/links t
the data at that point in the macro execution. The macro simply read
into an array a user-identified column of data on the worksheet, withi
the macro it reverses the order of the data points within the array, an
then the macro places the reversed data back to a new area within th
same worksheet. The first two parts of this activity process extremel
quickly (as expected), but the actual writing of the data back into th
worksheet is a good example where I witness the performance problem.
It takes my computer (Dell Dimension 3GHz w/hyperthreading, running Wi
XP Pro and Office 2003 Pro) nearly 20 seconds to simply place the ~250
data points back onto the same worksheet. Crazy! All other macr
output is sent to blank worksheets where, once the output stream i
completed, the data is copied and pasted (only values) to othe
worksheets where the new data then interacts with formulas on thos
worksheets. With automatic calculation set to true, the extremely slo
writing of data back to my worksheets seems to indicate that much, i
not all, of my workbook is being recalculated with each written dat
point despite the fact that no formulas or links are being directl
affected by the new data.

Something changed between Excel 2000 and 2003 that would explain th
incredible preformance difference, but formatting or hiding/unhidin
columns/rows is unfortunately not the culprit.

Any other thoughts?
-Jerem

 

[Charles Williams]

its impossible to tell exactly what the problem is without analysing the
workbook.

If you can send me a zipped copy of the workbook I would be happy to try to
analyse the root cause of the problem

see http://www.decisionmodels.com/contact.htm for an email addrress


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com