Formula with mixing TEXTJOIN and concatenate with named range returns "#VALUE!" and works with Excel

Aspose.Cells Product Family
1

Hello,

I am encountering an issue with the formula {=TEXTJOIN(“;”, TRUE, “'” & NAMEDRANGE & “'”)}. After calling the Cell.Calculate method, Cell.Value returns ‘#VALUE!’.

Here is an example of my excel file:
Test_V1.1.zip (14,2 Ko)

2

@flotoz,

Thanks for the sample Excel file.

Which version of Aspose.Cells for .NET you are using? I tested your scenario/case using your template XLSX file with Aspose.Cells for .NET v24.7 and it works fine. I got expected results for the formulas in B52, B53 and B54 cells in the worksheet. Could you please try your scenario/case using our latest version/fix: Aspose.Cells for .NET v24.7. If you still find the issue, kindly do share your sample (runnable) code to demonstrate the issue on our end. We will check your issue soon.

3

Hello,
Thank you for your prompt response.
I can confirm the issue that occurs when forcing the recalculation of cell values instead of using the buffered value computed by Excel.
I have attached an example code to refresh the value of these cells.
ProgramAspose.zip (693 Octets)

Please don’t hesitate to contact me if you have any difficulty reproducing the issue.
Best regards,
Florian

4

@flotoz
By testing with sample file and code on the latest version v24.7, we can reproduce the issue. Discovered that resetting the formula resulted in incorrect calculation results.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-56328

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

@flotoz,

We evaluated your issue further. The underlying formulas in B52 and B53 cells are dynamic array formulas, so, you may use SetDynamicArrayFormula() method instead of Formula attribute to re-insert or re-set the formulas. You may simply change the line of code:
cellWrite.Formula = cellWrite.Formula;
to:
cellWrite.SetDynamicArrayFormula(cellWrite.Formula,new FormulaParseOptions(){},true);
and it will work as expected.

Let us know your feedback.

6

It’s working, thank you all the team!

7

@flotoz,

You’re welcome. It’s good to hear that the suggested line of code resolved your issue. Please feel free to reach out if you have any more questions or comments.

8

If I have to build and fill an Excel file from scratch with a list of formulas, how to know if I need to call SetDynamicArrayFormula or SetFormula automatically?
Best regards,
Florian

9

@flotoz,

I think you may evaluate if a formula is a dynamic array formula via Cell.IsDynamicArrayFormula Boolean attribute.

10

@flotoz.

Just for your information, as your (original) posted issue is not a bug in the Aspose.Cells APIs, we won’t fix it. You may simply set those formulas as dynamic array formulas.