Bug 78929 - Macros: Calc setFormula() does not appear to be working
Summary: Macros: Calc setFormula() does not appear to be working
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected) release
Hardware: Other All
: medium major
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Blocks: Macro
  Show dependency treegraph
Reported: 2014-05-19 23:43 UTC by Ewald Anderl
Modified: 2021-12-19 10:03 UTC (History)
8 users (show)

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

Document with test cases (code) to reproduce the problem, screen shots to clarigy the discussion, and further details regarding the behavior and the impact. (284.91 KB, application/vnd.oasis.opendocument.text)
2014-05-19 23:43 UTC, Ewald Anderl
Updatd with overview and single button to run remaining test cases (286.49 KB, application/vnd.oasis.opendocument.text)
2014-05-20 15:29 UTC, Ewald Anderl

Note You need to log in before you can comment on or make changes to this bug.
Description Ewald Anderl 2014-05-19 23:43:21 UTC
Created attachment 99362 [details]
Document with test cases (code) to reproduce the problem, screen shots to clarigy the discussion, and further details regarding the behavior and the impact.

Problem description:

The “setFormula()” method in Calc appears to be broken. It will work for very simple formulas (e.g. “= 1”) but displays an “Err:508” in the cell for even slightly more complex formulas ( e.g. “=and(c2,e2)” ). I have tested this in both Linux and Windows environments and the problem occurs in both.

Steps to reproduce:
  -- oCell.setFormula("= and(c2,e2)") will create an "Err:508" message
  -- see attached included document for additional information and examples.

Current behavior:
   -- Using the setFormula() method will set the formula but in MOST cases will leave the cell showing a "Err:508" message. This can then lead to other very bad behavior. See the attached document for a further description and screen shots.

Expected behavior:
Working setFormula().

Operating System: All
Version: release
Comment 1 Joel Madero 2014-05-20 02:14:24 UTC
Please provide much simpler explanation. While we appreciate the extensive documentation - we have hundreds and hundreds of bugs to go through and having to read 4 pages of explanation is just too time consuming. 

Please just write a simple set of instructions (on the bug report itself) and attach the simplest test case you possibly can. Also, is this a regression or a new problem? 

Marking as NEEDINFO - once you provide much simpler steps and a much simpler test case, mark the bug as UNCONFIRMED and we'll try to confirm. Thanks for understanding - we truly to appreciate the effort you put into your explanation, just our volunteer teams are stretched much to thin to dedicate that much time to just reading about a single bug.
Comment 2 Ewald Anderl 2014-05-20 15:21:49 UTC
Below is an updated bug description along the lines that you requested. I have also answered the questions that you had in your comment. Please let me know if this meets your needs. I do not know of a way to modify the original bug description so I have added it here:

Problem description:

The “setFormula()” method in Calc appears to be broken. It will work for very simple formulas (e.g. “= true”) but displays an “Err:508” in the cell for even slightly more complex formulas ( e.g. “=and(a1,b1)” ). The problem occurs in both Linux and Windows environments.

Steps to reproduce:

1. In LibreOffice, open Tools --> Macros --> Organize Macros --> pick any module to edit

2. Type (or cut/paste) the following program in to the empty “Sub” of your choice (Main will do)

Dim oDoc					'new Calc doc for this test
Dim oSheet					'points to first sheet
oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
oSheet = oDoc.getSheets().getByIndex(0)
oSheet.getCellByPosition(0, 0).setFormula("= true")		'Works
oSheet.getCellByPosition(1, 0).setFormula("= true")		'Works
oSheet.getCellByPosition(2, 0).setFormula("= and(a1,b1)")	'Err:508

3. Run this Macro

Current behavior:
	Cell C3 displays “Err:508”

Expected behavior:
	Cell C3 should display “TRUE”

Operating System: All
Version: release

Answers to questions:

I can not personally confirm whether this is regression or a new problem. I strongly believe that this is regression since I was following code examples in documentation that appear to have worked for some people at some point. I am new to LibreOffice starting with version

One additional note. I would suggest that this is far from a simple bug. It has far broader scope, is more extensive, and creates far more damage in code than the simple exmaple that I have provided above would indicate. Even more so the case before it was narrowed down and cornered as described in the document. I have updated the document to include the simple bug report / description at the front along with a button that will run through the rest of the test cases. I will upload this as well.

Sorry for the confusion that my original report may have caused, I am new to LibreOffice and its bug reporting process. My intent was to help the developers identify the complete problem quickly and provide help in narrowing down the source. Please let me know if you need additional information.
Comment 3 Ewald Anderl 2014-05-20 15:29:28 UTC
Created attachment 99423 [details]
Updatd with overview and single button to run remaining test cases
Comment 4 Kaya Uluer 2014-08-04 12:49:37 UTC
I confirm that I faced with the same problem in Libreoffice Build ID: 420m0(Build:2)

