none
Activesheet.Calculate still slowing down with other open workbooks RRS feed

  • General discussion

  • I have some VBA code that simply calculates an active sheet 10,000 times (a simple loop). Even though I have switched to manual calculation, screen updating off and used Activesheet.Calculate, the loop significantly slows down when I have other workbooks open.

    Ultimately, I need to run a very long loop that only calculates (efficiently) one sheet, without being impacted (at all) by any other unrelated workbooks or sheets that are open. (Using Usedrange.Calculate does work but it is much slower than Activesheet.Calculate). My understanding was that using Activesheet.Calculate should not be affected by other open workbooks... but it clearly is for some reason.

    Please help.

    Here is the simplified VBA code (very, very simple):

    Sub RunSim()
    
      Dim i As Long
     
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Application.EnableEvents = False
    
      'START SIMULATION LOOP
      For i = 1 To 10000
    
        ActiveSheet.Calculate
        
      Next i
    
      Application.Calculation = xlCalculationAutomatic
      Application.EnableEvents = True
    
    End Sub


    Monday, October 5, 2015 1:20 AM

All replies

  • Counter question: Why should it be necessary to calculate =1+1 more than one/10.000 times?

    This article explains how to improve the calculation performance:
    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Andreas.

    Monday, October 5, 2015 5:41 AM
  • Thanks Andreas. The active sheet has random numbers (i.e. it's a Monte Carlo simulation), so every time it calculates will give a different answer (10,000 different results to be used for various our calculations).

    I have read through a number of articles/posts/forums (including your link) and by all accounts Activesheet.Calculate should only calculate the active sheet, and should not be affected by other open workbooks... I'm really stumped here as the code/loop runs significantly slower when other large workbooks are open. I know a workaround would be to either close all other workbooks or bring all the calculations into VBA... both of these are not real options for my purposes.

    Any other help would be really appreciated (seems like such a simple thing to want to restrict excel/vba to one sheet for the duration of my loop). (I'm thinking I need to switch off something else, as clearly excel is doing something with the other workbooks during my loop).

    Monday, October 5, 2015 12:37 PM
  • Hi ronelk,

    Can you reproduce this issue when you open another new workbook without data? Based on my understanding, the Excel application share the resource like memory/cpu on the machine with other application.

    Since you mentioned that you open other large workbooks, it may consume much memory or CPU which can possible affect the calculation in the original workbook.

    Hope it is helpful.

    Regards & Fei


    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.

    Tuesday, October 6, 2015 2:16 AM
    Moderator
  • I have read through a number of articles/posts/forums (including your link) and by all accounts Activesheet.Calculate should only calculate the active sheet, and should not be affected by other open workbooks...

    Everything follows is only based on my experience, I have no inside informations:

    What you've read is correct, the result cells are only inside the active sheet. But that doesn't mean Excel can ignore the values of the cells in the other opened workbooks, because it is possible that the active sheet refers to one of this files.

    When Excel would perform the calculation as you assume, it would be so slow that you would never use it.

    Excel has to cache and queue many think to speed up the calculation, this process is really complicated, read this article:
    https://msdn.microsoft.com/en-us/library/aa140058.aspx


    So when other files are opened and the calculation is noticeable slower, IMHO it is obvious and logical that one or more of this possibilities happens:

    a) Excel caches data from the other files to accelerate a following calculation.
    b) When you open a file it consumes memory. As more memory you use, as
    more complex, and thus slower, becomes this process.
    c) Any PC uses virtual memory, if the real memory that is available for our task runs out, the OS copies some data from the RAM to your hard disk, which is 1000 times slower.

    IMHO whateverthe exact reason is, it doesn't matter. The question is what can you do to speed up the process?

    The simplest solution is to open 2 Excel tasks, open "the other file" first as usual, then open another Excel instance from the start menu and open your Monte Carlo simulation using File\Open.

    When the simulation is slow as before, I guess that your PC is at the limits: Buy more RAM, create a RAM disk for temporary files, use a SSD drive instead of an "old" harddisk.

    Andreas.

    Tuesday, October 6, 2015 6:51 AM