No announcement yet.

Convert Decimal With Comma To Decimal With Point

  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert Decimal With Comma To Decimal With Point

    I was making a form where the user could enter a number; eg. 4.61

    However, depending on the user's background he might enter this as 4,61 (comma) or as 4.61

    In the form this variable comes in as a string. How can I convert this in VBA to a digit if I don't know in advance if the users system uses a . or , as the digit-seperator??

    Any help would be very grateful

    I thought that in access you can actually predefine the type that is entered into the form but I don't believe this is possible in Excel (or at least i could not find something like that)

    thanks for the help

    <snip by admin. Read the rules you have agreed to or you may be banned>
    Last edited by Dave Hawley; August 24th, 2008, 09:50.

  • #2
    Re: Forms Entry With Comma Or Point

    MsgBox CDbl(Me.TextBox1.Value)
    MsgBox CDbl(Replace(Me.TextBox1.Value, ",", ""))
    How to use well the Excel
    I want know...
    ps. Sheet2Web can use


    • #3
      Re: Forms Entry With Comma Or Point

      CDbl(Replace(Me.TextBox1.Value, ",", "."))
      However, the Replace should not be necessary; the type conversion functions consider the locale settings on the computer, so maybe just
      Entia non sunt multiplicanda sine necessitate.


      • #4
        Re: Forms Entry With Comma Or Point

        IF the textbox value is a number, you shouldn't need any conversion so long as you force the Value property of the TextBox


        • #5

          Re: Convert Decimal With Comma To Decimal With Point

          I agree, this is a real pain. I use something like the following:

          ' This function corrects a very annoying feature of Excel. Depending on the language, decimal points can be
          ' set to either comma's or points. This small function tests whether the same result is achieved when the
          ' comma in a string/variant is compared to the same numeric string/variant when a decimal point is used.
          ' If the result is different, it ASSUMES THAT THE COMMA IS INCORRECT and replaces it with a '.'
          ' It is called using:
          ' Result = Correct_Decimal_Usage(str1)
          ' where str1 is e.g. "34,78" and Result is dimensioned as a Double
          Function Correct_Decimal_Usage(ThisNum As Variant) As Double
          Dim Answ As Double
          If (CDbl(Replace(ThisNum, ",", "."))) <> CDbl(ThisNum) Then
               '    MsgBox "ThisNum " & CDbl(ThisNum) & " caused a problem!" & vbNewLine & " Should be: " & CDbl(Replace(ThisNum, ",", "."))
                   Answ = CDbl(Replace(ThisNum, ",", "."))
               '    MsgBox "str1 " & CDbl(ThisNum) & " is fine!"
                   Answ = CDbl(ThisNum)
          End If
          End Function
          If you wanted to be really clever, you could extend this so that it works for both comma2point and point2comma cases.