Macro: EDATE in setFormula goes lowercase

arthurvnc · April 29, 2022, 11:13pm

Hello everyone,

I’m making a macro to set a formula in a cell with EDATE in the middle, one of the formulas that I need is:

‘=IF($F10>TODAY();"";EDATE($G10;$E10))’
but the result is making “EDATE” lowercase and breaking the cell data:
‘=IF($F10>TODAY();"";edate($G10;$E10))’

I have others cells in this sheet that use EDATE and all the others functions stay uppercase after running the macro, except EDATE that goes lowercase… Is there any way to force it keep uppercase?

This is the code that I’m using for the example above:

sDESPESAS=ThisComponent.Sheets.getByName("DESPESAS")
firstRowD=10
sDESPESAS.getCellByPosition(7,firstRowD-1).setFormula("=IF($F"&(firstRowD)&">TODAY();"""";EDATE($G"&(firstRowD)&";$E"&(firstRowD)&"))")

mikekaganski · April 30, 2022, 6:04am

In any such cases, when you see .setFormula failing, it helps to test MsgBox(.getFormula(...)) on a cell formula that you created manually in UI and that works.

joshua4 · April 30, 2022, 12:05am

While EDATE is an ODF standard function, you may have to call it from Analytics in LO. Try:

com.sun.star.sheet.addin.Analysis.getEdate

in your string instead of just EDATE.

sDESPESAS.getCellByPosition(7,firstRowD-1).setFormula("=IF($F"&(firstRowD)&">TODAY();"""";com.sun.star.sheet.addin.Analysis.getEdate($G"&(firstRowD)&";$E"&(firstRowD)&"))")

I know that might seem crazy, but a nice, pretty EDATE should pop up in the target cell, just like you want.

arthurvnc · April 30, 2022, 1:08pm

Your formula also worked joshua4, thanks.

But karolus’s solution is easier to remember, just change setFormula for FormulaLocal and remove the parenthesis.

Probably there is other functions that have the same macro problem with lowercase, karolus’s code worked without need to call something else, maybe FormulaLocal is the best fix this kind of problem.

joshua4 · April 30, 2022, 2:38pm

FormulaLocal sounds fine if it works. However, notice that it isn’t a problem with lower case, nor parentheses.

The lower case shows up because Calc does not recognize the symbol EDATE when entered via the method setFormula(). This is because setFormula is not designed (deliberately or not) to look at com.sun.star.sheet.addin.Analysis symbols, apparently. FormulaLocal, however, is, since LO does recognize the symbol EDATE when entered via the property FormulaLocal.
A property is set using equality semantics, cell.FormulaLocal = "=A5". A method takes a value as a parameter, cell.setFormula("=A5").

karolus · April 30, 2022, 12:07am

sDESPESAS.getCellByPosition(7,firstRowD-1).FormulaLocal = "=IF($F" & firstRowD &">TODAY();"""";EDATE($G"&(firstRowD)&";$E"&(firstRowD)&"))"

to avoid all this quoting- and concatenation-mess just use python with:

sDESPESAS.getCellByPosition(7,firstRowD-1).FormulaLocal = f"""=IF($F{firstRowD}>TODAY();"";EDATE($G{firstRowD};$E{firstRowD}))"""

arthurvnc · April 30, 2022, 1:10pm

It worked flawless, thank you karolus.

And about quoting and concatenation, I totally agree with you, a complete mess.

mikekaganski · May 1, 2022, 8:57pm

Please note that using FormulaLocal looks like an ugly hack. The code written this way is destined to fail on anything except current locale. Change to a locale with localized function names, or with different decimal separators, and it breaks.

The function should be accepted using Formula, since it’s standard ODF; IMO it simply deserves a bug report (and eventual fix, preserving old com.sun.star.sheet.addin.Analysis function as a compatibility alias). Maybe @erAck has a clarification…

erAck · May 2, 2022, 8:48pm

Not really, would need debugging. For which a bug report with a proper sample would be welcome. Though I remember vaguely having heard such thing but don’t recall whether it was here on Ask or in a bug.

Update 2022-08-03T18:11+02:00
Problem is the formula API predates ODF (with OpenFormula ODFF) and closely follows the old syntax (PODF for Pre-ODF) where all AddIn function names are written/read by their programmatic name, hence the com.sun.star.sheet.addin.* names. Would need tweaking to at least accept the English names if known.

Filed as tdf#150253.

Villeroy · April 30, 2022, 6:41am

EDATE is an add-in function. The add-in is shipped with this office suite. All these functions are not part of the ODF standard. The full name of EDATE as reported by getFormula is com.sun.star.sheet.addin.Analysis.getEdate

EDATE is obsolete. The DATE function can calculate dates by calculated year, month and day numbers.

=com.sun.star.sheet.addin.Analysis.getEdate(A1;B1)
is equivalent to
=DATE(YEAR(A1);MONTH(A1)+B1;DAY(A1)))
calculating the date B1 months ahead of A1.

mikekaganski · April 30, 2022, 7:16am

@joshua4 mentioned the correct fact that it is part of ODF.

Additionally, your formula id not equivalent to EDATE, since it doesn’t account for the different days in the end month, that could need to adjust 31th to, say, 30th or 28th.
And also DATE is not necessarily able to handle months outside of 1…12 correctly; standard requires only the mentioned range, and only permits implementations to do what they want with the values outside of the constraints - so that formula would be non-standard.

sokol92 · April 30, 2022, 1:02pm

The closest relative is Basic DateAdd function (with “m” as the first parameter).

arthurvnc · April 30, 2022, 1:28pm

My problem with this code is exactly with the 29th, 30th and 31th day of the month, as mikekaganski wrote.

I’m using this formula to track monthly payments and I need to have at least one payment per month.

A1: 12/30/21
EDATE(A1;2) → Result: 02/28/22
DATE(YEAR(A1);MONTH(A1)+2;DAY(A1)) → Result: 03/02/22 (Skipped February)

I can use IF inside to solve this problem, but I would have a long formula, EDATE is short and simple.

Villeroy · April 30, 2022, 2:01pm

Well, then use something like

oCell.setFormula("=com.sun.star.sheet.addin.Analysis.getEdate(A1;B1)")