[Solved] command Val() not recognize decimal separator

[Ponny]

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

[Charlie Young]

Simplest thing I can think of offhand is a function that first changes the comma to a period, then takes the Val.

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

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.

[Ponny]

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

[RPG]

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.

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.

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

[JohnV]

Code: Select all

Sub Main  'Assumes two decimal places
v = Val("12,34")/100 
v = v + 10
Print v 'Result 22.34
End Sub

[B Marcelly]

Hello,

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.

Val() works as designed.
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)

[RPG]

Hello

Dim a As String
Dim b As Double
a = "1,25"
b = CDbl(a)

This not working for me. I have a local version with a comma for decimal values.

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)

I want have that value and not the effectivevalue

Romke

[Ponny]

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

[floris v]

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.

[B Marcelly]

Dim a As String
Dim b As Double
a = "1,25"
b = CDbl(a)

This not working for me. I have a local version with a comma for decimal values.

This is working for me. I have a local version with a comma for decimal values.

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.

[floris v]

Lol, beaten to it.

[RPG]

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

[B Marcelly]

Hi Romke,

LibreOffice 3.3.2 on openSUSE 11.3

Perhaps LibreOffice 3.3.2 differs from OOo on this function?

Code: Select all

print val(str("34,56"))*6

It is me unclear why the first line is working but it works

Help F1 : Str() converts a numeric variable or the result of a computation into a character string.

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

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.

[RPG]

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

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

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