Excel Version - strange performance difference

[Bartek]

Hi,

Not a real problem, but some technical oddities:

I was curious how different hardware platforms translates to speed of different operations in Excel VBA, so I tested three computers - old notebook (Mobile P4 1.8 processor, Geforce 2 Go GPU, WinXPP/Excel XP), rather old desktop (P4 2.8 processor, Geforce 4 MX, GPU Win2000/Excel 2000) and new notebook (Core Duo T2400, Mobility Radeon X1600 GPU, Win XPP/Excel 2003).

When it comes to operations like copying range (Range(X).Copy Range(Y)), modifying cells (With Cell(X)... value, interior etc) or purely mathematical calculations with VBA: desktop was about 60% faster than old notebook, and new notebook was about 40% faster than the desktop.

But there was one very strange exception: simple code to animate the shape, like this:

for i=1 to 1000
ActiveSheet.Shapes("myshape").Rotation=(ActiveSheet.Shapes("myshape").Rotation+3#)  Mod 360
DoEvents
next i

I ued the star shape (msoShapeStar) with 3D effect. DoEvents is necessary to update the screen. The system with Excel 2000 proved to be about 15 (yes - fifteen!) times faster than both old and new laptops win Excel 2002/2003. I tested some other shape operations and indeed - Excel versions after 2000 are much slower than Excel 2000 when it comes to displaying and refreshing shapes. Users will not notice the difference in typical, every day use of shapes, but it is still puzzling

When it comes to other findings, I also noticed that if multiple workbooks are open, this may slow down the operations on range and cells on an active workbook, even up to 20%. Internal calculations with VBA are unaffected by multiple workbooks.

 

[Damon Ostrander]

Hi Bartek,

I noticed some very similiar differences between Excel 97 and Excel 2000 speeds in manipulating shapes, with 2000 being much slower than 97. In that case it was easy to attribute the slowdown to the fact that the object model for shape objects had changed significantly, becoming much more complex and rich. As a result, many more properties had to be calculated and updated each time the shape was moved or manipulated in almost any way.

I don't know for sure, but my guess is that Microsoft updated the object models yet again (surprise!). I don't believe execution speed or memory usage is high on the Microsoft developers' priority list where Shape objects are concerned. They seem to be concerned only with functionality--provide more types of shapes, more handles and whiz-bang features (e.g., transparency), and are willing to pay a steep price in performance.

Damon