I’ve been using setFormula in my python calc macro for a few days, and it’s worked fine with all the ‘=(A4 * B4) -D3’ formulas I’ve needed. But now I need to use some functions, and it’s acting flaky. For the line:
sheet.getCellByPosition(column, row).setFormula('=MAX(4)')
The cell content is 4
(as expected).
sheet.getCellByPosition(column, row).setFormula('=MAX(4,3)')
The cell content is #NAME?
That is not what I expected.
If I double=click on the #NAME?
cell, I see the correct formula (=MAX(4,3)
). If I then ^A to select the formula, ^X to cut it, and ^V to immediately paste the formula back in, then hit Enter, the cell responds with the correct value of 4
.
This is the simplest form of the problem I could find. I found it because this much longer formula:
setFormula('=-' + quantity_cell + '*100*MAX(((0.25*' + underlying_cell + ')+MAX(' + underlying_cell + '-' + strike_cell + ',0) +' + price_cell + '), (0.15*' + strike_cell + '+' + price_cell + '))')
Behaves the same way, except instead of #NAME?
, the error in the cell is Err:508
. Again, an in-place cut and paste will make the formula work, but I’d like to have calc display it correctly without the intervention.
I suspect this is a bug, but perhaps there’s a work-around?
This is LO 5.1.2.2, and the problem occurs under Ubuntu 16.04 and Windows 10.