Description: Hi, I am using a macro with the EOMONTH() function but when the macro is activated instead of working and giving the END OF MONTH results I get a #NAME? In the Options - Libreoffice Calc - Formula I have been ticking the box "Use function name in English". In my macro I am using the DATE(YEAR()) which is working perfectly well. To have the result I want at the end of the macro, I need for each calc sheet to go again in Option - Libreoffice Cal - Formula and tick the box do APPLY and OK few times. Ticking "Use function in English or not as no impact. What has an impact is to tick it and do apply or ok whatever the choice is. I don't know if it is a bug or not or a language problem but it's like the function EOMONTH() or FIN.MOIS() is misinterpreted whatever the language is. I tried both functions and each time I re-execute the macro it doesn't work. Before this function EOMONTH() was hard coded in the calc sheet cell and worked perfectly. But now I need it in a macro... Many thanks in advance for your help. Regards. Olivier My LO environment is configured all in French. Steps to Reproduce: 1. Create a macro using the function EOMONTH(A1;1) 2. Write a date in A1 with DATE format 3. In A2 cell execute the macro Actual Results: #NAME? Expected Results: Computed End of the month from cell A1 value Reproducible: Always User Profile Reset: Yes Additional Info: It seems to be only when I use EOMONTH() or FIN.MOIS() in a macro.
(In reply to olivier_musson from comment #0) > Steps to Reproduce: > 1. Create a macro using the function EOMONTH(A1;1) > 2. Write a date in A1 with DATE format > 3. In A2 cell execute the macro What is the text of "a macro using the function EOMONTH(A1;1)"?
Here is the text of the macro used to reproduce the problem: Cell = oSheet.getCellRangeByName("A2") Cell.Formula = "=EOMONTH(A1;1)"
It's best if you provide the spreadsheet with the macro that shows the problem. However, the problem with EOMONTH is likely because it's actually an add-in function, and thus its internal name (that is used when assigning/reading cell's formula using get/setFormula()) is "com.sun.star.sheet.addin.Analysis.getEomonth". To test, put EOMONTH formula in A1, and call this: > sub getFormula > oCell = ThisComponent.Sheets(0).getCellByPosition(0,0) > msgBox oCell.getFormula() > end sub It's OK that it needs you to use the locale-independent name like that "com.sun.star.sheet.addin.Analysis.getEomonth" instead of user-visible "EOMONTH" in setFormula; the problem is that the function is not mentioned as add-in function in help [1], [2]. [1] https://help.libreoffice.org/6.4/en-US/text/scalc/01/04060111.html [2] https://help.libreoffice.org/6.4/en-US/text/scalc/01/func_eomonth.html
Mike, I used com.sun.star.sheet.addin.Analysis.getEomonth() as you recommended instead of EOMONTH() or FIN.MOIS() in my failing macro and now it's working as designed. In my test or production macro it's working perfectly now. So I guess I can continue like this and you can close the bug well as it's not really a bug? What do you think? Thank you very much for your help.
(In reply to olivier_musson from comment #4) > So I guess I can continue like this and you can close the bug well as it's > not really a bug? What do you think? Great it's solved for you; and thank you for filing this. I consider this still a bug of documentation. Setting it to new. Not having a hing that this is an add-in function does not allow users to even guess its internal name could be different. Also, both in Add-In functions help page, and in help section about "Calling Calc functions in Basic" [1], it would be nice to mention this detail about internal names being different (maybe even add the "API name" section for each such function?). [1] https://help.libreoffice.org/7.0/en-US/text/sbasic/shared/03131600.html
Yes I didn't know that it was add-in function so I agree with you about the documentation's bug. And if there are other add-in functions yes it would be great to know the list somewhere in the help section. Thank you again for your help.
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/7af288d0fa90e5e31c29014501f06f776a00366a tdf#134032 Calling Calc function from basic
Olivier Hallot committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/help/commit/cc69c3dde48f5038567ae0b964b9ef9b5f302748 tdf#134032 Calling Calc function from basic