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)
Expression=Val( Expression)
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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think you're on the right track.

If you have 1200 instances of Val(string_variable) in your code then I'd suggest creating yourself a new little global helper function ... call it eVal, and do a find-replace changing all instances of Val( with eVal(.  Then add this function (or something similiar).

Public Function eVal(ByVal s as string) as double
    s = Replace$(s, ",", ".")
    eVal = Val(s)
End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
:DeadlyTrev could be right. The Val Function always assumes the "." as decimal delimiter.
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, ",", "")
  '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)
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NessieBAuthor Commented:
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
NessieBAuthor Commented:
And another thing

str$(1,234) = 1.234

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.
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?
NessieBAuthor Commented:
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.
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.
NessieBAuthor Commented:
Cheers Trev

Looks like a eVal function is the key.  humph

NessieBAuthor Commented:
I decided to go for the function route in the end.

Thanks trev.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.