Created attachment 200377 [details]
gif showing step-by-step division bug on BASIC

(In reply to Mike Kaganski from comment #2)
> Resolved in bug 166231.

Bug is similar on the mathematical operation but different overall. It's linked here to decimal & thousand separators.

Created attachment 200440 [details]
gif showing step-by-step division & decimal separator bug on BASIC

This GIF shows better the nature of the issue here: either there is a real bug in the dialogbox & textfield settings or I have missed something when configuring my textfield.

In my example illustrated here, putting aside the "."/"," separator, I get different results when using Clng & CDec to convert the text value of my textfield into a decimal value:

Inserted value: 2.02

Clng(PrixProduit_string) = 2
CDec(PrixProduit_string) = 0

Expected result: 2.02

//---Additional information about the code lines used in my macro-----//

'--The "PrixProduit_string" variable was declared as followed:

Dim PrixProduit_string As String
PrixProduit_string = ActivationInterface.getControl("PrixProduit").Text

'--PrixProduit is the name of the textfield in my dialogbox.

//----------------------------------------------------//

Either way, the rules related to the use of the decimal separator make it really complicated especially if the "." does not work in a textfield, as shown here.

Thank you for reporting the bug. I can not reproduce the bug in

Version: 25.2.3.2 (X86_64) / LibreOffice Community
Build ID: bbb074479178df812d175f709636b368952c2ce3
CPU threads: 12; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

MySheet.getCellByPosition(1, 1).Value = Clng(FieldA_string) / CLng(FieldB_string)

Be careful: CLng is a long Integer without decimal places.
Did you really use the function in this way?
Use CDbl instead. Will work with local decimal separator.

Created attachment 200975 [details]
[Update May 27] gif showing step-by-step division & decimal separator bug on BASIC

Updated GIF showcasing a test using CDbl instead of CDec, recommended by Robert Großkopf on May 15th 2025.

The test results with an error, which translated in English, means "Incorrect value or data type. Inconsistent data type."

(In reply to Robert Großkopf from comment #6)
> MySheet.getCellByPosition(1, 1).Value = Clng(FieldA_string) /
> CLng(FieldB_string)
> 
> Be careful: CLng is a long Integer without decimal places.
> Did you really use the function in this way?
> Use CDbl instead. Will work with local decimal separator.

Sorry Robert, I meant to write the comment in the GIF description but I'm quite new at using this forum.

Basically, as you can see on the updated media, I have tried your recommendation by changing CDec to CDbl (that was the only change made) and it prompts an error saying "Incorrect value or data type. Inconsistent data type.

Any idea why? Did I have to add something in my script in order to make the CDbl-conversion work properly?

Some notes.

1. All the functions like CDbl implement *locale-specific* conversion. It is documented in the earliest documentation available - see "Implicit and Explicit Type Conversions" in [1]. No it is wrong that "you should just keep one of them for decimal values and that's it".

2. CDec is only implemented on Windows [2]. It is not important for your case (on Windows), but is useful to remember (just avoid it - it's not portable).

Now I must say, that the provided materials (the screencasts) are useless. Please provide a *simple* code like

  Sub X
    s = "1.2"
    MsgBox CDbl(s)
  End Sub

or similar, that shows which specific sample values the failing code has on input, and also specify the error that you see in result, and your expectation. If your testing environment is different from the Windows/en-US (fr_FR) shown in version info of comment 0, please also specify that.

Thank you.

[1] https://docs.oracle.com/cd/E19064-01/so7/817-1826/817-1826.pdf#page=53
[2] https://opengrok.libreoffice.org/xref/core/basic/source/runtime/methods1.cxx?r=b710ba48503372ddaf10a17ce7f4f3340bc8adb8&mo=7290&fi=258#258

(In reply to Mike Kaganski from comment #9)

Friend, your first comment was made obsolete for good reasons, I was not expecting another helpless comment like this one from you.

Obviously I searched the internet and the Document Foundation's resources for an answer and even posted on forums. If, despite my best efforts to find an error in my work (which is sourced from the available theory), I still get an error, then considering a bug in the LibreOffice app would not be inappropriate.

But I shall remain polite and answer your comment without repeating things already written which would make the whole thing confusing.

1- comment #0 provides a complete description of the bug and includes my detailed arguments about why simplifying the use of punctuation in LibreOffice would be welcomed.

2- comment #4 details how the bug occurs, alongside the GIF that adds a visual aid to better understand the issue at hand.

3- Testing is an art and should follow strict guidelines in order to understand the issue at hand. If I changed environment between these tests then I would have added that information. So yes, I'm still using 24.8.6.2 for now.

4- Here is a simplified version of the script as requested:

'//----------------------------------------------------//
Sub Macro
	'--DECLARING VARIABLES
	Dim MyDoc As Object
	Dim MySheet As Object
	Dim FieldA_string As String
    	Dim FieldB_string As String
	MyDoc = ThisComponent
	MySheet = MyDoc.Sheets.getByName("SHEET")

	'--RETRIEVING DATA FROM DIALOGBOX. "FieldA" & "FieldB" ARE THE NAMES OF TWO TEXTFIELDS IN MY DIALOGBOX.
	FieldA_string = ActivationInterface.getControl("FieldA").Text
    	FieldB_string = ActivationInterface.getControl("FieldB").Text

	'--CONVERSION STEP. CONVERTING STRING VALUE FROM DIALOGBOX INTO DECIMAL VALUE ONTO SHEET, COMPATIBLE WITH DECIMAL SEPARATOR.
	MySheet.getCellByPosition(1, 1).Value = CDec(FieldA_string) / CDec(FieldB_string)

End Sub
'//----------------------------------------------------//

(In reply to Cameron from comment #10)
> Friend, your first comment was made obsolete for good reasons

Please note that it was myself who marked it as such; you may check history. Please note also, that there is no need to mark your comments with version tags (this makes our use of tags difficult).

> I was not expecting another helpless comment like this one from you.

OK. Possibly you would also will not accept me fixing the bug? Please say so, and I will stop trying to move your bug forward.

> Obviously I searched the internet and the Document Foundation's resources
> for an answer and even posted on forums. If, despite my best efforts to find
> an error in my work (which is sourced from the available theory), I still
> get an error, then considering a bug in the LibreOffice app would not be
> inappropriate.

Who claimed that "considering a bug in the LibreOffice app is inappropriate"?

> ...
> 
> 4- Here is a simplified version of the script as requested:
> 
> '//----------------------------------------------------//
> Sub Macro
> 	'--DECLARING VARIABLES
> 	Dim MyDoc As Object
> 	Dim MySheet As Object
> 	Dim FieldA_string As String
>     	Dim FieldB_string As String
> 	MyDoc = ThisComponent
> 	MySheet = MyDoc.Sheets.getByName("SHEET")
> 
> 	'--RETRIEVING DATA FROM DIALOGBOX. "FieldA" & "FieldB" ARE THE NAMES OF TWO
> TEXTFIELDS IN MY DIALOGBOX.
> 	FieldA_string = ActivationInterface.getControl("FieldA").Text
>     	FieldB_string = ActivationInterface.getControl("FieldB").Text
> 
> 	'--CONVERSION STEP. CONVERTING STRING VALUE FROM DIALOGBOX INTO DECIMAL
> VALUE ONTO SHEET, COMPATIBLE WITH DECIMAL SEPARATOR.
> 	MySheet.getCellByPosition(1, 1).Value = CDec(FieldA_string) /
> CDec(FieldB_string)
> 
> End Sub
> '//----------------------------------------------------//

Please. This macro relies on some specific spreadsheet having sheet named "SHEET"; it uses something called "ActivationInterface". Could you please see if your code works simply starting LO in safe mode; from Start Center open Tools->Macros->Edit Macros; paste your code there; and running it - without anything opened first.

And indeed, please ignore me, if you are uninterested in me actually reproducing and maybe fixing this.

Thanks.

(In reply to Mike Kaganski from comment #11)

This is a corrected version of comment #10 which includes and explains the "ActivationInterface" variable:

'//----------------------------------------------------//
Sub DisplayingDialogbox
	'--THIS SUB IS NECESSARY TO PROMPT THE DIALOGBOX AND MAKE THE REST OF THE CODE WORK.
	
	'--THIS SECTION SOURCES LIBREOFFICE IN-HOUSE SCRIPTS (LIBRARIES) TO MAKE THE CODE WORK. THE LIBRARIES USED ARE GROUPED INTO THE VARIABLE "DialogboxLibrariesVariable".

	DialogLibraries.Loadlibrary("Standard")
   
	Dim DialogboxLibrariesVariable As Object
	DialogboxLibrariesVariable = DialogLibraries.GetByName("Standard")
	
	'--CREATING AND DECLARING THE DIALOGBOX INTO THE CODE (THE DIALOGBOX ITSELF HAS ALREADY BEEN CREATED, IS NAMED "MYDIALOGBOXDISPLAY" HERE AND CONTAINS THE TEXTBOXES USED IN THE NEXT SUB).
	Dim MyDialogbox As Object
	MyDialogbox = DialogboxLibrariesVariable.GetByName("MYDIALOGBOXDISPLAY")

	'--THOSE TWO LINES ACTIVATES THE DIALOGBOX AND MAKE THE WHOLE CODE WORK.
	ActivationInterface = createunodialog(MyDialogbox)
	ActivationInterface.execute
End Sub

Sub Macro
	'--DECLARING VARIABLES
	Dim MyDoc As Object
	Dim MySheet As Object
	Dim FieldA_string As String
    	Dim FieldB_string As String
	MyDoc = ThisComponent
	MySheet = MyDoc.Sheets.getByName("SHEET")

	'--RETRIEVING DATA FROM DIALOGBOX. "FieldA" & "FieldB" ARE THE NAMES OF TWO TEXTFIELDS IN MY DIALOGBOX.
	FieldA_string = ActivationInterface.getControl("FieldA").Text
    	FieldB_string = ActivationInterface.getControl("FieldB").Text

	'--CONVERSION STEP. CONVERTING STRING VALUE FROM DIALOGBOX INTO DECIMAL VALUE, COMPATIBLE WITH DECIMAL SEPARATOR.
	MySheet.getCellByPosition(1, 1).Value = CDec(FieldA_string) / CDec(FieldB_string)

End Sub
'//----------------------------------------------------//


> Could you please see if your code works simply starting LO in safe mode; from > Start Center open Tools->Macros->Edit Macros; paste your code there; and 
> running it - without anything opened first.

I cannot lunch the macro in safe mode. It does not work even if I apply default settings in the Safe Mode options and set minimal security requirements. I will add a GIF to illustrate my point.

Created attachment 200993 [details]
[Update May 28] gif showing step-by-step attempt at launching macro in safe mode

By the way, I just updated LO to latest stable version and I still get the same initial problem.

Version: 24.8.7.2 (X86_64) / LibreOffice Community
Build ID: e07d0a63a46349d29051da79b1fde8160bab2a89
CPU threads: 8; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win
Locale: fr-FR (fr_FR); UI: fr-FR
Calc: CL threaded

(In reply to Cameron from comment #12)
> I cannot lunch the macro in safe mode. It does not work even if I apply
> default settings in the Safe Mode options and set minimal security
> requirements.

You cannot launch a macro in safe mode, *because* you try to run it from a document (which shows how you do not read what I wrote: I explicitly asked you to test *without anything opened first*). You never attached a sample document with the data or macros (only GIFs), and your GIFs show how complex your documents are - and you expect people who want to reproduce your problem to invent your data for themselves.

But I didn't ask you to attach your document - because even if you did that, it would be a hard work to make sense in someone else's complex code. I asked you to prepare a *standalone* macro, that does *not* depend on anything else. It should be easy to test for anyone; and in my comment 11, I described which steps should be enough. These steps did not include "look at GIFs and try to re-create something yourself; or open a document, and try to figure which of the sheets and which of the functions are relevant". The idea was: paste the code copied from here, into the "My Macros and Dialogs", and it would run.

And yes, it would run from there. But for a document-local macro, the default security level prevents that, which you see. Sigh; if you pay attention, when you *change* the security level, the dialog will show you a yellow warning about the need to reload the document (implemented in bug 159985 for version 24.8.0). It will show it, when the choice is *changed* - so it definitely appeared to you the first time (not shown on your screencast - because there you already didn't change the setting). So yes, your code would run in safe mode, too - but that wouldn't allow me to reproduce, because your code relies on your document, and comment 12 doesn't provide a code that works out-of-the-box.

Now let's see at your previous descriptions.

You start comment 0 with:

> One of the fields gives a wrong answer when performing the same mathematical
> operation: when trying dividing the values of two cells, if one of them has
> a decimal value between 0 and 1 (e.g.: 0.05), Calc gives a wrong answer. 
> 
> In my example : if field A (containing value of 3) divided by field B
> containing value of 1.5), Calc returns the value of 1.5 (instead of 2).

Just reading this already creates some confusion. It talks about "a decimal value between 0 and 1 (e.g.: 0.05)", and immediately provides an example of dividing 3 by 1.5, where both are greater than 1.

> I understood the cause of this: Calc (& BASIC) consider "," as a thousand
> separator while "." is used for decimal values (which is annoying, you
> should just keep one of them for decimal values and that's it).

This is wrong, thousand separator has nothing to do here. If it were relevant, then it would only affect treatment of "1.5", and if the decimal there would be treated as thousand separator, then the resulting number used in calculations would be 15 (or even 1500), and dividing 3 by any of these would make the result of the division not 1.5, nor 2, but a value less than 1 (either 0.2, or 0.002).

> ...
> 
> Steps to Reproduce:
> 1.Build a simple interface with two field boxes
> 2.Make them divide one another and make the result be written in a cell
> using this line of code for instance:  MySheet.getCellByPosition(1, 1).Value
> = Clng(FieldA_string) / CLng(FieldB_string)

And here is the problem, that you use CLng. This problem has been explained to you by Robert in comment 6. Yes, CLng is documented [1] to return a long *integer*. It *necessarily* converts any floating-point number that it may obtain from the string, into a *whole number*. Meaning, that "1.5" is rounded up to 2, which produces the result that you see: 3 / 2 = 1.5. You were advised to use CDbl.

> ...
> 
> Actual Results:
> Wrong mathematical result (or sometimes division by zero error)

So here we see the wrong expectation, not a wrong mathematical result.

Then in comment 7 you provide another screencast, showing that you replace CDec with CDbl, and get an error (that you translate in comment 8). But we can't see the actual value in "PrixProduit_string"and "Contenance_string" - and your dialog is complex, e.g. with some of the controls using currency.

You are in the position to inspect the strings locally, and create the *simple* code for us to reproduce. "Simple" meaning: you, when preparing that code, put a breakpoint before the failing line; you inspect the values of the strings; you create the code that has these exact string literally, like in

sub HereIsAReallySimpleReproducingMacro
  s1 = "2,02 euro"
  s2 = "3,0"
  MsgBox CDbl(s2) / CDbl(s1)
end sub

And note how my sample does not anybody to look at any kind of GIF, or to inspect something called "Sub DisplayingDialogbox" (which is even not referenced from "Sub Macro"!) to understand, what "ActivationInterface" is, and how to obtain it.

[1] https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03100600.html?DbPAR=BASIC

@cameron: Could you please attach a real example, which only contains the simple macro and throws this error for you? All this gifs won't help to reproduce the buggy behavior.

Created attachment 201009 [details]
[Update May 28] gif showing step-by-step attempt at using simplified macro in safe mode

Created attachment 201010 [details]
[Update May 28] gif showing step-by-step attempt at using simplified macro in normal mode

Created attachment 201011 [details]
LibreOffice Calc file containing simplified macro for testing [USE THIS FOR TESTING]

(In reply to Mike Kaganski from comment #15)

> You cannot launch a macro in safe mode, *because* you try to run it from a
> document (which shows how you do not read what I wrote: I explicitly asked
> you to test *without anything opened first*). 

Sure, I did your test. Check the new GIFs that shows it.

> You never attached a sample
> document with the data or macros (only GIFs), and your GIFs show how complex
> your documents are - and you expect people who want to reproduce your
> problem to invent your data for themselves.

I have added a sample using the code provided earlier. You can test it yourself and see what happens.

The purpose of the GIFs are to show the tests performed on my terminal. I could tell you "it doesn't work" to each of your tiresome comments, without those GIFs, you wouldn't know how I performed them and most of all: you could not identify visually the issue if you were an expert (or knew enough about the topic).

> It should be easy to test for anyone; and in my comment 11, I
> described which steps should be enough. These steps did not include "look at
> GIFs and try to re-create something yourself; or open a document, and try to
> figure which of the sheets and which of the functions are relevant". The
> idea was: paste the code copied from here, into the "My Macros and Dialogs",
> and it would run.

And again, if you knew enough about the topic, you would make a suggestion very much alike Robert's ("try this" or "have you tried ... ?"). These types of comments mean the person understood the issue at stake and, combined with his knowledge of LO, analysed the problem and made a suggestion for me to test.

You need to understand that making a simplified version of my work is time-consuming for an issue that I am not sure I am really responsible for (until proven otherwise).

> Just reading this already creates some confusion. It talks about "a decimal
> value between 0 and 1 (e.g.: 0.05)", and immediately provides an example of
> dividing 3 by 1.5, where both are greater than 1.

Can't believe you are actually making a case out of this insignificant detail. You get the idea and can see it from the GIFs (and now the sample code)

> This is wrong, thousand separator has nothing to do here. 

Yes, it does. The error comes from there. It may not be the direct cause, but it is definitely linked. I am not going back over this again as it has been explained and shown in the GIFs.

> If it were
> relevant, then it would only affect treatment of "1.5", and if the decimal
> there would be treated as thousand separator, then the resulting number used
> in calculations would be 15 (or even 1500), and dividing 3 by any of these
> would make the result of the division not 1.5, nor 2, but a value less than
> 1 (either 0.2, or 0.002).

Look mate. This may not be a consensus but I do not see the point of having "." and "," being different as explained in comment #1 and comment #4. Thousand separator should only be used - in my opinion - for visual purpose only to avoid confusion and errors.

> And here is the problem, that you use CLng. This problem has been explained
> to you by Robert in comment 6. Yes, CLng is documented [1] to return a long
> *integer*. It *necessarily* converts any floating-point number that it may
> obtain from the string, into a *whole number*. Meaning, that "1.5" is
> rounded up to 2, which produces the result that you see: 3 / 2 = 1.5. You
> were advised to use CDbl.

Yes, good point. Finally a useful comment!

> Then in comment 7 you provide another screencast, showing that you replace
> CDec with CDbl, and get an error (that you translate in comment 8). But we
> can't see the actual value in "PrixProduit_string"and "Contenance_string" -
> and your dialog is complex, e.g. with some of the controls using currency.
> 
> You are in the position to inspect the strings locally, and create the
> *simple* code for us to reproduce. "Simple" meaning: you, when preparing
> that code, put a breakpoint before the failing line; you inspect the values
> of the strings; you create the code that has these exact string literally,
> like in
> 
> sub HereIsAReallySimpleReproducingMacro
>   s1 = "2,02 euro"
>   s2 = "3,0"
>   MsgBox CDbl(s2) / CDbl(s1)
> end sub

The simplified version attached answers that request of yours.

> And note how my sample does not anybody to look at any kind of GIF, or to
> inspect something called "Sub DisplayingDialogbox" (which is even not
> referenced from "Sub Macro"!) to understand, what "ActivationInterface" is,
> and how to obtain it.

Not a very useful comment since I demonstrated the use of visually analysing a test, before providing any kind of code samples.
The two SUBs are linked to make it work, the comments were added in the code provided specifically to explain that...

Have had a look at the example.
There are 2 fields, which are currency fields.
CDec or CDbl don't know anything about '€'. So this functions will fail if you get the Text-Element of the controls. Text is what an element will show.

Change to

FieldA = ActivationInterface.getControl("FieldA").Value
FieldB = ActivationInterface.getControl("FieldB").Value
MySheet.getCellByPosition(1, 1).Value = FieldA / FieldB

Will give the right result.

Created attachment 201017 [details]
LibreOffice Calc file containing simplified macro for testing [USE THIS FOR TESTING]

(In reply to Robert Großkopf from comment #21)

Thanks Robert. I have changed:

FieldA_string = ActivationInterface.getControl("FieldA").Text
FieldB_string = ActivationInterface.getControl("FieldB").Text

To:

FieldA_string = ActivationInterface.getControl("FieldA").Value
FieldB_string = ActivationInterface.getControl("FieldB").Value

And kept CDec() here:

MySheet.getCellByPosition(1, 1).Value = CDec(FieldA_string) / CDec(FieldB_string)

... And the macro and mathematical operation works well.

However, it does not work if I take CDec() off. Cannot explain why but I've added the updated file & a GIF showing the error.

Even though this "patch" works well on the test-file, I have applied the corrections to my own file and still cannot make it work properly.

Created attachment 201018 [details]
[Update May 29] gif showing step-by-step attempt at using simplified macro with value-element instead of text-element

Created attachment 201019 [details]
LibreOffice Calc file containing simplified macro for testing [USE THIS FOR TESTING]

Previous updated file could not be opened.

(In reply to Cameron from comment #23)
> 
> However, it does not work if I take CDec() off. Cannot explain why but I've
> added the updated file & a GIF showing the error.

You have defined the variable FieldA_string as STRING. So you read out a value (which is double) as STRING and change it by CDec to a decimal value.

Define directly as DOUBLE and it will work.

(In reply to Robert Großkopf from comment #26)

Yes, it works. Thanks.

Cannot make it work on my own file (but even adding a MsgBox does not work on my own file either so it may have been corrupted).

I could upload a copy of my own file but everything is written in French, would that be an issue?

On another note:

- Regarding the use of CDec, you suggested to use the "Double" variable type as the "Decimal" does not exist (even though it is already documented as a variable type [1]). 
Because I was not very familiar with the "Double" variable, I tried understanding the difference with the "Decimal" variable. The "Double" variable is presented as the appropriate variable for simple & quick calculations:

"Further, double is concerned with is performance, while decimal is concerned with is precision. When using double, you are accepting a known trade-off: you won't be super precise in your calculations, but you will get an acceptable answer quickly. Whereas with decimal, precision is built into its type:"

Would it be appropriate to add the "Decimal" variable type as an enhancement for future LibreOffice development?

- Also regarding conversion functions like CDec: the available documentation does not include conditions and explanations on their use [3]. 
For instance, you explained that "CDec or CDbl don't know anything about '€'." and that does not feature in the documentation.

Also as an enhancement suggestion (regarding the official website mostly), having additional explanations on their use would be useful to everyone.

[1] https://help.libreoffice.org/latest/en-US/text/sbasic/shared/01020100.html?DbPAR=BASIC
[2] https://exceptionnotfound.net/decimal-vs-double-and-other-tips-about-number-types-in-net/
[3] https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03100060.html?DbPAR=BASIC

Created attachment 201028 [details]
LibreOffice Calc file containing simplified macro for testing [USE THIS FOR TESTING]

(In reply to Cameron from comment #27)
> 
> - Regarding the use of CDec, you suggested to use the "Double" variable type
> as the "Decimal" does not exist (even though it is already documented as a
> variable type [1]). 
> [1]
> https://help.libreoffice.org/latest/en-US/text/sbasic/shared/01020100.
> html?DbPAR=BASIC

Funny to read this. Seems this should be a titel for the following variables (Single, Double, Currency) There isn't shown any way to declare a variable as DECIMAL.
When trying this I get an error in StarBasic for unknown datatype.

"Double" will be the way to get the highest precision in StarBasic. You could change to CURRENCY - but it will only show a decimal value with 15 non-decimal and 4 decimal places.

I have only tested, like I do this with many things in Basic:

SUB Devide
	a = "3,00"
	b = "1,50"
	ac = CDbl(a)
	msgbox ac
	c = CDbl(a) / CDbl(b)
	msgbox c
END SUB

Changed a to "3,00 €" or something else. It will give something like "incompatible Datatype". Any character I add there will show this behavior. So CDbl will need numbers, decimal separators and space only. 
Then tried the same with CDec and get "not implemented". So the Help of LibreOffice isn't correct. Think it never exists…