Legacy macros running slow in Excel 2010

[emanresu]

Hi,
I have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.

However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:

With Application
     .Calculation = xlAutomatic
     .MaxChange = 0.001
End With

ActiveWorkBook.PrecisionAsDisplayed = False

It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!

Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't help and anyway I loose things, like data validation, when I save it in Excel 2010.

Thanks for any help you can give.

 

[emanresu]

To give a better idea of the problem I'd like to post some more code but there is a lot of it!

Is it possible to attach a document on this forum?

 

[emanresu]

Please be aware I have also posted this question here:

http://www.excelforum.com/excel-programming/777779-2003-code-runs-super-slow-in-2010-a.html

http://answers.microsoft.com/en-us/...7-b12cda918fe2?tm=1307089316201&page=1#footer

 

[emanresu]

For completeness sake, here is the solution:

Excel 2003 differs from 2007/2010 in that the later versions have a new smart calculation engine, so it works pretty much differently from before.

The code written by the original author had several of those "Application.Calculation" commands.

I discovered that with the new calculation engine I didn't need any of those commands!

All I did in the end was to strip them all out and it worked like a dream.

 

[Sabotage]

For completeness sake... if anyone is reading this.

I have come across the same problem and it took me ages to figure it out what the solution is to it. Apparently not many forums mention my solution. In the end I have just laughed as all I had to do (instead of skimming my code with some of being 300 to 500 lines long) was this:

Application.PrintCommunication = False

the bleeding printer com app. What was I thinking .

Anyway if it helps don't forget to turn it back on again at the end of your code (True), otherwise your excel will not print.

Have a good day...

Alex