locked
Macro in Excel 2010 running 10 times slower RRS feed

  • Question

  • I wonder if anyone here could help or explain a strange behavior in Excel 2010! I believe it is a bug in Excel.

    In one of my workbooks I had some macros which after running for a while started to slow down (several times slower)!

    To make a long story short I striped down the Excel workbook and created 2 really simple workbooks: one runs a macro in 0.25 sec, and a second one that runs the same macro in 3.3 sec. Both have identical macros and identical formulas in Sheet1 (only one Sheet in each workbook).

    Moreover, the "Good copy" workbook slows down if the "Bad copy" is opened. Once the "Bad copy" is closed the "Good copy" gets back to speed.

    If the Calculation is set to manual then both are running fast (this points to a workbook re-calculation delay).

    If in the "Bad copy" I delete the three columns with formulas and I recreate them in new columns, then the "Bad copy" gets back to running fast again.

    It seems that Excel will compute only the required cells when a cell changes, which makes sense.

    However, in the "Bad copy" those three columns must have something that triggers a new calculation of that workbook every time the VB writes to a workbook even if the macro runs on another workbook! This is very strange to me!

    These simplified workbooks contain only one trivial macro (I made them just to isolate the issue):

    Sub simple_macro()
        Application.ScreenUpdating = False
        Dim starttime, taken
        starttime = Timer

        For i = 1 To 500
        Cells(1, 5).Value = i
        Next i
        
        taken = Round(Timer - starttime, 2)
        Cells(2, 5).Value = taken
        Application.ScreenUpdating = True
    End Sub

    I do not think you can recreate easily the problem starting with a new workbook, so I wish there would be a way to post here the two copies! I was hoping that someone from Microsoft would take a look at the "Bad copy", because this behavior can slow down anyone's macro without even knowing it! I think I have other workbooks with the same issue.

    Thanks for any comments anyone might have!

    • Moved by George123345 Tuesday, December 16, 2014 6:27 AM Excel dev issue
    Monday, December 15, 2014 8:46 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.
    Tuesday, December 16, 2014 6:26 AM
  • I do not think you can recreate easily the problem starting with a new workbook, so I wish there would be a way to post here the two copies!

    Hi Mike,

    You can upload your copies in OneDrive and share the link here, so that we can easily reproduce your problem. Note that remove any credential information from the uploaded Excel workbooks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Caillen Wednesday, December 17, 2014 10:01 AM
    Wednesday, December 17, 2014 10:01 AM
  • Did you try to do an "Open and Repair" on the "bad" workbook ?
    Wednesday, December 17, 2014 3:17 PM
  • Hi Caillen,

    Sorry for the delay!

    I have loaded the two files on OneDrive as you've suggested!

    I did not use OneDrive before so I hope that this link works!

    https://onedrive.live.com/redir?resid=A0A05A7504D0E5D%21116

    let me know if you cannot open it!

    Thanks!

    Mike

    Saturday, January 17, 2015 1:21 AM