[Sorry if Extensions component is wrong, wasn't sure what to pick] Running LO 6.2.0.3 on MacOS 10.13.6 in South Africa, where system regional setting uses comma rather than period for decimal separator. Writer treats strings with period, eg "1.5" as non-numeric and evaluates that as 0. With a comma instead, eg "1,5" the value is one-and-a half. OK, fair enough. Now I have a macro that extracts a value from a spreadsheet cell, and sets that value into a Writer variable field which is then used elsewhere in a Writer formula. The latter formula does not compute correctly - result is 0 - because the extracted spreadsheet value (a floating point number, type Variant/Double returned by RangeTLCellValue) gets turned into a string with a period for decimal point in the Writer variable field (DependentTextField.Content = myNumericValue) Either the implicit double-to-string conversion should be using the same punctuation that's expected elsewhere (so comma not period) or else everything should accept a period as decimal separator in number strings, and then do the local-dependent presentation only at the formatting stage (which is what OpenOffice used to do: this macro began life there). FWIW I tried changing Language settings in the document, made no difference. Presumably the system locale trumps that for number parsing. This also affected static "set variable" fields in the template: I had to change decimal periods to commas in those too, to make the formulas work.
Thank you for reporting the bug. it seems you're using an old version of LibreOffice. Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Thanks for the response. I just downloaded and installed LO 6.4.2, and the bug is still present, as described. I will try to compose a minimal demonstration, but for now let me describe what I am doing, and what is happening: Note that in my system regional settings (ZA) the decimal point character is a comma, not a period. This is central to the problem, I believe. (1) I have a Writer template with 3 "Set variable" fields... Field 1 ("Rate") value is 1 cent (0.01) actually entered as 0,01 Field 2 ("Count") value is initially 0 Field 3 ("Total") value is Count*Rate and so initially displays as 0 Note that I have to enter the fractional 1 cent value with a comma, not a period as the decimal. If I type the value as 0.01 then formulas based on the field do not work (the value displayed for the formula field does not change) (2) The macro opens a spreadsheet and reads a cell value like so: tCount = RangeTLCellValue(oSpreadsheet, "TotalCount") from a named range spanning one cell. The value is a calculation result that is not necessarily an integer. In the debugger watch window after reading it, tCount is displayed as Type Variant/Double, Value 1234.5 (Note the period in there, not a comma) (3) The macro then sets tCount into the Writer document "Count" field with the equivalent of this line: TextFieldMaster(sName).DependentTextFields(iIndex).Content = tCount (it actually loops through all the dependent text fields, I can't remember why) (4) Finally the macro does this: ThisComponent.GetTextFields.Refresh Now I would expect the Total field in the document to display "12.34" (or "12,34") (i.e. the result of calculating 1234.5 * 0.01) but it still shows 0. If I inspect the document Count field, its value is 1234.5 exactly as it came from the spreadsheet. Now if I change the Count field value to 1234,5 (with a decimal comma) then the Total field changes to the expected calculation result value 12,34 Phew. To summarise: * Writer seems to insist that numeric field values use the decimal character defined in the system regional settings, in my case a comma. If there is a period instead of a comma as the decimal, the value is treated as non-numeric. That's arguably justifiable, BUT... * A non-integer value extracted from a spreadsheet is rendered using a period as the decimal, not a comma, when it is stored in a Writer field. Thus Writer treats it as non-numeric is it is used in a formula. So these two behaviours conflict with each other. The older behaviour of Open Office (where my macro was initially developed) was to always use a decimal period in field values, but to _show_ values in the document using the regional characters. This is what I would recommend. Hope that all makes sense, and thanks for reading this far :-)
Could you provide a minimal working document as an attachement? I recently fixed a bug where this behaviour could be fixed ...
Created attachment 156251 [details] Minimal demonstration of decimal point bug Hi Andreas. The attached .OTT file demonstrates the issue. It includes an explanation as well as screenshots since YMMV, as I suspect the behaviour depends on the system locale. Mine is South Africa, where the decimal separator is a comma, not a period. For me this problem manifests in 2 ways: (a) with constant fields in the template itself, as demonstrated here and (b) with values pulled from a spreadsheet and inserted into the document by a macro. The second case is hard to demo in a single file, but I'd say it boils down to the same root cause: the number will be inserted into the document with a period (not a comma) as the separator, and consequently calculations that depend on the value will fail.
I think the bug lies in the handling of numeric types in the macro libraries. I recently fixed a bug, where Macros did not use any locale: https://bugs.documentfoundation.org/show_bug.cgi?id=97983#c10 Could you test your macro using a daily build of LO?
(In reply to Andreas Heinisch from comment #5) > I think the bug lies in the handling of numeric types in the macro > libraries. I recently fixed a bug, where Macros did not use any locale: > https://bugs.documentfoundation.org/show_bug.cgi?id=97983#c10 > > Could you test your macro using a daily build of LO? jsd: you will find daily builds here: https://dev-builds.libreoffice.org/daily/master/current.html https://wiki.documentfoundation.org/Installing_in_parallel/OS_X
(In reply to Buovjaga from comment #6) > > Could you test your macro using a daily build of LO? > > jsd: you will find daily builds here: > https://dev-builds.libreoffice.org/daily/master/current.html Sorry for delayed response. I'm afraid I see no change in behaviour, using this build https://git.libreoffice.org/core/+log/4118610df8a78b00e5369bad1d9ce21cf1fbafeb (downloaded today, 12 May) I'll attach a screenshot of my regional settings, where you can see (if you look very closely :-) that the decimal separator is a period, not a comma. Using the demo file I attached here on 2019-12-02, I get the same result, which is that calculations fail for numbers with a period as the decimal separator. Something that just occurred to me is this: for my locale, ZA, the _default_ decimal separator is a comma. I changed that to a period (and changed the thousands grouping character from period to comma). If LO is using the default locale settings instead of the getting the actual ones from the OS, this would explain my results.
Created attachment 160728 [details] My macOS locale settings Current locale settings, mentioned in my previous post. Decimal separator is a period, which is not the default (comma)
(In reply to jsd.libreoffice from comment #7) > Something that just occurred to me is this: for my locale, ZA, the _default_ > decimal separator is a comma. I changed that to a period (and changed the > thousands grouping character from period to comma). > > If LO is using the default locale settings instead of the getting the actual > ones from the OS, this would explain my results. So bug 73242 ?
(In reply to Buovjaga from comment #9) > > If LO is using the default locale settings instead of the getting the actual > > ones from the OS, this would explain my results. > > So bug 73242 ? Quite possibly, yes. If I understand that discussion correctly, the gist is that LO isn't picking up the OS locale settings. If it were to do so, I agree that would probably resolve this issue. But here's an interesting thing: in the LO Preferences under Language Settings/Languages, the settings I have are: Language Of: User interface: Default - English (USA) Formats: Locale setting: Default - English (UK) Decimal separator key: (ticked) Same as local setting (.) What's interesting to me is that AFAIK, the decimal separator for both English (USA) and English (UK) is a period, not a comma. Yet LO is nevertheless expecting a comma, not a period, in numbers. Not sure why that would be. I considered the possibility that it might be using the _document's_ language setting, which was originally English (South Africa). But changing that to English (USA) did not make a difference. Calculations involving numbers with a decimal comma worked, those involving numbers with a decimal period did not. Curiouser and curiouser.
Let's close as duplicate of bug 73242. *** This bug has been marked as a duplicate of bug 73242 ***