A solution to Excel2007 slowness when running Excel 2003 macros

[Q]

Excel environment:
-Excel 2003 on a Pentium4 computer with 2GB of Ram
-Excel 2007 on a 2-Xeon computer with 16GB of Ram. Office 2007 SP2 has been
applied.

Scenerio:
-Two Excel files, FileA.xls and FileB.xls, and both were created in Excel
2003.
-FileA.xls has some macros that do the followings:
open FileB.xls
open some text files, extract some data and put the data into FileB.xls,
does some graphs in FileB.xls,
save FileB.xls.
-FileB.xls has various worksheets that have predefined cell referencing to
other cells on different worksheets in FileB.xls. Also, some worksheets have
formula that performance calculations referencing other cells in in
FileB.xls. Furthermore, there are some macros in FileB.xls as well.

Result:
In Excel 2007, some macros run 2 to 4 times slower than that of in Excel 2003!

Solution:
In Excel 2007, open FileB.xls and do a save as to FileB.xlsm. Open
FileA.xls, modify the reference to opening FileB.xlsm, instead of FileA.xls.
FileA.xls is still saved as a .xls file. Now, when running the macro in
Excel 2007, the speed is about the same as when it is run in Excel 2003.
Saving FileA.xls as FileA.xlsm and run the macro does not seems to improve
the speed.

Credits:
Like some developers, I have experienced slowness when running macros in
Excel 2007. When I was searching the web to see if there is a solution, I
came across this article:
http://msdn.microsoft.com/en-us/library/aa730921.aspx. The article did not
help, but in the Community Content of the article rimbauda mentioned the
following article by Bob Flanagan
http://www.eggheadcafe.com/software/aspnet/30080342/slow-excel-2007-calculati.aspx helps!


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...583959a&dg=microsoft.public.excel.programming

 

[Chip Pearson]

For what it is worth, Microsoft claims that the XLSB binary 2007
format is faster than either XLSX or XLSM. How much faster, and
whether the end user would notice the difference, is another question.
It might be that it is comparing microseconds to milliseconds. Faster,
true, but at that scale, who cares?

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)