Decimal point in European Numbers
I know that this question has been asked a few times one of the best threads was Question 20347482 and Azrasounds reply.
The point is that using val(MyText) does not work in Europe where the decimal separator is a comma.
so val(0,123) = zero and not 0.123 and val(1,234) = 1 not 1.234
The advice using CDbl(0,123) = 0.123 does not work if there is any non-numeric characters in the text, which defeats the point of using it.
so I am left with creating a function like Azrasound suggested
But I just want to use it on examples where I would have used Val("1,234") = 1.234
and also Mytext = "1 item" val(Mytext) = 1
My project has over 1200 instances where I need the value of a string returned. So to add code to solve this for say German users is impractical.
The following is garbage , but gives the idea of what I think I need.
Public Function eVal(ByVal Expression)
if Expression contains a comma And doesnt contain any non-numeric chatacters then
Expression= CDbl(Expression)
else
Expression=Val( Expression)
endif
end function
MyText = "1,234 Items"
eVal(MyText) = 1.234
It has to work returning the correct value of a text string irrespective of what the users Regional and Language settings are.
Its quite simple really but Im chasing my tail trying to make it work
So for 500 points who can help me out?
The point is that using val(MyText) does not work in Europe where the decimal separator is a comma.
so val(0,123) = zero and not 0.123 and val(1,234) = 1 not 1.234
The advice using CDbl(0,123) = 0.123 does not work if there is any non-numeric characters in the text, which defeats the point of using it.
so I am left with creating a function like Azrasound suggested
But I just want to use it on examples where I would have used Val("1,234") = 1.234
and also Mytext = "1 item" val(Mytext) = 1
My project has over 1200 instances where I need the value of a string returned. So to add code to solve this for say German users is impractical.
The following is garbage , but gives the idea of what I think I need.
Public Function eVal(ByVal Expression)
if Expression contains a comma And doesnt contain any non-numeric chatacters then
Expression= CDbl(Expression)
else
Expression=Val( Expression)
endif
end function
MyText = "1,234 Items"
eVal(MyText) = 1.234
It has to work returning the correct value of a text string irrespective of what the users Regional and Language settings are.
Its quite simple really but Im chasing my tail trying to make it work
So for 500 points who can help me out?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't have Europe system to test.
Can you use CCur()
You may also want to check what is on regional setting before you do the replace
Can you use CCur()
You may also want to check what is on regional setting before you do the replace
ASKER
Thanks Guys.
It seems such a simple request for programs function for users in Europe, yet its so messy to do.
If I was able to programatically change their Language settings to English while my program was running how would this affect say an Excel program that was running ast the same time? Would this be a bad move?
Thanks for the help and advice
It seems such a simple request for programs function for users in Europe, yet its so messy to do.
If I was able to programatically change their Language settings to English while my program was running how would this affect say an Excel program that was running ast the same time? Would this be a bad move?
Thanks for the help and advice
ASKER
And another thing
str$(1,234) = 1.234
why?
str$(1,234) = 1.234
why?
I would not recommend changing a user's language settings ... you'd get unpredictable results from different applications. It may be possible to change it for an individual process .... I'd have to look into it.
Str$(1,234) should only = "1.234" where the regional settings have "," as a decimal indicator (AFAIK).
When I try that with my machine set to International English & Australia, VB6 says "Wrong number of arguments" for the Str$ function.
Str$(1,234) should only = "1.234" where the regional settings have "," as a decimal indicator (AFAIK).
When I try that with my machine set to International English & Australia, VB6 says "Wrong number of arguments" for the Str$ function.
From Win32 SDK ...
"The system assigns a locale to each thread. Initially, the system assigns the system default locale to the thread. This default locale is set by the user when the system is installed or through Regional Options in Control Panel. If a thread is run in a process belonging to a user, the system assigns the user-default locale to the thread. An application can override either default by using the SetThreadLocale function to explicitly set the locale for a thread."
Perhaps your VB6 application could determine it's process ID, enumerate its threads, and then call SetThreadLocale on each?
"The system assigns a locale to each thread. Initially, the system assigns the system default locale to the thread. This default locale is set by the user when the system is installed or through Regional Options in Control Panel. If a thread is run in a process belonging to a user, the system assigns the user-default locale to the thread. An application can override either default by using the SetThreadLocale function to explicitly set the locale for a thread."
Perhaps your VB6 application could determine it's process ID, enumerate its threads, and then call SetThreadLocale on each?
ASKER
Thanks v.much for all your input Trev.
I am amazed that such a simple request causes such difficulty. This question is quite old now by EE standards and so far not much input from the resident Gurus.
I'll let it run a bit longer if thats ok. Some one somewhere may actually have an answer - not that your replies arent valid - but I was hoping that someone would say 'do this and it will solve the problem'
How do German users write their programs for say the USA market?
Do they all simply use "." and not "," ? surely not.
I am amazed that such a simple request causes such difficulty. This question is quite old now by EE standards and so far not much input from the resident Gurus.
I'll let it run a bit longer if thats ok. Some one somewhere may actually have an answer - not that your replies arent valid - but I was hoping that someone would say 'do this and it will solve the problem'
How do German users write their programs for say the USA market?
Do they all simply use "." and not "," ? surely not.
From the book "Internationalization with Visual Basic - The authoritative solution", Michael S Kaplan 0-672-31977-2:
"The most important thing to keepp in mind is that in the code, a hard-coded number should always use the U.S. decimal separator and no commas. ... Just as with dates, however, any time a number becomes a string, VB and COM expect it to follow the format of the regional settings in the user's control panel. Although VB will try to make guesses and interchange separators as it does with dates, usually you will end up with a runtime error 13 (type mismatch)."
The book gives plenty of reasons why failure to be consistant with strings of numbers and currency values will cause you nightmares, and also offers some insight why VB has an IsNumeric function but no IsCurrency function. It suggests using the FormatNumber, FormatCurrency and FormatPercent functions to convert numbers to strings for display purposes and otherwise don't use strings for numbers.
How to make a 'friendly' textbox that accepts decimal numbers for users that don't use a . as a decimal separator? Have your program format a decimal to a string and see what character is used as the decimal. Then, in the keypress event for the textbox ignore any keystroke that isn't a digit, plus/minus sign or a valid decimal character. After input substitute the decimal character with a '.' and Val() the string.
Hope this helps.
Cheers!
DT.
"The most important thing to keepp in mind is that in the code, a hard-coded number should always use the U.S. decimal separator and no commas. ... Just as with dates, however, any time a number becomes a string, VB and COM expect it to follow the format of the regional settings in the user's control panel. Although VB will try to make guesses and interchange separators as it does with dates, usually you will end up with a runtime error 13 (type mismatch)."
The book gives plenty of reasons why failure to be consistant with strings of numbers and currency values will cause you nightmares, and also offers some insight why VB has an IsNumeric function but no IsCurrency function. It suggests using the FormatNumber, FormatCurrency and FormatPercent functions to convert numbers to strings for display purposes and otherwise don't use strings for numbers.
How to make a 'friendly' textbox that accepts decimal numbers for users that don't use a . as a decimal separator? Have your program format a decimal to a string and see what character is used as the decimal. Then, in the keypress event for the textbox ignore any keystroke that isn't a digit, plus/minus sign or a valid decimal character. After input substitute the decimal character with a '.' and Val() the string.
Hope this helps.
Cheers!
DT.
ASKER
Cheers Trev
Looks like a eVal function is the key. humph
Looks like a eVal function is the key. humph
ASKER
I decided to go for the function route in the end.
Thanks trev.
Thanks trev.
The cDbl Function uses systems default and throws error if wrong signs are used. cDbl also accepts the "grouping" delimiter Bsp: cDbl(123.400.12,91)
Now it depends on what are your goal. If you want the user to be able to use grouping then
DeadlyTrev´s version returns wrong values cause the german grouping sign "," is replaced with ".". Im using a version like this , which eliminates Grouping first.
Private Function dblVal(ByVal sInput As String)
On Local Error Resume Next
Dim dblDummy As Double
'Check if "." is the decimal delimter
dblDummy = CDbl("1.2")
If dblDummy = 1.2 Then
'DecimalDel = "."
'Remove GroupSign
sInput = Replace(sInput, ",", "")
Else
'DecimalDel = ","
'Remove GroupSign
sInput = Replace(sInput, ".", "")
'Replace Dez. sing
sInput = Replace(sInput, ",", ".")
End If
dblVal = Val(sInput)
If Err.Number <> 0 Then
'...
End If
End Function
If you want the user to use the decimal and gouping delimiter of his choice you may be can parse the Input String whether "." or "," is used first (instr function)
But the properbly best way is asking the System for its delimiters and replace it for VAL function
(I´m not sure about the API to use 4 this and not knowing the right Registry Key to read at the moment)