2003 code runs super slow in 2010

Hi,
I am trying to run a suite of Excel 2003 spreadsheet macros 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 it 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:

Please Login or Register  to view this content.

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.

I tried removing the lines but that caused a runtime subscript out of range error elsewhere!

Here is some code, attached, that may help...

Originally Posted by snb

In which other forum did you post your question ?

Thank you for your interest. Mr Excel and Microsoft Answers, but no luck as of yet.


It will require either someone who has come across this before or with a super understanding of Excel 2007/2010 changes to answer this I suspect.

Not sure why the interest in other forums? I've got the best response here, but here you go:
http://answers.microsoft.com/en-us/o...&page=1#footer

http://www.mrexcel.com/forum/showthread.php?t=552931

Originally Posted by royUK

Also, selecting will slow the code down. It's generally not necessary.

I'm sure the macros can be improved in lots of ways, as I didn't write them.

However, my main concern at the moment is just to get this Generate Statement code working at a reasonable speed.

I have stepped through in debug and it is not until I hit the lines mentioned that it goes ultra slow.

Checking again yesterday, I noticed that when it hit ".Calculation = xlAutomatic" I saw Calculate appear on the bottom left of the spreadsheet (i.e. the line actually made it do a calculation). I don't think that happens in 2003 but I'll go and test that now.

With the PrecisionAsDisplayed line, I didn't see Calculate appear but it did still take an age to return as if it was doing something with the whole workbook.

Company decision. Eventually everybody will be Windows 7/Office 2010.

Guy who wrote this left a while back and it has now fallen in my lap.

It probably is a simple reason why this works in 2003 but not 2010, but I haven't come across it yet. Just hoping that someone else has.....

Originally Posted by emanresu65

Checking again yesterday, I noticed that when it hit ".Calculation = xlAutomatic" I saw Calculate appear on the bottom left of the spreadsheet (i.e. the line actually made it do a calculation). I don't think that happens in 2003 but I'll go and test that now.

Yes, doesn't happen in 2003. Calc is set to automatic but it doesn't force a calculation.

Originally Posted by snb

It seems you are only interested in a simple solution.

Not quite sure where you got that impression from. If you have a convoluted, difficult solution I am still interested. Please let me know!
All I have been doing is highlighting where the problem lies. What I'll do is see if I can make a cut down version and see if it has the same problem, then I can attach that instead.

Thanks everyone for your help.

I'm trying to find time to create a meaningful cut-down version which still shows the issue....I may be a few days!

When I have one or have identified the issue then I will post back.

Thank you.

A little more on this....

Whilst still going slow on the "With Application .Calculation = xlManual .MaxChange = 0.001 End With" lines, I found that pressing on <F8> again would push on through the code. Btw, also I realise now that "Calculate" in the bottom left allows me to force an update if I click on it and doesn't mean it is actually doing a calculation. Doh!


However, it then really gets stuck on subtotals and pressing <F8> just makes the VBA screen go an opaque white until it finishes whatever processing it is doing.

For example

Please Login or Register  to view this content.

The named range contains a few lines of data and the remaining rows are empty. Every cell contains a formula, however, grabbing info from another sheet in the workbook. e.g. "=IF(Collateral!C4="","",UPPER(Collateral!C4))".

It takes many minutes to process this which seems strange for only 50 rows.

N.B. Code already has Application.ScreenUpdating = False and I have also added Application.DisplayStatusBar = False

Hi Mordred,
Does it do the calculation when the mode is set to xlAutomatic or when an update is next made to the workbook? I was expecting the latter but starting to suspect the former!

However, I eventually reconfirmed my problem was on the subtotal command - at which point calculation has been set to xlManual. In the last example I posted it should only be looking at 2,500 cells.

I now have Microsoft on the case. When I know the solution/reason why I'll post back, probably in a couple of weeks, and let you know.

Meanwhile, I'll tell you I have just finished a first draft of a stand-alone version which is huge (having to contain the data rather than links to it) though some of the filtering is gone. Now the statement is created much faster, though it is still slower than when I run it in 2003; 2 to 4 secs in 2003 vs 12 to 15 secs in 2010.

Hi all,

Apologies for not posting answer sooner ...


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.


Kind regards,
Bob