Speed Issue - Excel X on G4 1G vs Excel 2003 on PII 333

[Reto Walker]

Hi guys

Developed an Excel spreadsheet (~100MB) on my rather antique Pentium II 333
MHz laptop (192MB RAM / Excel 2003). Spreadsheet uses a Add-In (xla) and two
macros (not very complex.)

Execution of macros takes approximately 60 secs on my PII which considering
its age is quite OK - loading time is also the time to get coffee ;-)

Now, after moving to a new employer with fantastic looking iMacs G4 GHz
(512MB RAM / Excel X), I was expecting execution times of a few seconds...

Loading time was OK (definitely quicker than on the PC even though not mind
blowing by any means) but I discovered that the same spreadsheet takes
forever to execute the same macros (at least 5x longer!)

Any ideas why that may be and what I can do to significantly increase speed?
Any RAM tweaks or similar?

Any thoughts are highly appreciated

Thanks in advance

Regards,

Reto

 

[JE McGimpsey]

Developed an Excel spreadsheet (~100MB) on my rather antique Pentium II 333
MHz laptop (192MB RAM / Excel 2003). Spreadsheet uses a Add-In (xla) and two
macros (not very complex.)

Execution of macros takes approximately 60 secs on my PII which considering
its age is quite OK - loading time is also the time to get coffee ;-)

Now, after moving to a new employer with fantastic looking iMacs G4 GHz
(512MB RAM / Excel X), I was expecting execution times of a few seconds...

Loading time was OK (definitely quicker than on the PC even though not mind
blowing by any means) but I discovered that the same spreadsheet takes
forever to execute the same macros (at least 5x longer!)

Any ideas why that may be and what I can do to significantly increase speed?
Any RAM tweaks or similar?

Any thoughts are highly appreciated

First, make sure you've applied the 10.1.2, 10.1.4 and 10.1.5 updates -
they each have improved speed to at least some extent. You can get them
at

MacTopia downloads:

http://www.microsoft.com/mac/downloads.aspx

Second, make sure you're not running other processor intensive programs.
Panther is better than Jaguar, but I've seen XL still starved for cycles
(i.e., getting 35% of the processor) when other tasks were mostly idling.
You can tweak Excel's "nice" setting to hog more of the processor if
that's happening.

Third - XLv.X is just not a speed demon in a lot of areas, especially
macros. Some calculations are actually significantly faster in XLv.X
than in XL03 on a compaarable Windows machine, but many are far, far
slower. It's getting better with each update, but there's still a long
way to go. 100MB is a very large file for XL, whether on Windows or Mac,
and WinXL has had much longer with the same architecture to optimize its
routines.

Fourth - you can often make calculations significantly faster by
rearranging them, using different built-in functions, etc. See Charles
Williams' site for a host of suggestions:

http://www.decisionmodels.com/optspeed.htm


One Mac-specific bottleneck that isn't listed in Charles' site is the
use of Copy and Paste. MacXL uses the system Clipboard, and it's pretty
poky. If you're doing a lot of copying, and you just want to pasted
values, it may be helpful to use direct assignment. For instance,
instead of:

Worksheets(1).Activate
Range("A1:A10").Select
Selection.Copy
Worksheets(2).Select
Range("j10").Select
Selection.PasteSpecial Paste:=xlPasteValues


use

With Worksheets(1).Range("A1:A10)
Worksheets(1).Range("A1:AJ0).Resize( _
.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Tim Golobic]

I see a huge slow just from XL 98 in OS9 to XL for OSX. The biggest
factor in the slowdown is the is OSX the screen takes longer to redraw
with the new transparent windows and font smoothing. Someone
previouslyt gave me this tip:

Application.ScreenUpdating = False

With this XL doesn't redraw the screen each time sometime changes. At
the end of your macro include:

Application.ScreenUpdating = True

The speedup will be impressive.

Tim