Forums Home / Document Solutions / Document Solutions for Excel, .NET Edition
Posted by: yyoshimura on 22 October 2024, 11:01 pm EST
yyoshimura
Posted 22 October 2024, 11:01 pm EST
Hi,
We have a dynamic array defined in cell B2 with formula ‘=A2:A4*100’. We are setting DeferUpdateDirtyState to true and setting value to A2:A4 and then setting EnableCalculation to true. We expect to get value from cell B3 with calculated value 300 but we got 0 instead. Please see attachment for reproduction.
Please help to look at this issue. Thank you.
Regards
kartik.dua
Posted 23 October 2024, 2:51 am EST
Hi,
The formula is already in the Xlsx file you are loading in the Workbook, so its state is not dirty. Changing the values of precedents present in the formula will not automatically update the values generated by the formula.
To update the values of the cells B2, B3, and B4, it is required to set the formula to dirty state first using the Dirty method of the workbook.
Refer to the attached sample that sets the formula to dirty state which forces the recalculation of the formula. As you have already set DeferUpdateDirtyState to true, the formula is recalculated as soon as it goes to dirty state.
Attachment: DynamicArrayNotCalculated_Updated.zip
Please feel free to reach out if you encounter any further issues or if any further guidance is required.
Best Regards,
Kartik
yyoshimura
Posted 23 October 2024, 9:29 pm EST
Hi,
Thank you for the clarification. Is is right to think that when Dirty() method is called, all the formulas in workbook will be recalculated and may impact performance? Other formulas are working perfectly when DeferUpdateDirtyState is set to true, so for this dynamic array formula to work correctly there is no way to know when is the proper timing to call Dirty() method. If we need to call it whenever cell value changed the performance degradation is a problem and the meaning of DeferUpdateDirtyState is becoming obscure.
If we don’t call the Dirty() method, the first time to get B3 cell value is 0 but if we get B2 cell value first and then get B3 cell value we can get the correct result. I think this behavior is somehow inconsistent. Please refer attachment.
We hope that the dynamic array formulas behaving the same as other formulas when DeferUpdateDirtyState is true because the performance of the library is always our main concern.
kartik.dua
Posted 24 October 2024, 2:23 am EST
Hi,
You are right that when the workbook’s Dirty method is called, all the formulas present in the workbook are recalculated and may impact performance. To tackle this scenario, you can use IWorksheet’s Dirty method (updates state of all the formulas present in the worksheet) or IRange’s Dirty method (updates state of all the formulas present in the specified range) instead of Workbook’s Dirty method.
When DeferUpdateDirtyState is true, other formulas of the worksheet work perfectly without calling the Dirty method because when a range containing the formula is accessed, the value of the cells containing the formula are recalculated and updated values are returned.
Due to the same reason as above, when you access cell B3 without calling the Dirty method, the value is 0 and when you access the cell B2 containing the formula, the values are recalculated and cell B3 returns value 600.
Please refer to the attached sample that updates the value of cell B3 before cell B2 is invoked by just setting the state of cell B2 to dirty. Please feel free to reach out if you have any further issues or if any further guidance is required.
Attachment: DynamicArrayNotCalculated_updated3.zip
Best Regards,
Kartik
yyoshimura
Posted 24 October 2024, 9:59 pm EST
Hi
Thank you for the explanation. We understand the behavior of setting range to dirty. However, to achieve what we want, for example, when setting value 6 to cell A3, we need to check the dependent ranges of A3 and do the dirty setting for those ranges and we need to do this kind of work for every cell value change which may still impact performance because we have lots of cell value change in one process. That is why we are requesting some ways to know whether the cell is part of dynamic array and when it is so we can do the dirty setting only for the related cells as stated in [https://developer.mescius.com/forums/documents-excel/range-hasformula-returns-false-for-cells-in-dynamic-array].
kartik.dua
Posted 25 October 2024, 5:47 am EST
Hi,
We have shared your concerns with the development team to get their insights on this and will let you know the updates soon.
[Internal Tracking ID: DOCXLS-11391]
As for the case https://developer.mescius.com/forums/documents-excel/range-hasformula-returns-false-for-cells-in-dynamic-array, it seems both these threads has come to the similar requirement. Therefore, lets continue the thread here only to avoid confusions.
Best Regards,
Kartik
kartik.dua
Posted 29 November 2024, 12:56 am EST
Hi,
Apologies for the delay in response.
As per the development team, the use of Dirty method should not be required in this scenario. The cell value of B3 should always be correct as per the updated array formula. This seems like a bug and they will be fixing it in an upcoming release.
Fixing this bug should eliminate the need of the two properties you mentioned in your other thread. Could you please confirm whether your requirements will be met once we have fixed this bug?
Thanks, and Kind Regards,
Kartik
yyoshimura
Posted 29 November 2024, 2:20 am EST
Hi,
Thank you for your response. Yes, if we can get the correct values the two properties in other thread are not needed. It is the best result we can get and we really appreciate the fix.
kartik.dua
Posted 29 November 2024, 2:43 am EST
Hi,
Thanks for the confirmation. We will update you as soon as the bug is fixed.
Best Regards,
Kartik
