CalcFormulas function not working for formula INDIRECT

[veb86]

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

1. sh.WriteFormula(1,2,'INDIRECT("Sheet3!D1")'); \\  << Sheet3!D1 does not exist
2. sh.CalcFormulas; << Bug, the program freezes. It must be too #N/A
3.  

This is very bad when code execution leads to crashes in the program.
I think that if a sheet is written in the formula that does not exist, then the result of executing the formula should be an error of the type ("#N/A")

Please solve this problem

[wp]

I think I have solved the problem with INDIRECT.

I came to a similar solution, but am setting also the Result.Worksheet which is left dangling in yours. Please test the current svn version which also catches the case of a non-existing worksheet.

[EDIT]
Switched to your implementation which is more logical and shorter.
BTW, added also some unit tests regarding the new functions. If you have an idea about what is missing, please tell me.

[veb86]

Added the basic INDEX formula: INDEX(range, row, col) to get you started. It omits the cases where a cell range should be returned.

Not completely correct.
col - optional parameter.

If the range is one-dimensional vertically or horizontally, then only the 2nd value is sufficient. They indicate which value to take from the beginning of the range.
If the range is two-dimensional, then a third value is added. The second value is a rows. The third value is the column.

I think we need to make an INDEX to this temporary state.

[veb86]

I think we need to make an INDEX to this temporary state.

INDEX. Test file shows how it works

[wp]

Fixed.

There is another missing option, namely row or column index specified to be zero, which returns an entire row or column: This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far.

[veb86]

This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far.

I'm very upset. These are the basic functions of the formula engine. I am now at the stage of developing a large Excel workbook for complex calculations. I'll try not to use this.
How difficult is it to add this functionality?

[wp]

There is another missing option, namely row or column index specified to be zero, which returns an entire row or column: This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far.

Added the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working.

[veb86]

Added the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working.

Great. Very cool, thank you very much.

All I have to do is add 3 formulas (SUMIFS, COUNTIFS and IFS) to get the same results as Excel through OLE control.
SUMIFS, COUNTIFS and IFS differ from SUMIF, COUNTIF and IF only in multiple conditions.
I saw in the code the implementation of SUMIF, COUNTIF and IF; they are all tied to the DoIF function.
Please look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function?

SUMIFS:
https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
COUNTIFS:
https://exceljet.net/functions/countifs-function
IFS:
https://exceljet.net/functions/ifs-function

[veb86]

Added the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working.

Great. Very cool, thank you very much.

All I have to do is add 3 formulas (SUMIFS, COUNTIFS and IFS) to get the same results as Excel through OLE control.
SUMIFS, COUNTIFS and IFS differ from SUMIF, COUNTIF and IF only in multiple conditions.
I saw in the code the implementation of SUMIF, COUNTIF and IF; they are all tied to the DoIF function.
Please look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function?

SUMIFS:
https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
COUNTIFS:
https://exceljet.net/functions/countifs-function
IFS:
https://exceljet.net/functions/ifs-function

Don't waste your energy on this task. I'll add it myself.
In my project I select equipment. And it would be very cool if you added the ability to copy and add validation lists. Is this really possible?
https://www.powerusersoftwares.com/post/2018/07/31/how-to-create-simple-or-dynamic-drop-down-lists-in-excel-and-why-you-should

I asked this a long time ago, but you didn't answer. For me, this is about creating ease of use. It would be very cool if it was. But it’s not critical if there are no validation lists

[wp]

Please look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function?

I refactored the ugly DoIF function into a dedicated class for evaluation, and then was able to add SUMIFS, COUNTIFS, and AVERAGEIFS. IFS is still missing because it has a different logic in the arguments.

A few tests with files created from Microsoft examples showed that the functions seem to work - mostly. There are a few limitations, though:
- In string comparisons, Excel allows wildcards - this is not supported ATM.
- When a cell contains a condition which is referenced by the formula, the expression parser so far does not reckognize the argument as a cell reference but assumes it is a string. Therefore, something like SUMIFS(A1:A10, B1:B10, C1) where C1 contains the string ">10" is not calculated correctly.

[veb86]

- In string comparisons, Excel allows wildcards - this is not supported ATM.

This is very similar to regular expressions. In my work I do not use such opportunities.

- When a cell contains a condition which is referenced by the formula, the expression parser so far does not reckognize the argument as a cell reference but assumes it is a string. Therefore, something like SUMIFS(A1:A10, B1:B10, C1) where C1 contains the string ">10" is not calculated correctly.

I didn't know that this was possible. I don't use this either, but it's an interesting possibility.

I refactored the ugly DoIF function into a dedicated class for evaluation, and then was able to add SUMIFS, COUNTIFS, and AVERAGEIFS

You greatly changed the way formulas with conditions work, it turned out very well. I couldn't do that, I have a weak level of knowledge. I'm leaving for a few days, and when I get back I'll test it right away. Thank you!