I try to set a formula with the following line;


than, the result is Err:508

But if I edit the formula with spreadsheet formula bar without any changes (just add a space character and delete it again and press enter) than, the formula is working good without any error messages
Comment 5 Jacques Guilleron 2014-08-04 15:25:39 UTC
Hello Ewald,

I change coma into formula with semicolon and it seems that works.
Build ID: 92216be6ce13990b8ea6b6264c656d2bc1746401
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-07-14_16:21:42
Windows 7 Home Premium.
Can you try it too?

Comment 6 Kaya Uluer 2014-08-04 17:01:22 UTC
I tried FormulaLocal property instead of SetFormula method and it is working now.
Comment 7 Buovjaga 2014-11-18 09:16:07 UTC
Set to NEEDINFO because of comment 5. Ewald: change to RESOLVED WORKSFORME, if it's ok for you.
Comment 8 LeMoyne Castle 2015-02-17 06:56:53 UTC
*** Bug 82610 has been marked as a duplicate of this bug. ***
Comment 9 Jacques Guilleron 2015-02-17 11:00:09 UTC
Set the Status to WORKSFORME.

Comment 10 LeMoyne Castle 2015-02-17 23:53:27 UTC
Setting back to NEW because SetFormula() and Formula property should work.  

expected: setFormula should work for the functions from the 'built-in' Analysis Add-In the same way the native calc functions work.  

actual: calc 'built-in' Analysis Add-In functions placed on sheet in Basic with oCall.setFormula(<str>) require modification in calc ui in order to compile and work correctly.  So far, setFormula has failed for *every* Analysis AddIn function I have tried (see dup Bug#82610).

Additionally, localization or special syntax may be required to get some calc functions to work in setFormula (even native funcs? -- h/t Jacques Guilleron).


It is tempting to go with resolved+workaround because oCell.FormulaLocal = "dec2hex(A1)" does work.  However, I'm not sure FormulaLocal is in the published API (maybe I just didn't find it).  Besides, setFormula(<fstr>) and Formula = <fstr> should simply work without the user needing to edit the cell in calc. 

Additionally, want to make sure can access other functions that should(?) be available in calc: e.g. Basic library.module.functions that are both placed on sheet and called from sheet through the scripting interface. 


Thanks Kaya for the workaround (use FormulaLocal) and thanks to Ewald for the multi-page initial report, and again, a tip of the hat to Jacques Guilleron.
Comment 11 tommy27 2016-04-16 07:23:04 UTC Comment hidden (obsolete)
Comment 12 QA Administrators 2017-05-22 13:22:43 UTC Comment hidden (obsolete)
Comment 13 Gülşah Köse 2018-05-23 18:55:14 UTC
Reproducable in LibreOfficeDev
Comment 14 Gülşah Köse 2018-05-23 19:26:32 UTC
There is a solution here for this. But still has buggy behavior. Although my seperator is , in my settings ; seperator works for me.

Comment 15 b. 2020-01-13 12:24:40 UTC
i think this can be closed as wfm, (or notabug?)

when changing the ',' in the formulas to ';' - what is needed for my german locale - all tests hold, 

if have to obey my locale settings as well when entering formulas manually, i'm in doubt if it's possible to write an 'universal formula interpreter' guessing correct function of separators without knowledge or respecting settings done somewhere, 

#82610 fails with me, and there may be many more bugs, this one is taking time from testers while they check the original complaint, find 'ah, locale settings', and then are led to other failures, 

other failures should have their own bugs, 

if anybody with ',' as formula separator has problems with 

oCell.setFormula("= and(c2,e2)")

feel free to reopen, 



ver. tested: 

Version: (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
Comment 16 Mike Kaganski 2021-12-19 10:00:29 UTC
(In reply to Ewald Anderl from comment #0)
> The “setFormula()” method in Calc appears to be broken.
> It ... displays an “Err:508” in the cell for
> even slightly more complex formulas ( e.g. “=and(c2,e2)” ).

(In reply to Kaya Uluer from comment #4)
> I confirm that ...
> oCell.SetFormula("=HYPERLINK(sheet1.A1,""Sheet1"")")

This is not a bug. All formulas that fail in the examples are invalid for setFormula, which only takes canonical formula representation - among other things, usung semicolon as argument separator. The formulas here use comma to separate arguments, likely using "wysiwyg" approach in en-US locale (which *displays* formulas using commas).

There is a FormulaLocal property
Comment 17 Mike Kaganski 2021-12-19 10:03:22 UTC
(In reply to Mike Kaganski from comment #16)
> There is a FormulaLocal property

... that is intended for cases when formulas must be set or obtained using current locale conventions - e.g., from user input. In all other cases, programmers should use setFormula with canonical syntax, to not depend on user settings.