Bug 101316 - Macro Creates Formulas with Error 508
Summary: Macro Creates Formulas with Error 508
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: lowest minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro
  Show dependency treegraph
 
Reported: 2016-08-05 03:33 UTC by ghborrmann
Modified: 2021-05-20 14:14 UTC (History)
4 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description ghborrmann 2016-08-05 03:33:26 UTC
Setting cell B2's formula (by means of a macro) to
    =IF(A2="",B1,B2)
results in the display in cell B2
    Error 508
Clicking on cell B2 shows the formula in the edit box which looks OK.  If I then edit the formula by deleting the close parenthesis and pressing enter, the display in cell B2 is as expected (no error).  Display of the formula in the edit box is identical to the first display (the close parenthesis is there).

Detailed Steps to Reproduce:
    1. In new empty LO Spreadsheet document,
use the menu Tools>Macros>Organize Macros>LibreOffice Basic... and click on "Untitled 1", then click on the "New" button and then the "OK" button to put you in the macro editor, editing Module 1 of the Standard library of "Untitled 1".
    2.  Enter the following code in Sub Main:

        Dim oSheet as Object, oCell as Object
        oSheet = ThisComponent.Sheets(0)
        oCell = oSheet.getCellByPosition(1,1)
        oCell.Formula = "=IF(A2="""",B1,A2)"

    3.  Execute the code by pressing F5
    4.  The cell B2 now displays "Error 508".
    5.  Edit cell B2 to delete the last parenthesis.  The error disappears, and the parenthesis is still there when you click on cell B2.

Versions:
The version I am now running is Fedora's 1:5.1.5.2-1.fc24.  However, I first encountered it when running version 4.6 if I remember correctly.  When updating to version 5 did not fix the problem, I decided to submit this bug report.
Comment 1 Joel Madero 2016-08-07 16:02:18 UTC
LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

Also tested on:
Version: 5.3.0.0.alpha0+
Build ID: fc305bb6d656736bedc2f89789e18d8c9a3bbf2c
CPU Threads: 2; OS Version: Linux 3.16; UI Render: default; 
Locale: en-US (en_US.UTF-8); Calc: group

Same behavior on both.

Confirming:
New
Minor - can slow down but will not prevent high quality work;
Lowest - macros are not used by the vast majority of users, case in point....this issue has existed for years and no one even reported it.
Comment 2 QA Administrators 2017-09-01 11:20:42 UTC Comment hidden (obsolete)
Comment 3 ghborrmann 2017-09-03 19:06:14 UTC
Version 5.4.1.2 on Windows 7: behavior is unchanged.
Comment 4 QA Administrators 2018-09-04 02:55:21 UTC Comment hidden (obsolete)
Comment 5 ghborrmann 2018-09-04 20:39:49 UTC
Tested version 6.1.0.3 on Windows 7.  Still shows same behavior.
Comment 6 Andreas Heinisch 2020-05-05 17:57:10 UTC
Using oCell.Formula = "=IF(A2="""";B1;A2)" instead of oCell.Formula = "=IF(A2="""",B1,A2)" I get no error.

Does the interpretation of the separator lie in your locale settings? Can the comma be used as separator?
Comment 7 Mike Kaganski 2021-05-19 11:09:34 UTC
oCell.Formula takes *standard* (language-independent) syntax of formulas (using ';' for function argument separators, among other things), so "=IF(A2="",B1,B2)" is of course an invalid input resulting in error from parsing the formula text. This is not a bug.
Comment 8 ghborrmann 2021-05-19 14:05:51 UTC
(In reply to Mike Kaganski from comment #7)
> oCell.Formula takes *standard* (language-independent) syntax of formulas
> (using ';' for function argument separators, among other things), so
> "=IF(A2="",B1,B2)" is of course an invalid input resulting in error from
> parsing the formula text. This is not a bug.

Where does is state that using ';' for function argument separators is standard?  LO itself acknowledges that the comma is standard, in two ways:
1.  When entering a formula from the keyboard, LO shows a template of the function.  This template uses commas, not semicolons.
2.  When a formula is created by a macro, using semicolons results in no error but clicking on the cell shows that the formula now in the cell uses commas.

My point: this bug, while trivial for experienced LO macro writers, can give new users a bad impression of LO's overall quality.
Comment 9 Mike Kaganski 2021-05-19 14:34:02 UTC
(In reply to ghborrmann from comment #8)

Calc displays formulas to users using whatever modification options set in the program - taking into account the localized defaults for locales (like commas as argument separators in en-US), and user overrides in Options->Calc->Formula. It would use R1C1 notation, or Excel A1 notation, if user sets so. And that's all just a *display* thing; internally, there's only one "API" syntax that is accepted by oCell.Formula - namely, those defined in ODF standard (e.g., for function parameters, see its OpenFormula sect. 5.6 [1]). In other words, answering to

> Where does is state that using ';' for function argument separators is standard?

the answer is: in ODF international standard. Using locale-independent strings when programming is essential for any programmer, to avoid gotchas when your program stops working on your neighbor just because they changed their display preferences.

[1] http://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#FunctionsFunctionsParameters
Comment 10 ghborrmann 2021-05-20 14:01:44 UTC
(In reply to Mike Kaganski from comment #9)
> 
> the answer is: in ODF international standard. Using locale-independent
> strings when programming is essential for any programmer, to avoid gotchas
> when your program stops working on your neighbor just because they changed
> their display preferences.
>
I see your point.  Nevertheless, the way LO treats the comma-separated arguments leads me to believe that the original programmers intended to accept commas as separators.  In any event, the question is moot: the current maintainers apparently consider this issue too trivial to warrant any change.
Comment 11 Mike Kaganski 2021-05-20 14:14:14 UTC
(In reply to ghborrmann from comment #10)
> I see your point.  Nevertheless, the way LO treats the comma-separated
> arguments leads me to believe that the original programmers intended to
> accept commas as separators.

Try changing your locale to e.g. ru-RU, and you would get a different belief: that "original programmers" intended to only accept semicolons as argument separators, commas as decimal separators, and that there's no "VLOOKUP" function, but instead "ВПР". What you see is *not* an indication of any "programmers' intention", but a result of localization effort, allowing people to use familiar UI, similar to that of related localized Excel. It is unrelated to API, where programmer uses techniques that are as much as possible generic.