Our user has an Excel 2003 workbook with some iterative calculations. It runs for 10 mins only on Window XP. When they upgrade to Win 7 (64-bit), the same workbook runs for an hour. It works but extremely slow. Based on that, they try to run on Win 7 (32-bit), it finally goes to 20 mins. As the workbook runs repeatly, the one-hour workbook turns to days overhead.
We get this workbook for analysis. The structure is as follow:
1. It runs a simple loop for 10,000 times in VBA macro.
2. For each loop, it mainly performs two tasks, says task a, task b.
(a) It is to re-calculate Sheet1. i.e. Sheets("Sheet1").Calculate. In Sheet1, there is a formula in cell H1 to read a record from Sheet2 using =INDEX(Sheet2!$B$4:$L$65536,C3,2), where C3 is just a running index from 1 to 10,000. Then Sheet1 contains a list of records and formula to refer to cell H1.
(b) It is to accumulate the range value from another range using:
Range("BC4:BE364").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
The workbook is simply saved as Excel 2010 format. Then check the performance. We find that 50% of its time spends in the above coded statements, i.e. INDEX, Copy and PasteSpecial. We have tried to replace them by another VBA code but the performance is even much worse.
i) Any hints on the slow performance of Excel in Win 7 (64-bit)?
ii) As our users have to go to Win 7 (64-bit) from other constraint, is there any suggestion to improve the performance or tuning?
Thanks a lot.
Moved to 2010.
In fact, we have tried both. User's initial complaint was in Excel 2003. We get it and confirm that it is an issue in Excel 2003. But for my further analysis, I have already moved the workbook to 2010.
- Edited by AntCh Wednesday, November 20, 2013 5:22 AM
You may be double calculating - changing a cell value may force a re-calc, and then using the .calculate method does it as well.
So, start your code with
Dim xlCalc As XlCalculation
.ScreenUpdating = False
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
and then finish with
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
On Error GoTo noCalc
.Calculation = xlCalc
.Calculation = xlCalculationAutomatic
The other thing that you can do is change your iterations to 1:
Application.MaxIterations = 1
Then change your formulas to be circular - for example, if you have this in cell C2
change it to
=C2 + LongFormula
Then when you calc, the formula will accumulate the value without having to copy and add values.
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Wednesday, November 20, 2013 3:18 PM edit
Thanks for suggestion.
For the first one, we already have such codes. So the Automatic calc is off, Update screen is off, Event trigger is off. They are in place.
For the second one, get your point on circular formula. But if I would like to reset the cell C2 value to zero for each run of spreadsheet, how can we do that?
Apart from the formula tuning, do you have any hint why the same coding and formula run very quick in Win XP but not in Win 7 (64-bit)? That is the major question that the user cannot understand.
When you want to be able to clear and restart, create a cell and change KeyCell from 1 to 0, and then back to 1:
I don't know why the OS makes such a large difference - are you using Excel 64-bit too? Maybe ask in a Windows group?
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Friday, November 22, 2013 1:23 PM edit