Decimal point in European Numbers

Posted on 2006-03-29
Last Modified: 2008-01-09
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?

Question by:NessieB
    LVL 6

    Accepted Solution

    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
    LVL 2

    Expert Comment

    :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)
    LVL 26

    Expert Comment

    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

    Author Comment

    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

    Author Comment

    And another thing

    str$(1,234) = 1.234

    LVL 6

    Expert Comment

    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.
    LVL 6

    Expert Comment

    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?

    Author Comment

    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.
    LVL 6

    Expert Comment

    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.

    Author Comment

    Cheers Trev

    Looks like a eVal function is the key.  humph


    Author Comment

    I decided to go for the function route in the end.

    Thanks trev.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now