Bug 54854 - calc add-in functions are not evaluated as expected
Summary: calc add-in functions are not evaluated as expected
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Whiteboard: target:7.3.0
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2012-09-13 10:30 UTC by kiaora-paora
Modified: 2021-11-06 09:59 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Calc add-in component. Issues first seen using this. (3.28 KB, application/vnd.openofficeorg.extension)
2012-09-13 10:30 UTC, kiaora-paora
Python script to be run as a macro to illustrate the issue decrined in the previous comment. (845 bytes, text/x-python)
2013-05-18 13:02 UTC, kiaora-paora
Python macro illustrating the issue and several workarounds (2.31 KB, text/x-python)
2013-05-18 20:14 UTC, kiaora-paora
Office Basic Example shows issue entering formulae (770 bytes, text/plain)
2013-05-19 17:12 UTC, kiaora-paora
Example showing issue with standard add-in functions (550 bytes, text/plain)
2013-05-19 17:20 UTC, kiaora-paora

Note You need to log in before you can comment on or make changes to this bug.
Description kiaora-paora 2012-09-13 10:30:52 UTC
Created attachment 67091 [details]
Calc add-in component.  Issues first seen using this.

Problem description:

calc add-in functions are not evaluated as expected

A) UI shows #NAME? instead of the add-in function result when a simple formula
that calls the function is entered using setFormula() via the pyuno bridge.

The same formula is evaluated as expected when entered by hand via the UI.

The #NAME? condition may be cleared at the UI by selecting the cell and, at the
keyboard, typing F2 SPC RET (effectively re-entering the formula by hand).

It may also be cleared by using Find & Replace to substitute the leading the
leading "=" with "=".  The 'workaround' is also effective from a Python script
using the pyuno bridge.

These symptoms affect all add-in functions I have implemented in Python.

B) UI shows #VALUE! instead of the add-in function result when the calc
workbook is closed and then re-opened.

The #VALUE! condition may be cleared as for the #NAME? but it may also be
cleared by selecting the cell(s) and typing DEL ^Z.

Oddly these symptoms do not show with all the add-in functions I have
implemented in Python but I suspect the issue is not with the function itself.

Steps to reproduce:

1.  Install the attached add-in component.

Please review the add-in.  It seems simple enough but it is the result of
triangulation of scant information from obscure sources of dubious currency.

2.  Open LibreOffice with a UNO bridge and a new calc document.  E.g.

  soffice --calc --accept="socket,host=localhost,port=2012;urp";

From the UI, enter in a cell the formula:


The UI should show the formula evaluates to "Sheet1".

In another cell enter:


The UI should show the formula evaluates to "Sheet1".

3.  From a shell console start Python as bundled with LibreOffice.  E.g.


and copy/paste the following one line at a time into the shell:

  import uno
  # connect with running LibreOffice
  lcxt = uno.getComponentContext()
  resolver = lcxt.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", lcxt)
  unoUrp = 'socket,host={0},port={1};urp'.format("localhost", 2012)
  rctx = resolver.resolve("uno:{0};StarOffice.ComponentContext".format(unoUrp))
  # enter formula into a cell of the open workbook
  desktop = rctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop",rctx)
  cell = desktop.getCurrentComponent().Sheets.getByName('Sheet1').getCellRangeByName("E5")

There should be no errors.  Beware line wrap: each assignment is one line.

The cell E5 on Sheet1 will now show #NAME? when one would expect it to show Sheet1.

Clear the #NAME? using one of the methods describe in A) of the Problem Description above.
Save, close and reopen the workbook.

The cell E5 on Sheet1 will now show #VALUE! when one would expect it to show Sheet1.

Clear the #VALUE! using one of the methods describe in A) of the Problem Description above.

Current behaviour (3.6.2):

See Problem Description above.  I will add more comments if and when I
understand the circumstances better.

Expected behaviour:

Formulae involving add-in functions should always evaluate to the correct
result regardless of:
  a) how the formula is entered (manually or by macro)
  b) regardless of circumstances in which the containing document is opened
  c) the language of implementation (here Python)

Platform (if different from the browser): 

  Debian GNU/Linux 6.0.5 (aka Squeeze, aka Stable)

Installation media:

Comment 1 kiaora-paora 2012-11-08 22:11:58 UTC
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")

g_exportedScripts = bug54854,
Comment 2 kiaora-paora 2013-03-20 19:09:48 UTC Comment hidden (obsolete)
Comment 3 kiaora-paora 2013-05-13 19:44:35 UTC
This issue IS still present in 4.0.3. Both unwanted behaviours A and B.
Comment 4 Jacques Guilleron 2013-05-13 21:03:18 UTC
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
Comment 5 Jacques Guilleron 2013-05-16 03:15:17 UTC Comment hidden (obsolete)
Comment 6 Jacques Guilleron 2013-05-16 16:46:58 UTC
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,


Jacques µGuilleron
Comment 7 Jacques Guilleron 2013-05-16 17:10:42 UTC
Sorry, please reverse the names'fuctions for correct reading.

Jacques Guilleron
Comment 8 Jacques Guilleron 2013-05-16 23:42:19 UTC
Hi Forester,

New try with LO 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
Comment 9 Jacques Guilleron 2013-05-17 21:52:24 UTC
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.

Jacques Guilleron
Comment 10 kiaora-paora 2013-05-18 12:38:32 UTC
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,
Comment 11 kiaora-paora 2013-05-18 13:00:45 UTC
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:
but what I see entered in the spreadsheet is:

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.

Comment 12 kiaora-paora 2013-05-18 13:02:21 UTC
Created attachment 79498 [details]
Python script to be run as a macro to illustrate the issue decrined in the previous comment.
Comment 13 kiaora-paora 2013-05-18 20:14:15 UTC
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:


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,
Comment 14 Jacques Guilleron 2013-05-19 12:24:24 UTC
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.


Jacques Guilleron
Comment 15 kiaora-paora 2013-05-19 17:12:51 UTC
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.
Comment 16 kiaora-paora 2013-05-19 17:20:43 UTC
Created attachment 79533 [details]
Example showing issue with standard add-in functions
Comment 17 kiaora-paora 2013-05-19 17:26:10 UTC

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.
Comment 18 kiaora-paora 2014-02-24 21:41:19 UTC
Problems persists in 4.0.6, 4.1.5 and 4.2.0.
Comment 19 QA Administrators 2015-06-08 14:41:56 UTC Comment hidden (obsolete)
Comment 20 kiaora-paora 2015-10-21 11:55:25 UTC
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.
Comment 21 Olivier Hallot 2015-12-14 17:21:46 UTC
This may be related to bug 79588
Comment 22 kiaora-paora 2016-02-13 09:07:41 UTC
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.
Comment 23 QA Administrators 2017-03-06 15:04:26 UTC Comment hidden (obsolete)
Comment 24 fcyrilf 2017-05-16 09:47:15 UTC
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.
Comment 25 fcyrilf 2017-05-16 09:49:37 UTC
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.
Comment 26 QA Administrators 2018-07-23 02:32:25 UTC Comment hidden (obsolete)
Comment 27 QA Administrators 2020-07-23 03:55:45 UTC Comment hidden (obsolete)
Comment 28 b. 2020-09-02 11:48:00 UTC
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, 


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. 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 ...
Comment 29 Mike Kaganski 2020-10-12 06:52:11 UTC
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
Comment 30 Commit Notification 2021-09-28 00:17:14 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":


tdf#54854 Using Calc Functions in macros