Forum > FPSpreadsheet

Add "IFERROR" formula

(1/1)

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

Something like this:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure fpsIFERROR(var Result: TsExpressionResult; const Args: TsExprParameterArray);// IFERROR( value; value_if_error )// If "value" is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?// or #NULL), this function will return Args[1]. Otherwise, it will return Args[0].var  cell: PCell;begin  if (Args[0].ResultType = rtCell) then  begin    cell := ArgToCell(Args[0]);    if (cell <> nil) and (cell^.ContentType = cctError) and (cell^.ErrorValue <= errArgError)      then Result := Args[1]    else result := Args[0];  end else  if (Args[0].ResultType = rtError) then    Result := Args[1]  else    Result := Args[0];end;  
Add:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---    AddFunction(cat, 'IFERROR',   'S', '?S',    INT_EXCEL_SHEET_FUNC_IFERROR,    @fpsIFERROR); 
Const:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---  INT_EXCEL_SHEET_FUNC_IFERROR    = 370; 
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:

--- Quote from: abk.964 on January 30, 2024, 08:21:05 am ---Is result and parameters type right in AddFunction? ('S' and '?S')

--- End quote ---
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.


--- Quote from: abk.964 on January 30, 2024, 08:21:05 am ---Can range be 1 parameter of function IFERROR?

--- End quote ---
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.

Navigation

[0] Message Index

Go to full version