Office 2004 Excel

[jjpmir]

Hi All,

Just wanted to share a really interesting and weird tidbit about VPC 7.01
and Office 2004 for Macintosh.

I have a 30+MB Excel spreadsheet that I have developed with many cells and
calculations (over 450M formula cells). The worksheet was developed in
Office 2004 Excel, the current version (11.1.1)

Since recalculating this spreadsheet takes time, I've taken to turning off
the automatic calculation and just doing it when I needed/wanted to.

I also use VPC a lot for work (running Outlook and such).

For kicks, I thought I would time the recalculation under Mac OS X versus
VPC using Office 2003 and XP Pro.

Well, guess what? The exact same worksheet copied over the VPC took 50
seconds to recalculate whereas using the Macintosh Office 2004 Excel took 102
seconds (104% longer) with no other Mac programs running and 113 seconds
(126%) with a full complement of normal Mac programs!!!!!! Huh? What could
the reason for that?

Any users have a clue? Both Excels were configured the same (add-ins,
etc.), VPC was paused when running the recalculate on the Macintosh side.

Thanks for everything,

Jim

 

[Jim Gordon MVP]

Hi jj,

A huge number of unknowns come into play here. There are some
calculations that are faster on Mac. Others are faster in Windows.

There have been some postings in this newsgroup that point out specific
formulas that can be used in place of others with the result of faster
calculations. Do a Google search and see if a formula you are using has
already been identified as pokey. Perhaps a simple substitution will
result in better performance.

-Jim

 

[Jim]

Thanks Jim -

I've hunted all of the messages still on this newsgroup without success and
google'd until my eyes crossed.

Nothing yet...

The thing is, the formulas are match and index... at least 90% of them are.
We are talking memory searches, how could an emulated processor run faster
than native hardware for a memory search????

In any case, thanks again,

Jim

 

[chestnut]

Hi,
Has there been any new finding on this topic of slow recalculation on
Excel for Mac?
I just got a new iMac G5 with 1G of memory and 2GHz clock (i.e. power
should not be an issue...) and installed Excel 2004 for Mac.

I imported from PC a relatively small Excel sheet with about 10,000
cells and 10 graphs spread over 40 sheets.

The sheet uses a few macros, as well (nothing particularly exotic, just
moving data from am input mask to the apporopriate sheet). Each time I
execute one of them, I get the "recalculating, xxx%..." message and a
long waiting time. When the exactly same sheet runs on PC, the effect
of the macro is almost instantaneous (and I have "automatic
recalculation on" set).

Anything in my macros which might be a bottleneck on Mac more than on
PC? I understand that there might be some difference in the calculation
times, but here we are talking about a 20x~30x factor, for quite simple
formulas, after all.

I am looking forward to reading your shared experiences!

Bob

 

[J Laroche]

chestnut wrote on 2005/05/23 05:26:

Hi,
Has there been any new finding on this topic of slow recalculation on
Excel for Mac?
I just got a new iMac G5 with 1G of memory and 2GHz clock (i.e. power
should not be an issue...) and installed Excel 2004 for Mac.

I imported from PC a relatively small Excel sheet with about 10,000
cells and 10 graphs spread over 40 sheets.

The sheet uses a few macros, as well (nothing particularly exotic, just
moving data from am input mask to the apporopriate sheet). Each time I
execute one of them, I get the "recalculating, xxx%..." message and a
long waiting time. When the exactly same sheet runs on PC, the effect
of the macro is almost instantaneous (and I have "automatic
recalculation on" set).

Anything in my macros which might be a bottleneck on Mac more than on
PC? I understand that there might be some difference in the calculation
times, but here we are talking about a 20x~30x factor, for quite simple
formulas, after all.

I am looking forward to reading your shared experiences!

Bob

Hi Bob,

... on both platforms setting the value of a cell to nothing is
noticeably longer than setting it to any other value. But on the
Mac is excruciatingly slow.

So it might be that your data moving is actually coping with a lot of empty
cells. It could be faster to test data before moving it and using
destCell.ClearContents instead of destCell.value = sourceCell.value when the
latter is empty.

Of course you might try setting automatic calculation to manual at the
beginning of the macro (Application.Calculation = xlCalculationManual) and
back to automatic (Application.Calculation = xlCalculationAutomatic) at the
end. Be careful though: if the macro is relying upon cell values that should
recalculated during the execution of the macro you might get unexpected
results.

Look also if Application.ScreenUpdating is false during the execution of the
macro.

JL
Mac OS X 10.3.9, Office v.X 10.1.6