Bug 117511 - Setting the cell formula from a macro results Err:508 cell value
Summary: Setting the cell formula from a macro results Err:508 cell value
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-05-08 19:19 UTC by Dmitry
Modified: 2018-05-11 22:14 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
Test spreadsheet with a macro (9.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-08 19:19 UTC, Dmitry
Details
Screenshot of the sheet after running the macro (8.50 KB, image/png)
2018-05-08 19:20 UTC, Dmitry
Details
Test spreadsheet with a macro (9.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-11 22:08 UTC, Dmitry
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dmitry 2018-05-08 19:19:54 UTC
Created attachment 141985 [details]
Test spreadsheet with a macro

Steps to reproduce:

1. Create a macro that sets the cell formula which in return contains quotes:

ThisComponent.Sheets.getByIndex(0).getCellByPosition(0, 0).Formula = "=TEXT(A2,""##"")"

2. Run this macro

Expected:

Cell A1 contains formula '=TEXT(A2,"##")' and displays value of cell A2 ("12").

Actual:

The error Err:508 is displayed signalling that either braces or quotes are not balanced.

Note:

If after running a macro one edits the cell A1 and makes insignificant edit (e.g. adds a space), the whole formula starts to work correctly.
Comment 1 Dmitry 2018-05-08 19:20:48 UTC
Created attachment 141986 [details]
Screenshot of the sheet after running the macro
Comment 2 Mike Kaganski 2018-05-08 19:54:34 UTC
Works for me with Version: 6.0.4.2 (x64)
Build ID: 9b0d9b32d5dcda91d2f1a96dc04c645c450872bf
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL

... after I replace comma (,) in the code with semicolon (;)
Comment 3 Eike Rathke 2018-05-08 19:56:39 UTC
In the API you have to use the non-locale-dependent and not-user-defined ; semicolon parameter separator in spreadsheet functions. This works:

 ....Formula = "=TEXT(A2;""##"")"
Comment 4 Dmitry 2018-05-09 08:51:48 UTC
Thanks! Indeed it works fine with ";". You may agree that locale dependency adds a lot confusion... For example it could work in the manner that if user enters '=TEXT(A2,"##")' the actual formula written to cell is '=TEXT(A2;"##")' i.e. it is "fixed" on the fly. With current implementation I can put two visually identical formulas in two neighbouring cells, and one of them works fine while another results err:508.

And second, the error could be more specific, for example, err:504 "Parameter list error".
Comment 5 Mike Kaganski 2018-05-09 09:44:03 UTC Comment hidden (off-topic)
Comment 6 Dmitry 2018-05-11 22:08:51 UTC
Created attachment 142040 [details]
Test spreadsheet with a macro
Comment 7 Dmitry 2018-05-11 22:14:40 UTC
I fully agree with the statement that macros should use the correct syntax. However the programmer should have the ability to reason the error. I have attached another version of test.ods, this time with the "same" formula in cell B1. Now if one scrolls from A1 to B1, visually the same formula is displayed, however A1 results an error. This is not logical.