Invalid Name error when using a SCAN and Lambda function

Aspose.Cells Product Family
1

I’m generating Excel Workbooks that uses a formula that utilizes the SCAN() function. The SCAN funtion needs a LAMBDA function as input. For example:
=SCAN(0, columname, LAMBDA(acc,n,acc+n))
The Workbook is created without any errors, but upon opening the workbook in Excel the cells show “#NAME?”. When I click the cell I get a exclamation mark telling me there is a Invalid Name Error.
But the strange thing is that when I click the formulabar and hit enter, the cells show the correct value! The formula was not changed in any way (I’ve checked that multiple times). It just doesn’t seem to be evaluated.
I use the following code:
sheet.Cells[2, 2].SetDynamicArrayFormula("SCAN(0,column1,LAMBDA(acc,n,acc+n))", new FormulaParseOptions() { }, true);
Am I doing something wrong, or is the Scan or Lambda function not supported?
Thank you very much for any help on this.

2

@Verycrispy,

Thanks for the details.

As per the supported Excel formulas list, both SCAN and Lambda Excel 365 functions may not be supported by Aspose.Cells formula calculation engine. Do you need to only set the formulas (in Excel workbook to be calculated by MS Excel itself) or you want to calculate the formulas dynamically via Aspose.Cells formula calculation engine, please specify? Anyways, we are going to log a new ticket for your issue/requirements, so we could evaluate it in details.

3

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

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.

4

Hello @Amjad_Sahi,
Thank you for your quick reply.
For now I only need to set the formulas (in Excel workbook to be calculated by MS Excel itself). So if the user opens the workbook, they don’t see a ugly error and don’t need to press [Enter] behind the function in the formula bar.
If you want, I’m happy to share more details or the complete code I’m using.
Thank you.

5

@Verycrispy,

Thanks for your feedback.

We have noted it down and we will try to support setting your desired formulas to the cells in the first phase. Once we have an update on it, we will let you know.

6

Hello @Amjad_Sahi,
I understand this issue will not be fixed in a couple of weeks, but as we’re evaluating if Aspose is a valid option for our situation, I would like to know if you could give us a forecast? Can we expect the basic support for example within the next quarter or could it take more than a year? Or is it to expected that the support will not even make it into to the product?
I hope you can give us some direction as we need to make some decision on moving forward with our project.
Thank you

7

@Verycrispy，

We are working on your requirement of supporting to parse/read/save LAMBDA and SCAN functions now. Hopefully we can include this enhancement into our next official release 23.2 which may be published in one or two weeks.

8

@johnson.shi, Thank you for your quick reply!

9

@Verycrispy,
You are welcome.

10

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

11

It works! Thank you

12

@Verycrispy,

Thanks for your feedback.

Good to know that your issue is resolved by the new version/fix. Feel free to contact us any time if you have further queries or comments, we will be happy to assist you soon.