[Solved] command Val() not recognize decimal separator

Creating a macro - Writing a Script - Using the API

[Solved] command Val() not recognize decimal separator

Postby Ponny » Mon May 09, 2011 7:42 pm

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
Last edited by Ponny on Tue May 10, 2011 3:41 pm, edited 1 time in total.
MS Windows 10: OpenOffice 4.1.7
User avatar
Ponny
 
Posts: 128
Joined: Sun Jun 21, 2009 7:34 pm
Location: Mystical Kingdom of Krakovo

Re: command Val() not recognize decimal separator

Postby Charlie Young » Mon May 09, 2011 9:15 pm

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   Expand viewCollapse view
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.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: command Val() not recognize decimal separator

Postby Ponny » Mon May 09, 2011 9:25 pm

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
MS Windows 10: OpenOffice 4.1.7
User avatar
Ponny
 
Posts: 128
Joined: Sun Jun 21, 2009 7:34 pm
Location: Mystical Kingdom of Krakovo

Re: command Val() not recognize decimal separator

Postby RPG » Mon May 09, 2011 10:01 pm

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   Expand viewCollapse view
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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: command Val() not recognize decimal separator

Postby JohnV » Tue May 10, 2011 2:32 am

Code: Select all   Expand viewCollapse view
Sub Main  'Assumes two decimal places
v = Val("12,34")/100
v = v + 10
Print v 'Result 22.34
End Sub
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: command Val() not recognize decimal separator

Postby B Marcelly » Tue May 10, 2011 10:27 am

Hello,
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.

Val() works as designed.
You want to convert a string to a Double, then use the conversion function :
Code: Select all   Expand viewCollapse view
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
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: command Val() not recognize decimal separator

Postby RPG » Tue May 10, 2011 11:52 am

Hello

B Marcelly wrote: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   Expand viewCollapse view
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
Last edited by acknak on Tue May 10, 2011 2:53 pm, edited 1 time in total.
Reason: spelling/clarification: "of topic" -> "off topic"
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: command Val() not recognize decimal separator

Postby Ponny » Tue May 10, 2011 2:27 pm

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


:super: Ponny :bravo:
MS Windows 10: OpenOffice 4.1.7
User avatar
Ponny
 
Posts: 128
Joined: Sun Jun 21, 2009 7:34 pm
Location: Mystical Kingdom of Krakovo

Re: command Val() not recognize decimal separator

Postby floris v » Tue May 10, 2011 2:35 pm

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.
AOO 4.1.6 op Linux Mint
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
User avatar
floris v
Volunteer
 
Posts: 4250
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: command Val() not recognize decimal separator

Postby B Marcelly » Tue May 10, 2011 2:48 pm

RPG wrote:
B Marcelly wrote: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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: command Val() not recognize decimal separator

Postby floris v » Tue May 10, 2011 3:11 pm

Lol, beaten to it.
AOO 4.1.6 op Linux Mint
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
User avatar
floris v
Volunteer
 
Posts: 4250
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: [Solved] command Val() not recognize decimal separator

Postby RPG » Tue May 10, 2011 8:07 pm

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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] command Val() not recognize decimal separator

Postby B Marcelly » Wed May 11, 2011 8:35 am

Hi Romke,
RPG wrote:LibreOffice 3.3.2 on openSUSE 11.3

Perhaps LibreOffice 3.3.2 differs from OOo on this function?

RPG wrote:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
'                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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: [Solved] command Val() not recognize decimal separator

Postby RPG » Wed May 11, 2011 9:45 am

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   Expand viewCollapse view
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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests