none
Excel 2003 calculation runs extremely slow in Win 7 (64-bit)

    Question

  • Hi all,

    Problem:

    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.

    Analysis:

    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("AP8:AR368").Copy
                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.

    Question:

    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.

    Regards,
    Antony

    Saturday, November 16, 2013 8:36 AM

All replies

  • Are you still in 2003, or have you moved the workbook to 2010?
    Tuesday, November 19, 2013 2:26 PM
  • Hi Bernie,

    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.

    Regards,

    Antony.


    • Edited by AntCh Wednesday, November 20, 2013 5:22 AM
    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

        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With

    and then finish with

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            On Error GoTo noCalc
            .Calculation = xlCalc
            Exit Sub
    noCalc:
            .Calculation = xlCalculationAutomatic
        End With

    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

    =LongFormula

    change it to

    =C2 + LongFormula

    Then when you calc, the formula will accumulate the value without having to copy and add values.


    Wednesday, November 20, 2013 2:59 PM
  • Bernie,

    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.

    Regards,

    Antony.

    Friday, November 22, 2013 10:12 AM
  • 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:

    =IF(KeyCell=1,C2+LongFormula,0)

    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?


    Friday, November 22, 2013 1:22 PM
  • My Excel Version is:

    Version: 1.0.6129.5000 (32-bit)

    Where is the group for Win 7 (64-bit)?


    • Edited by AntCh Friday, November 22, 2013 5:25 PM
    Friday, November 22, 2013 5:11 PM
  • Maybe it is a 64 bit OS running a 32 bit program issue - and that is beyond my understanding.... sorry.
    Friday, November 22, 2013 5:42 PM