Index array out of bounds error when calling RefreshDynamicArrayFormulas method

Aspose.Cells Product Family
1

Dear Aspose,

In an earlier topic, you fixed two of my issues related to MAP and LAMBDA. (Evaluating dynamic array formula within custom calculation engine issue)

Now I am trying to get rid of the calculation engine extensions, and trying to read the calculation results from the Excel spreadsheet. I am using a template where data is loaded, and for one instance (working.xlsx), I get proper results with the attached code, but for the exception.xlsx, I get an Index out of bounds exception when calling the RefreshDynamicArrayFormulas method.

If I don’t supply the CalculationOptions parameter, then I don’t get the exception, but the results of the calculation are incorrect for some other reason.

Can you please advise for a workaround or let me know how I can fix the template not to get this exception?

Thank you for your help in advance!

ConsoleApp1.zip (247.2 KB)

Regards,
Marton

2

@marton.varsanyi
Test by using sample files and code. We can reproduce the issue. Index out of bounds exception was found when calling RefreshDynamicArrayFormulas method.

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-54680

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.

3

HI @John.He

Thanks. Is there any workaround either by changing the code or the formulas that could help resolving this issue until this is fixed?

Regards,
Marton

4

@marton.varsanyi
I’m afraid there are no alternative solutions before the fix. We will evaluate as soon as possible, and once there is an update, we will notify you promptly.

5

@marton.varsanyi
It is a bug of refreshing complicated dynamic array formulas and we are afraid there is no workaround for it before we solve it and provide the fix.

For your another point:
“If I don’t supply the CalculationOptions parameter, then I don’t get the exception, but the results of the calculation are incorrect for some other reason”

By our test it seems only ‘Countries of tabs’!AV13,AV14,AV21 and AV22 cannot get the correct values. We will investigate it too. If you get different results that what we found at our end, please point out those incorrectly calculated cells so we can solve your issues together.

6

Hello @johnson.shi

See the attached Excel file with the results. The Output sheet has the differences marked, the ListOutput is my main concern, as the length of the arrays in the C column is different in some cases, and some values are not calculated properly (likely due to the issue that you mentioned).

Thanks,
Marton
results.zip (27.6 KB)

7

@marton.varsanyi,

Thanks for providing us with the sample Excel file and details. We will evaluate it and get back to you with further updates.

8

@marton.varsanyi

We have fixed the issue of CELLSNET-54680. Because in your tempalte file the dynamic array formulas are complicated and depend on each other, please use “true” as the first argument for calling RefreshDynamicArrayFormulas(bool, CalculationOptions) which denotes the dynamic array formula will be calculated and set to the correct result when it is refreshed(and so other dynamic array formulas that depend on it can be calculated and refreshed correctly).

Here we also provide the calculated results by the new fix for both your template files(working.xlsx and exception.xlsx) for you to check whether the result is correct. The fix will be included into our next official release 23.12 which may be release in the second week of Dec 2023.
calculatedresults.zip (258.0 KB)

9

Looks good to me, thanks!

10

@marton.varsanyi,

You are welcome.

11

The issues you have found earlier (filed as CELLSNET-54680) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi