Check if a formula is valid before assigning it to a cell

Aspose.Cells Product Family
1

Hi,

In Excel, if I try to enter the following formula in a cell “=CELL[INVALID]”, Excel tells me that the formula has a problem.

Is there a way to check if a formula is valid before I assign it to Cell.Formula? I want to set a blank value on the Cell instead of assigning the formula if it is invalid.

Thanks.

2

@vonH you can try calculating the formula before using the method CalculateFormula, in example:

var results = worksheet.CalculateFormula("=Sum(A1:A2)");

You can learn more about formula calculations here

3

Hi @eduardo.canal,

I tried your suggestion and it does tell me whether a formula may have errors or not (e.g. #REF). However, I was hoping there was a sort of parse method that will return a bool (or an exception) to indicate whether the formula is valid.

Furthermore, I think it might not work in all cases. As an example, I have the following formulas:

  • =CELL[5]-CELL[INVALID]
  • =SUM[X]

In Excel, both formulas will result in a formula error and I’m prevented from using them.

However, when I called CalculateFormula:

  • “CELL[5]-CELL[INVALID]” returned a #REF! which tells me that there is an error.
  • “SUM[X]” returned 23 instead of an error.
4

@vonH,
Would you like to provide your sample file and executable Console project? We will check it soon.

5

AsposeTest.zip (8.5 KB)

Hi,

Please see attached file. The sample file is actually just a blank spreadsheet. The formulas I used are just samples, but can actually be anything. Which is why I was looking for a method that validates the formula when I try to assign it to a cell (same as what Excel does).

Thanks.

6

@vonH,

You provided just application EXE file which might not help much. We need a standalone VS.NET console application (source code without compilation errors) to trace your issue. Please do the needful and provide us the sample project with resource files. We will check your issue soon.

7

@vonH I was able to reproduce your issue, this is the code that I use:

// Create a workbook
Workbook workbook = new Workbook();

// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
var cell = worksheet.Cells["A1"];

// The Exception is not throwed
try
{
    cell.Formula = "=invalid(X)";
}
catch (CellsException ce)
{
    Console.WriteLine("The formula is not valid: " + ce.Message);
}

// This return 23
var results = worksheet.CalculateFormula("=Invalid[X]");
8

Hi @amjad.sahi, see attached file. AsposeTests.zip (5.6 MB)

Hi @eduardo.canal, my code is similar to yours except that I don’t have that try…catch block since I noticed that an exception is never thrown at least for the invalid formulas I’ve tried so far.

9

@vonH,

Thanks for the sample project.

We reproduced the issue you mentioned where Aspose.Cells incorrectly calculates invalid formulas via Worksheet.CalculateFormula 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-53132

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.

1 Like
10

@vonH,

Are you intending to use table reference(TableName[TableColumn]) or “[]” is just used to call a function in your region(just like “()” in common regions such as en_US)?

If you intend to use table reference, by our test it seems ms excel does not allow such kind formula when the table or table column does not exist. We need to investigate whether we can throw exception for you when setting such kind of formula. If we cannot change current behavior for setting such kind of formula, at least we will make the calculated result be something like #REF! instead of current value “23”.

If actually you are intending to call function like =invalid(X), please use “()” instead of “[]”, we only support the syntax of en-US region when you setting formulas by Cell.Formula.

And for some “invalid” formulas, we think maybe you are talking about the calculated result of the formula. For example, for formula “=SUM(A1:A2)”, if A1’s value is error, then this formula’s calculated result will be error too. Such kind of formula itself cannot be taken as “invalid” and whether its result is error can only be detected by calculation. So, we cannot show error or other message when setting such kind of formulas. You should check the calculated result of CalculateFormula to check whether the returned value is some kind of error such as #VALUE!, #NAME?, …

11

@vonH,

Your issue has been resolved now. The fix/enhancement will be included in our upcoming release (Aspose.Cells v23.4) that we plan to release in the next week. From v23.4, we will throw exception when setting formula with invalid table reference(table or column does not exist) to cell. When calculating such kind of formula by Worksheet.CalculateFormula(formula), we will return “#REF!”.

1 Like
12

Thanks @amjad.sahi! I’ll check it out once that version is available.

13

@vonH,

You are welcome. We will notify you once the new version is published.

14

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

1 Like