Code:MsgBox CDbl(Me.TextBox1.Value) or MsgBox CDbl(Replace(Me.TextBox1.Value, ",", ""))
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 at 10:50.
However, the Replace should not be necessary; the type conversion functions consider the locale settings on the computer, so maybe justCode:CDbl(Replace(Me.TextBox1.Value, ",", "."))
Entia non sunt multiplicanda sine necessitate.
MS MVP - Excel
IF the textbox value is a number, you shouldn't need any conversion so long as you force the Value property of the TextBox
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
If you wanted to be really clever, you could extend this so that it works for both comma2point and point2comma cases.Code: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, ",", ".")) Else ' MsgBox "str1 " & CDbl(ThisNum) & " is fine!" Answ = CDbl(ThisNum) End If End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)