### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Bug. Formula row and column  (Read 836 times)

#### veb86

• Jr. Member
• Posts: 60
##### Bug. Formula row and column
« on: July 09, 2024, 09:23:16 am »
The row() formula does not work correctly.
If there are no arguments inside the formula, then it returns the number of the line in which it is written. Now if the line is empty, the calculation is not performed.

Code: Pascal  [Select][+][-]
1.
2. procedure fpsROW(var Result: TsExpressionResult;
3.   const Args: TsExprParameterArray);
4. { ROW( [reference] )
5.   Returns the row number of a cell reference (starting at 1!)
6.   "reference" is a reference to a cell or range of cells.
7.   If omitted, it is assumed that the reference is the cell address in which the
8.   ROW function has been entered in. }
9. begin
10.   Result := ErrorResult(errArgError);
11.   if Length(Args) = 0 then
12.     exit;  // We don't know here which cell contains the formula.
13.   case Args[0].ResultType of
14.     rtCell     : Result := IntegerResult(Args[0].ResRow + 1);
15.     rtCellRange: Result := IntegerResult(Args[0].ResCellRange.Row1 + 1);
16.     else         Result := ErrorResult(errWrongType);
17.   end;
18. end;
19.
20.

Code: Pascal  [Select][+][-]
1.
2. if Length(Args) = 0 then
3.     exit;  // We don't know here which cell contains the formula.
4.

If you don't know that row() and column() behave differently in libraries, this can cause errors during operation.
If the value obtained when applying the formula row() is included in min(), then the calculation causes the program to freeze.

Formula column() same error

I am attaching an example file
my code:
Code: Pascal  [Select][+][-]
3.     sh := b.GetFirstWorksheet;
4.     b.CalcFormulas;  // << freeze
5.     b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);
6.

Description of the formula:
https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d
« Last Edit: July 09, 2024, 09:25:42 am by veb86 »

#### wp

• Hero Member
• Posts: 12287
##### Re: Bug. Formula row and column
« Reply #1 on: July 10, 2024, 10:55:14 am »
This one is tough... I have no idea how to solve this without rewriting a lot of the formula engine. The problem is that the cell from which a formula is called is listed in the argument - but when there is no argument there is no way how the formula could find out from where it was called...

#### veb86

• Jr. Member
• Posts: 60
##### Re: Bug. Formula row and column
« Reply #2 on: July 11, 2024, 02:07:49 pm »
This one is tough... I have no idea how to solve this without rewriting a lot of the formula engine. The problem is that the cell from which a formula is called is listed in the argument - but when there is no argument there is no way how the formula could find out from where it was called...

It's not a big problem, I'll fix my calculator. It’s frustrating that the program breaks down when calculating such a formula.