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.
Created attachment 141986 [details] Screenshot of the sheet after running the macro
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 (;)
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;""##"")"
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".
(In reply to Dmitry from comment #4) > You may agree that locale dependency adds a lot confusion... ... which is the reason why API *must use locale-INDEPENDENT syntax*. > 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. ... and if user enters "=SUM(1,2)" it somehow knows that the "," here is meant to be argument separator and not decimal separator? Can you come with a fool-proof way of guessing everything? A programmer MUST USE CORRECT SYNTAX. Period. > 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 that will be your bug, yes. > And second, the error could be more specific, for example, err:504 > "Parameter list error". Well - yes.
Created attachment 142040 [details] Test spreadsheet with a macro
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.