Issue with RecalculateAll() Area on a Sheet

Issue with RecalculateAll() Area on a Sheet

Posted by: olivier.rondeau on 21 October 2019, 5:43 am EST

• 

  olivier.rondeau

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 October 2019, 5:43 am EST

  Hi,
  
  I encounter a problem when recalculating a sheet with RecalculateAll ().
  
  The method should force the re-evaluation of all cells in the Sheet.
  
  However, if a formula is sufficiently far from the area of ​​the sheet displayed, it is not reevaluated.

  To illustrate this problem you will find a project attached.
  
  The sheet contains a custom formula in cell row 136 column AE.
  
  The next scenario to reproduce the problem is simply to recalculate the document using the button launching RecalculateAll ().
  
  A breakpoint is set on the re-evaluation procedure of the custom formula RIK_AC. We should see that the breakpoint is reached when opening the document, but not when clicking Recalculate.

  In addition, if you run the recalculation and then scroll to the row of the cell containing the formula (136), its re-evaluation will be triggered …

  Apprenez à prononcer
  
  So my question is does the RecalculateAll() method compartmentalize the re-evaluation of sheet cells?
  
  And how to get around the problem?

  Thanks for your help.
  
  Best Regards.

• 

  olivier.rondeau

  • Post Options:
  • Link
    Copy

  Posted 21 October 2019, 9:08 am EST

  TestSpread12.zip

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 22 October 2019, 4:11 am EST

  Hello Olivier,

  Thank you for attaching the application. Running it I could see the behavior you mention.
  
  To make the sheet re-calculate the formulas, even for the cells that are currently not is the view, please try setting the AutoCalculation property to false.```
  
  fpSpread1.ActiveSheet.AutoCalculation = false;

  Ruchir