Macro Speeds in Vista OS vs XP OS

[Guest]

I have a macro that took about 3 minutes to run in Windows XP running Office
2003. I currently got a much much faster computer and it has Vista OS on it.
I installed Office 2007 and the same macro took over 15 minutes to run. I
thought that maybe it was an issue with Excel 2007. I then installed Excel
2000 on the computer and tried to run the macro. It also took 15 minutes.
That leads me to believe that it isn't the version of office that is slowing
it down. It seems like it is the OS. Any suggestions. Thanks

 

[RB Smissaert]

What does the macro do?

RBS

 

[Guest]

It does a lot of calculations. It first orders several rows based on a
parent child relationship. Then it looks at another sheet that contains up
to 15,000 customers. It matches the usage which is in one column to the
section that it belongs to in the initial workseet. Then for each row, it
calculates voltages, currents, impedances, and power. These are complex
numbers. I hope this explains it.

 

[RB Smissaert]

So, it is all work done in Excel?
Can you see what part of the code is causing the slowness?

RBS

 

[Guest]

The work is done in excel. However, there isn't an aparent slowup.
Everything just goes a little slower. I can watch it fill in data as it does
its calculations and it just does everything slower. The strange thing is
that it does this on the Vista OS machine using both excel 2000 and excel
2007. That leads me to believe that it isn't the version of excel.

 

[RB Smissaert]

I haven't used Vista yet and I have no idea why it would make Excel slower.
If the speed is a problem then maybe you will have to redesign the whole
setup. Maybe post the code.
It might be an idea to move code to a VB6 ActiveX dll as this can speed
things up quite a bit and it is quite simple to do.
Another option might be to move to a database like SQLite. This can be very
fast.

RBS

 

[Chip Pearson]

Adam,

Since you say you can watch Excel fill in the data, I assume that you have
not turned off ScreenUpdating. Disabling ScreenUpdating can dramatically
improve the performance of any version of Excel on any Windows platform.
Moreover, Vista works with the graphics subsystem in manner quite different
than does Windows XP, and therefore the difference made by turning of
ScreenUpdating may be considerably more important in Vista than in Windows
XP.

Application.ScreenUpdating = False
'
' your code here
'
Application.ScreenUpdating = True

Also, you may be able to turn calculation off if your code does not require
the intermediate calculated results.

Application.Calculation = xlCalculationManual
'
' your code here
'
Application.Calculation = xlCalculationAutomatic



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Gary Keramidas]

not sure what your problem is, but a lot of people are reporting a great
disparity in speed using vista.
i know i reported it.

i have a macro that opens another workbook and populates a few cells and closes
it. it does this for a number of blends that are entered. let's say there are 15
or 20 blends, it opens each workbook and populates some cells and closes it.

now, in xp/office 2003, this may take 3 or 4 seconds. in vista/office 2007, it
takes 8 or 9 seconds. it's not my code, it's the vista/office 2007 combination.

i can put the all of the files on the local hard drive and xp/office 2003 is
faster opening the files across the network than vista/office 2007 is opening
the files locally.