Bug 134032 - Formula cell used in macro with function EOMONTH() or FIN:MOIS() giving #NAME?
Summary: Formula cell used in macro with function EOMONTH() or FIN:MOIS() giving #NAME?
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.4.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Olivier Hallot
URL:
Whiteboard: target:7.1.0 target:7.0.0.1
Keywords:
Depends on:
Blocks:
 
Reported: 2020-06-16 10:10 UTC by olivier_musson
Modified: 2020-06-17 11:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description olivier_musson 2020-06-16 10:10:01 UTC
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.
Comment 1 Mike Kaganski 2020-06-16 10:49:05 UTC
(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)"?
Comment 2 olivier_musson 2020-06-16 10:51:48 UTC
Here is the text of the macro used to reproduce the problem:

Cell = oSheet.getCellRangeByName("A2")
Cell.Formula = "=EOMONTH(A1;1)"
Comment 3 Mike Kaganski 2020-06-16 10:59:07 UTC
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
Comment 4 olivier_musson 2020-06-16 11:14:10 UTC
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.
Comment 5 Mike Kaganski 2020-06-16 11:27:56 UTC
(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
Comment 6 olivier_musson 2020-06-16 11:51:42 UTC
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.
Comment 7 Commit Notification 2020-06-17 11:39:26 UTC
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
Comment 8 Commit Notification 2020-06-17 11:54:00 UTC
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