Add "IFERROR" formula

[abk.964]

I would like to add IFERROR formula. Al least with some limits.

Something like this:

Code: Pascal  [Select][+][-]

1. procedure fpsIFERROR(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. // IFERROR( value; value_if_error )
3. // If "value" is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?
4. // or #NULL), this function will return Args[1]. Otherwise, it will return Args[0].
5. var
6.   cell: PCell;
7. begin
8.   if (Args[0].ResultType = rtCell) then
9.   begin
10.     cell := ArgToCell(Args[0]);
11.     if (cell <> nil) and (cell^.ContentType = cctError) and (cell^.ErrorValue <= errArgError)
12.       then Result := Args[1]
13.     else result := Args[0];
14.   end else
15.   if (Args[0].ResultType = rtError) then
16.     Result := Args[1]
17.   else
18.     Result := Args[0];
19. end;
20.  

Add:

Code: Pascal  [Select][+][-]

1.     AddFunction(cat, 'IFERROR',   'S', '?S',    INT_EXCEL_SHEET_FUNC_IFERROR,    @fpsIFERROR);
2.  

Const:

Code: Pascal  [Select][+][-]

1.   INT_EXCEL_SHEET_FUNC_IFERROR    = 370;
2.  

Not sure, that it's right.
Any corrections are welcome.

PS. I use xlsx files.

[wp]

Yes it seems to work for xlsx and ods. However, it does not work for xls; the file created cannot be read by Excel without error, maybe because the BIFF format does not support it? It is possible to re-enter the formula in Excel (I have v2016), but when saving it tells that it cannot write the formula and must modify it. Looking at the written xls file with the BIFFExplorer it can be seen that the formula identifier code has been written as value 255, and a "defined name" record has been created with the value "_xlfn.IFERROR". fpspreadsheet does not support defined names, so far. But since Excel does not seem to be able to assign any code to it, it does not matter. It is my impression that 255 is the identifier for "formula unknown". My question is: where did you get the value INT_EXCEL_SHEET_FUNC_IFERROR=370 from?

xlsx and ods files, however, can be written correctly. Since they are more important than xls, I applied your code to the svn version.

[abk.964]

I'd got 370 as some unused number, so asking for correction. Let`s set 255.
Is result and parameters type right in AddFunction? ('S' and '?S')
Can range be 1 parameter of function IFERROR?

[wp]

Is result and parameters type right in AddFunction? ('S' and '?S')

The input parameters certainly are correct since the first parameter is a cell and the second one a message text. Not sure about the result parameter, I tend to set it to '?' because it returns the cell when there is no error.

Can range be 1 parameter of function IFERROR?

You mean whether it is possible to use a range in the first parameter? I think, in Excel you can, but not in fpspreadsheet which does not support array formulas.

[abk.964]

ОК. Thanks.