[Solved] command Val() not recognize decimal separator
[Solved] command Val() not recognize decimal separator
Hoy-hoy
Situation is: Windows XP SP3, OpenOffice 3.1.0, OpenOffice Basic
Basic interpreter - command Val() not rocognize decimal separator, if it is comma (,).
Decimal point (.) as decimal separator recognize OK.
But I live in Europe / Slovenia, and not USA.
I tried this simply test:
Dim a As String
Dim b As Double
a = "1,25"
b = Val(a)
Last line returns number 125 to variable a. Decimal separator completely ignore.
I tried to report this to http://openoffice.org/bugzilla/report.cgi
but I couldn't. I can't login. The lock in righ end of status bar of browser is red and broken.
Hmmmmm..... I have a lot of files with tables and numbers, all with comma (,) decimal separator. Do I have to fix all of documents, for heaven's sake?
But, after all, today is nice sunny day. So, I hope to nice sunny solution.
civ-civ
Ponny
Situation is: Windows XP SP3, OpenOffice 3.1.0, OpenOffice Basic
Basic interpreter - command Val() not rocognize decimal separator, if it is comma (,).
Decimal point (.) as decimal separator recognize OK.
But I live in Europe / Slovenia, and not USA.
I tried this simply test:
Dim a As String
Dim b As Double
a = "1,25"
b = Val(a)
Last line returns number 125 to variable a. Decimal separator completely ignore.
I tried to report this to http://openoffice.org/bugzilla/report.cgi
but I couldn't. I can't login. The lock in righ end of status bar of browser is red and broken.
Hmmmmm..... I have a lot of files with tables and numbers, all with comma (,) decimal separator. Do I have to fix all of documents, for heaven's sake?
But, after all, today is nice sunny day. So, I hope to nice sunny solution.
civ-civ
Ponny
Last edited by Ponny on Tue May 10, 2011 3:41 pm, edited 1 time in total.
MS Windows 10: OpenOffice 4.1.13; LibreOffice 7.3.5.2
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: command Val() not recognize decimal separator
Simplest thing I can think of offhand is a function that first changes the comma to a period, then takes the Val.
This should work on strings with only one comma, and it also seems to be okay if the string uses spaces as the thousands separator.
Code: Select all
function ValComma(s as String) As Double
dim i As Long
dim v As String
v = s
if InStr(v,",") > 0 then
Mid(v,InStr(v,","),1) = "."
endif
ValComma = Val(v)
End Function
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: command Val() not recognize decimal separator
Can I ask just one thing? Is this problem persist also in later version of OOOoo, like 3.2 or 3.3? And what about LibreOffice? Is there anybody who knows, is this problem present also in LibreOffice?
Than you for all answers. I will think very deeply.
Good night
Ponny
Than you for all answers. I will think very deeply.
Good night
Ponny
MS Windows 10: OpenOffice 4.1.13; LibreOffice 7.3.5.2
Re: command Val() not recognize decimal separator
Hello
The answer of Charlie Young is as far I can understand real correct and also explain all the little steps. I have search also for a solution for me own and found two methodes. Both are working only values with a decimal point or comma. When there is used also thousand separators it does not work.
It is me unclear why the first line is working but it works, I have it test real often. I did have a little test and it seems to me that the version with replace is faster then with str but not much.
I think it is better to find an other way so you can avoid basic for importing data.
I use LibO.
Romke
The answer of Charlie Young is as far I can understand real correct and also explain all the little steps. I have search also for a solution for me own and found two methodes. Both are working only values with a decimal point or comma. When there is used also thousand separators it does not work.
Code: Select all
print val(str("34,56"))*6
print val(replace("34,56",",","."))*6
print val(replace("346",",","."))*6
print val("34,56")*6 ' This give a wrong value.
I think it is better to find an other way so you can avoid basic for importing data.
I use LibO.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: command Val() not recognize decimal separator
Code: Select all
Sub Main 'Assumes two decimal places
v = Val("12,34")/100
v = v + 10
Print v 'Result 22.34
End Sub
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: command Val() not recognize decimal separator
Hello,
You want to convert a string to a Double, then use the conversion function :
Val() works as designed.Ponny wrote:Situation is: Windows XP SP3, OpenOffice 3.1.0, OpenOffice Basic
Basic interpreter - command Val() not rocognize decimal separator, if it is comma (,).
Decimal point (.) as decimal separator recognize OK.
But I live in Europe / Slovenia, and not USA.
I tried this simply test:
Dim a As String
Dim b As Double
a = "1,25"
b = Val(a)
Last line returns number 125 to variable a. Decimal separator completely ignore.
You want to convert a string to a Double, then use the conversion function :
Code: Select all
Dim a As String
Dim b As Double
a = "1,25"
b = CDbl(a)
Bernard
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Re: command Val() not recognize decimal separator
Hello
I have discovered I can use the uno service "com.sun.star.util.NumberFormatter" for changing values who are in a control. The control does contain the values who I need in that service. It seems me off topic here but a little part of the code is not wrong.
I want have that value and not the effectivevalue
Romke
This not working for me. I have a local version with a comma for decimal values.B Marcelly wrote:Dim a As String
Dim b As Double
a = "1,25"
b = CDbl(a)
I have discovered I can use the uno service "com.sun.star.util.NumberFormatter" for changing values who are in a control. The control does contain the values who I need in that service. It seems me off topic here but a little part of the code is not wrong.
Code: Select all
oNumberFormatter=createunoservice("com.sun.star.util.NumberFormatter")
oNumberFormatter.attachNumberFormatsSupplier(oControl.FormatsSupplier)
GetControlXXX=oNumberFormatter.convertStringToNumber(oControl.FormatKey,oControl.text)
Romke
Last edited by acknak on Tue May 10, 2011 2:53 pm, edited 1 time in total.
Reason: spelling/clarification: "of topic" -> "off topic"
Reason: spelling/clarification: "of topic" -> "off topic"
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: command Val() not recognize decimal separator
Hoy-hoy
I made another longer test. Stupid, you'll probably say. But result is sehr interresant:
sub Main
Dim a$
Dim b As double
Dim c$
Dim d As Double
a = "1.123.123,12325"
b = CDbl(a) ' - - - - returns 1123123,12325
c = Str(b) ' - - - - returns "1123123.12325"
d = Val(c) ' - - - - returns 1123123,12325
End Sub
In fact, variable b is the final result for further work.
Variable double (portion) obviously shows always comma as decimal separator. Maybe is reason, I have slovenian version of OOOoo 3.1.0.
Another interessant thing is this. Function Str() converts decimal comma to decimal point. I don't know why. But it is not problem.
Well, in real life works this OK. I mean calculating. Like this:
Sub Main
Dim a$
Dim b As double
Dim c As Double
a = "1.003,12"
b = CDbl(a) ' returns 1003,12
c = b + 5.12 ' returns 1008,24 - result is OK
End Sub
By the way. Is there any chance to copy the value of a variable from "glasses" window to clipboard?
Hm, I thing, this problem is solved. I did not know how to solve this problem without your help. Thank you to all. Can I mark this topis as "solved" now, or maybe anybody wish to have the last word?
civ-civ
Ponny
I made another longer test. Stupid, you'll probably say. But result is sehr interresant:
sub Main
Dim a$
Dim b As double
Dim c$
Dim d As Double
a = "1.123.123,12325"
b = CDbl(a) ' - - - - returns 1123123,12325
c = Str(b) ' - - - - returns "1123123.12325"
d = Val(c) ' - - - - returns 1123123,12325
End Sub
In fact, variable b is the final result for further work.
Variable double (portion) obviously shows always comma as decimal separator. Maybe is reason, I have slovenian version of OOOoo 3.1.0.
Another interessant thing is this. Function Str() converts decimal comma to decimal point. I don't know why. But it is not problem.
Well, in real life works this OK. I mean calculating. Like this:
Sub Main
Dim a$
Dim b As double
Dim c As Double
a = "1.003,12"
b = CDbl(a) ' returns 1003,12
c = b + 5.12 ' returns 1008,24 - result is OK
End Sub
By the way. Is there any chance to copy the value of a variable from "glasses" window to clipboard?
Hm, I thing, this problem is solved. I did not know how to solve this problem without your help. Thank you to all. Can I mark this topis as "solved" now, or maybe anybody wish to have the last word?
civ-civ
Ponny
MS Windows 10: OpenOffice 4.1.13; LibreOffice 7.3.5.2
Re: command Val() not recognize decimal separator
It's not very likely that this problem will be fixed, because the decimal period in numbers is the standard in all programming languages, Pascal, BASIC, you name it. Spreadsheets form an exception. So if you want to enter values with a decimal comma and use them in a macro, you have to convert that to the notation with a period.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: command Val() not recognize decimal separator
This is working for me. I have a local version with a comma for decimal values.RPG wrote:This not working for me. I have a local version with a comma for decimal values.B Marcelly wrote:Dim a As String
Dim b As Double
a = "1,25"
b = CDbl(a)
Verify that you have Tools > Options > Language settings > Languages :
Local Settings = your language (country)
Just under, you must see a comma indicated as separator for decimal values (it is small but visible).
I tested with dutch (Netherland) and with dutch (Belgium), it works.
For english (USA) the comma is ignored.
For French(Switzerland), using dot separator, I got an error, probably a bug in the locale data.
Bernard
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Re: command Val() not recognize decimal separator
Lol, beaten to it.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
Re: [Solved] command Val() not recognize decimal separator
Hello B Marcelly
I believe cdbl("78,12") is working good for you. But it does not work for me. I have test it with an other user on the computer I seldom use and also I did make a new user profile and it does not work good. When I use the format function in BASIC I do get a value with a comma.
I have test the gnome calculator with 100/3 and that give 33,3333. Also I see a comma on the place you told. I have also switch the language to dutch but I always do keep the local setting for Dutch.
For me it is no problem. I do now have the openSUSE unstable version maybe that is the reason of the problem.
Romke
I believe cdbl("78,12") is working good for you. But it does not work for me. I have test it with an other user on the computer I seldom use and also I did make a new user profile and it does not work good. When I use the format function in BASIC I do get a value with a comma.
I have test the gnome calculator with 100/3 and that give 33,3333. Also I see a comma on the place you told. I have also switch the language to dutch but I always do keep the local setting for Dutch.
For me it is no problem. I do now have the openSUSE unstable version maybe that is the reason of the problem.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: [Solved] command Val() not recognize decimal separator
Hi Romke,
If the argument is a string, Str() interprets it using the locale settings to obtain a number, then converts this number to its string value using dot as decimal separator. Then Val() understands this string as a value.
Things may be very tricky in Basic. Tested on my OpenOffice with french locale :
Note 1
Str() receives the argument resulting from Basic automatic conversion of 3 + "2.7" : since it begins with a number, Basic converts the expression to a number. When converting the string to a number, the automatic conversion does not use the locale settings for decimal separator.
Note 2
Since the expression starts with a string, the automatic conversion of Basic interprets each part of the expression as string, that is 3 is converted to "3" and the + is the concatenation operator.
Note 3
Here Basic tries to find a number from "2,7". Since the automatic conversion does not use the locale settings for decimal separator, it decides that the number finishes at the comma.
Same results are obtained from OpenOffice.org 1.1.5, it is working as designed.
Perhaps LibreOffice 3.3.2 differs from OOo on this function?RPG wrote:LibreOffice 3.3.2 on openSUSE 11.3
Help F1 : Str() converts a numeric variable or the result of a computation into a character string.RPG wrote:It is me unclear why the first line is working but it worksCode: Select all
print val(str("34,56"))*6
If the argument is a string, Str() interprets it using the locale settings to obtain a number, then converts this number to its string value using dot as decimal separator. Then Val() understands this string as a value.
Things may be very tricky in Basic. Tested on my OpenOffice with french locale :
Code: Select all
' Result string
print str(2.7) ' 2.7
print str("2,7") ' 2.7
print str(3 + "2.7") ' 5.7 note 1
print str("2.7" +3) ' 2.73 note 2
print str(3 + "2,7") ' 5 note 3
Str() receives the argument resulting from Basic automatic conversion of 3 + "2.7" : since it begins with a number, Basic converts the expression to a number. When converting the string to a number, the automatic conversion does not use the locale settings for decimal separator.
Note 2
Since the expression starts with a string, the automatic conversion of Basic interprets each part of the expression as string, that is 3 is converted to "3" and the + is the concatenation operator.
Note 3
Here Basic tries to find a number from "2,7". Since the automatic conversion does not use the locale settings for decimal separator, it decides that the number finishes at the comma.
Same results are obtained from OpenOffice.org 1.1.5, it is working as designed.
Bernard
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Re: [Solved] command Val() not recognize decimal separator
Hello Bernard
That BASIC is very tricky is true, for that reason i read very careful the manual. Maybe I don't always understand it correct.
I did test it also on my system and I do not get the same result as you. On one line I do get an other result as you
I have several time tried to understand how to search in the system for finding bugs first for OOo and now sometimes for LibO. I did never succeed in finding good results for me. Maybe this is a reason for me to start again searching in the bug system for LibO there it seems partly a problem there.
Conclusion
I don't have it. That says more from me then from the difference between OOo and LibO. Most of the time I accept how it is working but I have the idea that it is not good that there is a difference between OOo and LibO in this case.
Thank you for the explanation
Romke
That BASIC is very tricky is true, for that reason i read very careful the manual. Maybe I don't always understand it correct.
I did test it also on my system and I do not get the same result as you. On one line I do get an other result as you
Code: Select all
print str(2.7) ' 2.7
print str("2,7") ' 2.7
print str(3 + "2.7") ' 5.7 note 1 'The result for me is 5
print str("2.7" +3) ' 2.73 note 2
print str(3 + "2,7") ' 5 note 3
Conclusion
I don't have it. That says more from me then from the difference between OOo and LibO. Most of the time I accept how it is working but I have the idea that it is not good that there is a difference between OOo and LibO in this case.
Thank you for the explanation
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2