Range.HasFormula returns false for cells in dynamic array

Range.HasFormula returns false for cells in dynamic array

Posted by: yyoshimura on 22 October 2024, 10:52 pm EST

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 22 October 2024, 10:52 pm EST

  Hi,

  We have a range defined as dynamic array like ‘=A2:A4*100’ in B2 cell and the formula is expanded to B4 as per the dynamic array definition. When we try to get the HasFormula or HasArray property except the first definition cell B2, e.g. B3 cell the value returns false. We are expecting true because B3 is part of the dynamic array. Please refer attachment for reproduction.

  Please help to look at this issue. Thank you.

  Regards

  IsInDynamicArray.zip

• 

  kartik.dua

  

  • Post Options:
  • Link
    Copy

  Posted 23 October 2024, 12:09 pm EST

  Hi,

  The HasFormula property of the IRange interface returns true only if the formula is present in the cell range i.e. it will return true for cell B2 but not for B3 as the value in cell B3 is the result of SPILL in cell B2.

  Similarly, the HasArray property returns true if the cell range is a part of the Array Formula not of the Dynamic Array Formula

  We also tried to locate all the formulas present in the sheet using MsExcel and it also returns only the first occurrence of the dynamic array formula present in cell B2 and another formula present in cell E2. Please refer to the attached screenshot - MsExcelBehavior.zip

  ---

  However, the behavior you mentioned seems like a valid request as the cell is also able to fetch the Value as per the dynamic array formula. We have shared this as an enhancement request to the development team and will let you know the updates soon.

  [Internal Tracking ID: DOCXLS-11391]

  Best Regards,
  
  Kartik

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 23 October 2024, 9:37 pm EST

  Hi,

  Thank you for your support. We are looking forwad to the enhancement and it will be a lot easier to use the library for our usage.

• 

  kartik.dua

  

  • Post Options:
  • Link
    Copy

  Posted 24 October 2024, 1:57 am EST

  Hi,

  The development team has asked for some details related to your use-case. Could you please let us know your exact requirement for which you need to know if the cell comes under a dynamic array formula?

  Using this information, the development team will further research about the feasibility/requirement of this feature request.

  Thanks, and Best Regards,
  
  Kartik

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 24 October 2024, 9:49 pm EST

  Hi

  The use case is when we change the value of A3 to 6 and then get value of cell B3, we hope the latest result 600 should be returned. The current behavior of the library is returning 0 which is not correct. If there is a property like [IsInDynamicArray] to indicate the cell is part of dynamic array and if there is property like [DynamicArrayDefinitionRange] of cell B3, we can set the [DynamicArrayDefinitionRange] range to dirty when [IsInDynamicArray] is true in our code so that we can achieve what we want as you suggested in [https://developer.mescius.com/forums/documents-excel/values-in-dynamic-array-not-calculated-as-expected]. Hope that makes the use case clear.

• 

  kartik.dua

  

  • Post Options:
  • Link
    Copy

  Posted 25 October 2024, 5:50 am EST

  Hi,

  It seems like this thread and case https://developer.mescius.com/forums/documents-excel/values-in-dynamic-array-not-calculated-as-expected has come to a similar requirement. Therefore, let’s continue the thread on that case as all the details/scenarios has been discussed there.

  Best Regards,
  
  Kartik