With 3.6.3, behaviour A) is still present but behaviour B) appears to have gone away.

In 3.5.7, there is also behaviour A) but not B).

The problem is easier to reproduce using a python script as a macro:
{{{
import uno

def bug54854():
    ctx = uno.getComponentContext()
    desktop = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop",ctx)
    cell = desktop.getCurrentComponent().Sheets.getByName('Sheet1').getCellRangeByName("E5")
    cell.setFormula("=PAGENAME(SHEET(Sheet1.A1)-1)")

g_exportedScripts = bug54854,
}}}

This issue IS still present in 4.0.3. Both unwanted behaviours A and B.

Hello Forester,

These issues don't concern directly LibreOffice. You have to expose them to the extension's author, which is Paul Bryan Roberts at http://www.pbr.org. Only him can answer to you.

Have a nice day,

Jacques Guilleron

Hi Forester,

I wonder if, once added, we have not to close LibreOffice and reload it. The first time I use it, without reloading, I got the same results like you.

Now, for PAGENAME and with the assistant's function, if I enter "Feuille1" with double quote in French UI, I get the correct result. By trying added sheets in this way, I get correct results.

Second function PAGEINDEX work too.

Hope this try can help,

Regards,

Jacques µGuilleron

Sorry, please reverse the names'fuctions for correct reading.

Jacques Guilleron

Hi Forester,

New try with LO 3.6.6.4 on Windows 7.
Install PAGENAME.oxt. Close LO. Reopen. New document Calc. Insert 3 sheets.
Select A1. With Function wizard select PAGENAME formula. Enter 0. Get: Feuille1
in A1 with French UI. Get similar results in A2 and A3 with Feuille2 and Feuille3.

In A4, select PAGEINDEX formula. Enter "Feuille1" double quoted before and after text, get 0 not displayed since "0". Similar results in A5 and A6 with 1 and 2 for "Feuille2" and "Feuille3".

Functions work perfectly in this way.

Jacques Guilleron

Hi Forester,

Its seems that changes in cell A1 are not saved, as in 
[Bug 62938] FORMATTING: Print ranges: Rows to repeat
Try in any other cell work, except that one.
Ca
Regards,

Jacques Guilleron

Salut Jacques,

Last entry of mine seems not to have taken so I will try again.

First, thank you for taking an interest in this bug.  It is 8 months since I filed it and was wondering if anyone was ever going to take notice.  I have more to contribute and will try to put something together over the weekend.

Second, thank you for trying the extension out under Windows™. I am ill equipped to do so myself.  It seems you agree that the add-in functions themselves work.  
Since the bug is still UNCONFIRMED I deduce you have not tried running the macro I supplied that shows the data entry issue.  Why ?  It is the macro, not the add-in, that shows the issue that I filed as a bug report.

A bientôt,
   Forester

Salut Jacques,

The PageName extension is derived from the DoobieDoo example that can be found here:  http://wiki.openoffice.org/wiki/Calc/Add-In/Python_How-To.  The example is 4 years old and was written for the other Office but I was unable to find any other documentation that did not look a lot older and out-of-date.

You can download a zip file from the DoobieDoo page.  This contains an oxt file.  I used this to install the extension (under LibreOffice 3.5.7).  It seemed to install OK and the add-in function seem to work OK as well.

I will attach a simple python script that can be run as a macro from the UI that inserts several simple formula that call the DoobieDoo functions.  The macro does not refer to Sheet1 but you may still have to modify it yourself if your numbers use a «virugle décimal» instead of a decimal point.

When I run the script I get Err:508 for the numeric functions and Err:509 for the string functions.  As with PageName, selecting a cell containing a 'bad formula'  and entering the key sequence F2, SPC, RET causes the function to be re-entered at the UI and the error goes away.

I rebuilt the DoobiBoo oxt file under 3.5.7 using my methods.  I get the same results.  This suggests the package itself is not to blame.

The errors suggest that LibreOffice (some component thereof) is unable to parse the formulae due to a syntax error.  But there is no syntax error as the F2, SPC, RET sequence confirms.

I also infer we do not see the #NAME! error because LibreOffice has given up on a syntax error and has not got as far a trying to look up the function.  I think we do not see a syntax error with the PageName functions because they take only one parameter.  We are perhaps looking at another bug here.

