TMSFNCSpreadGrid UpdateCalculations Method

FNC TMS FNC UI Pack
1

Experimenting with the UpdateCalculations method in TMSFNCSpreadGrid, it does not seem to function as anticipated. The test code has a TMSFNCSpreadGrid component and a timer, which updates cell A1 within the spreadgrid programmatically. Given this incremented value updates A2, from the FMX documentation on the UpdateCalculations method, calling UpdateCalculations should update A2 programmatically. AutoRecalc is set to TRUE and the test code is below for the Timer's OnTimer event:


procedure TForm1.Timer1Timer(Sender: TObject);

var

 S : String;

begin
 TMSFNCSpreadGrid1.Floats[1,1] := TMSFNCSpreadGrid1.Floats[1,1] + 1;
 Str(TMSFNCSpreadGrid1.Floats[1,1]:2:2,S);
 label2.Caption := S;

 TMSFNCSpreadGrid1.UpdateCalculations;
 Str(TMSFNCSpreadGrid1.Floats[1,2]:2:2,S);
 label4.Caption := S;
end;          

After compiling the code, the spreadgrid was updated as below:
  1. A1 -> =97
  2. A2 -> =A1*60/1000
The timer was then enabled, thereby incrementing the value in A1. The test code writes the incremented value of A1 to label2, which is updated on the OnTimer event as anticipated. However, the value of A2 is not updated when incrementing A1. The caption for label4 remains unchanged. 

When replacing UpdateCalculations with Recalc, the code works as anticipated. However, calling Recalc is computationally expensive. Could you please confirm whether UpdateCalculations is working correctly? 
2

UpdateCalculations is used for doing calculations for cells that were changed through editing, not via programmatic changes. 
For programmatic changes to cells, only recalc can be used, UpdateCalculations cannot be used as internally there are no cells marked as changed.

3

Thank you very much for the feedback.


Could you please share any recommendations or advice on speeding up the recalculation after a programmatic change. I tried using BeginUpdate and EndUpdate, but this did not change the calculation speed noticeably.

Does FlexCel have a different calculation engine, where for example dependency (calculation) trees minimise the number of calculation when recalculating? 

Much appreciate your kind help. 
4

I'm not a deep expert in the internal Flexcel calculation engine but it is completely different from TTMSFNCSpreadGrid and it is faster (for large data)
To optimize TTMSFNCSpreadGrid usage, try to reduce calls to Recalc. Do first update the maximum nr. of cells that need to be updated and after that call Recalc just once.