Bug and crash. I think that the cell calls itself

[veb86]

Bug and crash. I think that the cell calls itself
I know that the cell should not call itself. This mechanism is not implemented. But the presence of a cell that calls itself should not cause the program to crash.
I tried to localize the problem to find out exactly what causes such an error. But it does not work. After transferring this piece of calculation to a new book, the calculation in the new book works correctly.

Bad bug (The program simply closes, the reason can only be found by debugging. Below are screenshots
I am throwing off my entire calculation, see what the problem could be

PS I understand that calling a cell to itself is not currently implemented. But I think in the future this will have to be done, many calculations contain such an element. And immediately the incorrect behavior of the library will scare off its use.

PPS For me this is not critical

[wp]

What do you mean with "a cell calls itself"? A circular reference? This is detected by FPSpreadsheet: it raises an exception and writes a message to the error log:

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

1. program project1;
2. uses
3.   SysUtils, FPSpreadsheet, FPSTypes, fpsExprParser, xlsxOOXML;
4. var
5.   b: TsWorkbook;
6.   sh: TsWorksheet;
7.   cell: PCell;
8. begin
9.   b := TsWorkbook.Create;
10.   sh := b.AddWorksheet('Test');
11.   cell := sh.WriteFormula(0,0,'=A1');
12.   b.CalcFormulas;
13.   WriteLn('Cell A1 = ', sh.ReadAsText(cell));
14.   if b.ErrorMsg <> '' then
15.     WriteLn('ERROR: ', b.ErrorMsg);
16.   b.WriteToFile('CircRefError.xlsx', true);
17.   b.Free;
18. end.  

BTW, Excel is doing a very bad job here. When I type in cell A1 the formula "=A1" it does report the circular reference, but then displays in the cell the value 0. Any calculation based on this cell displays 0 again, which makes is very difficult to find bugs in the worksheet's formulas.

Could you simplify your test workbook? With myriads of formulas debugging is a hell...

[veb86]

Could you simplify your test workbook? With myriads of formulas debugging is a hell...

I will analyze the book in parts and check for errors. I was unable to localize the error quickly(((
I need time to reduce the book.

What do you mean with "a cell calls itself"? A circular reference?

Yes. I explained it poorly, plus the translator distorts the conveyed meaning.

BTW, Excel is doing a very bad job here. When I type in cell A1 the formula "=A1" it does report the circular reference, but then displays in the cell the value 0. Any calculation based on this cell displays 0 again, which makes is very difficult to find bugs in the worksheet's formulas.

I agree, circular references look bad in calculations, and can lead to errors. I will remove circulation links from my calculations.
I think that the behavior of the calculation module should be the same as that of the giants: Excel and LibreCalc.

[veb86]

Could you simplify your test workbook? With myriads of formulas debugging is a hell...

I did not find the function that causes the program to crash.
It is COUNTIF. If an error is given to the input of the 1st argument, the program crashes.

I am not the only such function. Because there was another crash log. I am waiting for the COUNTIF to be fixed and continue testing.

[wp]

Fixed.

[veb86]

2 more bugs: 1st - error, 2nd - crash.
1st:
The MATCH function does not correctly determine the position by the composite value. It determines the position by the resulting piece of text. See the example.
2nd:
This bug is more complicated, it causes a crash. The bug is complex, it is caused by MATCH, when the second argument instead of a range, comes an unknown parameter. INDERECT returns emptiness. You need to test it yourself. When I analyze the cells in parts, the bug disappears.
I have prepared an example. I am waiting for both to be fixed, and continue testing

[VisualLab]

I think that the behavior of the calculation module should be the same as that of the giants: Excel and LibreCalc.

But how to reconcile this? Because Excel behaves indecently. However, LibreOffice Calc is quite decent, i.e. in a cell with a circular reference it displays: Err: 522.

[veb86]

But how to reconcile this? Because Excel behaves indecently. However, LibreOffice Calc is quite decent, i.e. in a cell with a circular reference it displays: Err: 522.

I didn't know about this behavior of librecalc.
Then you need to write an error so that the author of the book removes the circular reference.
I was thinking about circular references, and I agree with the author of the library, this is not normal Excel behavior. It is better to immediately avoid the inherent error at the calculation level

[VisualLab]

Then you need to write an error so that the author of the book removes the circular reference.
I was thinking about circular references, and I agree with the author of the library, this is not normal Excel behavior. It is better to immediately avoid the inherent error at the calculation level

Definitely yes.

[veb86]

I see new commits. Are the bugs fixed?

[veb86]

1st:
The MATCH function does not correctly determine the position by the composite value. It determines the position by the resulting piece of text. See the example.

I checked the commits. This bug is fixed.

2nd:
This bug is more complicated, it causes a crash. The bug is complex, it is caused by MATCH, when the second argument instead of a range, comes an unknown parameter. INDERECT returns emptiness. You need to test it yourself. When I analyze the cells in parts, the bug disappears.

This bug causes the program to crash.
I understand that I have incorrectly defined functions with cyclic references (it won't take long to rewrite). But errors in functions should not cause the program to crash

[wp]

Now I was able to fix a series of bugs involved in your 2nd error xlsx file. Please test.

[veb86]

Great! This is the first time my book hasn't broken during calculation.
There are many cyclic references in the book being tested.
I have already started removing cyclic references from the calculation. In order to continue testing. So far, another bug has been identified, which causes slow calculation. I will describe it in a new topic