One thing to note is that the first formula the script tries to enter is:
    =DOOBIEMULT(2.5,1.1)
but what I see entered in the spreadsheet is:
    =DOOBIEMULT(2.5,10.1)

where did the extra 0 come from ?  I looks to me what whatever bit of code is trying to parse the formula is having a really bad day.

Ciao

Created attachment 79498 [details]
Python script to be run as a macro to illustrate the issue decrined in the previous comment.

Created attachment 79505 [details]
Python macro illustrating the issue and several workarounds

Salut Jacques,

Attached is a Python macro that revisits this bug.

With the PageName.oxt installed, open a new workbook.
Create at least one sheet, select any sheet.
It does not matter what the sheets are named.

In cells E2 and E3 enter the formulae:

    =PAGENAME(A1)
    =PAGEINDEX(E2)

It is easy to see the results are correct.
Now run the attached macro.

#NAME! will appear in cells E6 and E7.
This is the bad behaviour A that the original bug report complained about.
This uses the setFormula() method of .com.sun.star.table.XCell interface.
I believe this the correct way to enter a formula using the UNO API.
It seem work for built-in functions but not add-in functions.

Correct results appear in cells E10 and E11.
This works around the bug by using a replace descriptor to change '=' to '='.
The formula are 're-entered' and interpreted correctly.
Nice to see this workaround from the days before Windows™ still works.
 
Correct results appear in cells E14 and E15.
This time the 'dispatch' interface has been used to simulate user input.
Clumsy.  It is what you would get if you use 'macro record' and then
translate the !LibreOffice Basic in Python.  Yuk.

Correct results appear in cells E18 and E19.
This uses setFormula() but uses the PageName functions' full UNO Component IDs.
I don't think this should be necessary.  It does not work form the UI.

Can you run this and confirm the bug ?

Merci d'avance,
   Forester

Hi Forester,

With this last attachment, I see quite cleary the issue and the demonstration. Even with the wizard to validate the inserted 'bad' formulas in E6 and E7 works.
it's not easy to say what is lacking here. If I find something. Now I set status to NEW.

Regards,

Jacques Guilleron

Created attachment 79532 [details]
Office Basic Example shows issue entering formulae

Salut Jacques,

Many thanks for changing the bug status to NEW.

Attached is an Basic macro that tries to enter the formula
I see the same old #NAME! issue, which suggests the issue is not Python specific.

Created attachment 79533 [details]
Example showing issue with standard add-in functions

Salut,

My comment that went with the last attachment has got lost in the Ether.

This example uses two functions (Dec2Bin and BintoDec) that come with LibreOffice as standard but which fall into the add-in category.  I hope you don't have to translate the function names.

The script also shows the #NAME! issue.  I dont't think these functions are implemented in Python.

I saved the workbook with the two formula entered by hand and the two entered by script still showing #NAME! and had a peek at the contents:

unzip bug54854.ods
xmllint --format content.xml

Most interesting.

By for now.
   Forester

Problems persists in 4.0.6, 4.1.5 and 4.2.0.

Dear LibreOffice QA Team,

I can confirm that bad behaviour A occurs with LibreOffice 4.4.5 and 5.0.2.  I did not observe behaviour B but B has gone and returned before.

I managed to install LibreOffice 3.3.0 and observed both behaviour A and B so I have changed the version number to "inherited from OOo" as requested.  I do hope this does not mean you will declare the bug a feature and encourage me to abandon LibreOffice in favour the Apache version.

While I reported this as a problem using Python, I did as long ago as 2013 prove that it also occurs with Office Basic and 'standard' add-in functions.

Naturally I would update the status of this bug report if the bug magically fixed itself but, on account of even less progress on another bug, I still use LibreOffice 3.5.7 so I'm unlikely to notice.

Should I put this bug down on my list of things to do when I retired in 2025 ?

Happy hacking.

This may be related to bug 79588

Thanks for the suggestion and apologies for taking so long to look into it.

Is bug 79588 related ?

Using the classic (Aristotelian) model of inquiry we have:
  * both have cells showing #NAME!
  * both involve add-in functions
  * this bug is apparent when first entering an add-in function
    * bug 79588 is apparent only when the workbook is re-opened
  * this bug, once an add-in function has been entered correctly, #NAME! does
    not reappear no matter how many times the workbook is closed and reopened
  * this bug concerns add-ins written in Python
    * bug 79588 concerns add-in written in Basic

Let's try the pragmatic (Modern) model of inquiry and re-examine what we already
know about this bug.

1.  Not a load issue

See comments 13/14.

Here you are asked to install the add-in and enter two function calls at the
UI.  This is successful so I infer any loading of libraries has taken place.

Then you are asked to run a macro that shows the #NAME! problem.

2.  Not a Python or a Must-Be-The-User issue

Comment 15 refers to a LibreOffice Basic macro example that shows the problem.
The attachment is not quite as I intended but it is easy to see what was needed.

Comments 16/17 refer to a Python macro that uses add-in functions that came with
LibreOffice that shows the #NAME problem.  As a wild guess, these add-functions
are not implemented in Python.

This attachment is not at all what I intended.  No matter - the functions used
are add-in functions in !LibreOffice 3.5 but not in 5.0 so some productive
tester using 5.0 might have declared "works for me" and closed the bug.


I program Python and perhaps I know a little about how to debug code but when it
comes to LibreOffice, I am a user of an API.  My inquiries have gone as far as
they can without someone who can debug LibreOffice code providing some assistance.


If one looks at it this way ... comments 13/14:
  * enter =functionname(parameters) at the UI gives us what is wanted
  * use a macro to enter = unoComponentId.functionname(parameters) likewise
  * use a macro to enter =functionname(parameters) gives #NAME!
  * enter = unoComponentId.functionname(parameters) at UI gives #NAME! too

Notice the inconsistency ?

I disassembled a workbook and found that, internally, LibreOffice stores
= unoComponentId.functionname(parameters).

It seems LibreOffice can do the conversion but doesn't when using a macro to do
data entry.  It seems like the code exists but just isn't being called on this
particular execution path.  Hypothesis:  Frame-Controller-Model.

Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again.

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.

Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again for the session.

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.

some years later ... 

an attempt to boil down the issue: 

(with some bugs it's a problem that they are spotted in complex sheets / situations and problematic for devs to recheck ...) 

@QA Team: still an issue, 

simple-sample: 

------------ 
sub bug54854

Sheet        = ThisComponent.CurrentController.ActiveSheet
 
Cell         = Sheet.getCellByPosition(1, 2)
Cell.Formula = "=15"

Cell         = Sheet.getCellByPosition(1, 3)
Cell.Formula = "=DEC2BIN(B3)"

end sub 'bug54854
------------ 

results in #NAME? in cell B4 for me with ver. 7.1.0.0.a0 on winx64, formula in the cell is: "=dec2bin(B3)", lowercase!, 

entering that cell (F2 - edit mode), selecting complete content (ctrl-a), copying (ctrl-c), and pasting into another cell on the sheet results in formula "=DEC2BIN(B3)" in that cell and display of correct result "1111", 

works better with other functions like average, i don't know which functions are 'native'? and which are 'add-in'?, 

might be an issue with 'formula' as appearing in tdf#129464, a dup of tdf#82610,  and / or 'related' to plenty other bugs / requests about 'add-in' or 'user-defined' functions, 

happy hacking ...

This is not a bug. API access to add-in functions is described in our help [1]:

> The Add-in functions are supplied by the UNO com.sun.star.sheet.addin.Analysis
> service

which links to [2] "Calling Add-In Calc Functions in BASIC", ... which tells about the separate UNO service that implements the add-in functions.

However, it's not clear from those links that one should also use specific names when setting formulas to cells, not only when calling spreadsheet functions in code. So we need to improve the documentation, that one needs something like this:

> Cell.Formula = "=com.sun.star.sheet.addin.Analysis.getDec2Bin(B3)"

instead of

> Cell.Formula = "=DEC2BIN(B3)"

... which may be seen using the reverse call, after you set the required working formula in that cell manually:

> MsgBox Cell.getFormula

In addition, [3] also needs a reference to that information (currently only Analisys Functions help pages link to that).

[1] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060115.html
[2] https://help.libreoffice.org/7.0/en-US/text/sbasic/shared/calc_functions.html
[3] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060111.html

Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/4e6211e98f0f4000477ecee0783522603b3caad8

tdf#54854 Using Calc Functions in macros