Convert from string to double no matter the locale
in [Visual Studio]
|
From: Rick Rothstein (MVP - VB) on 15 Nov 2007 05:10 Ignore the "Excel VBA world" comment... I thought I was posting to a different newsgroup. No matter though, the functions are all valid in VB6. Rick "Rick Rothstein (MVP - VB)" <rickNOSPAMnews(a)NOSPAMcomcast.net> wrote in message news:_b2dnf_9jfIth6HanZ2dnUVZ_tajnZ2d(a)comcast.com... > >I solved that problem by introducing a global variable: >> Public European as Boolean >> >> and then setting it True/False by: >> >> Temp = Format(123 / 100, "0.00") >> European = (Mid(Temp, Len(Temp) - 2, 1) = ",") >> >> I also have a procedure which checks if a string is >> numeric, which means: >> - There may be a "-" as the first character only. >> - The other characters may only be ",", "." and "0-9". >> - There may only be ONE "." or ",". >> - If European I convert "." to "," else "," to ".". > > Here are some functions that I developed and posted awhile ago for the > compiled VB world, but which should work fine in Excel's VBA world as > well. The Regional Settings functions will return the indicated local > settings for the computer they are run on. The Number Checkers will tell > you if a string has the form of a valid number. > > Rick > > REGIONAL SETTINGS > ================================== > DateSeparator = Format$(0, "/") > > DecimalPoint = Format$(0, ".") > > ThousandsSeparator = Mid$(Format$(1000, "#,###"), 2, 1) > > Function DateFormat() As String > DateFormat = CStr(DateSerial(2003, 1, 2)) > DateFormat = Replace(DateFormat, "2003", "YYYY") > DateFormat = Replace(DateFormat, "03", "YY") > DateFormat = Replace(DateFormat, "01", "MM") > DateFormat = Replace(DateFormat, "1", "M") > DateFormat = Replace(DateFormat, "02", "dd") > DateFormat = Replace(DateFormat, "2", "d") > DateFormat = Replace(DateFormat, MonthName(1), "MMMM") > DateFormat = Replace(DateFormat, MonthName(1, True), "MMM") > End Function > > Function TimeFormat() As String > TimeFormat = CStr(TimeSerial(13, 22, 44)) > TimeFormat = Replace(TimeFormat, "22", "mm") > TimeFormat = Replace(TimeFormat, "44", "ss") > If InStr(TimeFormat, "13") > 0 Then > TimeFormat = Replace(TimeFormat, "13", "HH") > If InStr(CStr(TimeSerial(1, 22, 44)), "0") = 0 Then > TimeFormat = Replace(TimeFormat, "HH", "H") > End If > Else > TimeFormat = Replace(TimeFormat, "1", "h") > TimeFormat = Replace(TimeFormat, "0", "h") > TimeFormat = Replace(TimeFormat, "PM", "tt", , , vbTextCompare) > End If > End Function > > NUMBER CHECKERS > ================================== > Here are two functions that I have posted in the past for similar > questions..... one is for digits only and the other is for "regular" > numbers: > > Function IsDigitsOnly(Value As String) As Boolean > IsDigitsOnly = Len(Value) > 0 And _ > Not Value Like "*[!0-9]*" > End Function > > Function IsNumber(ByVal Value As String) As Boolean > ' Leave the next statement out if you don't > ' want to provide for plus/minus signs > If Value Like "[+-]*" Then Value = Mid$(Value, 2) > IsNumber = Not Value Like "*[!0-9.]*" And _ > Not Value Like "*.*.*" And _ > Len(Value) > 0 And Value <> "." And _ > Value <> vbNullString > End Function > > Here are revisions to the above functions that deal with the local > settings for decimal points (and thousand's separators) that are different > than used in the US (this code works in the US too, of course). > > Function IsNumber(ByVal Value As String) As Boolean > Dim DP As String > ' Get local setting for decimal point > DP = Format$(0, ".") > ' Leave the next statement out if you don't > ' want to provide for plus/minus signs > If Value Like "[+-]*" Then Value = Mid$(Value, 2) > IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ > Not Value Like "*" & DP & "*" & DP & "*" And _ > Len(Value) > 0 And Value <> DP And _ > Value <> vbNullString > End Function > > I'm not as concerned by the rejection of entries that include one or more > thousand's separators, but we can handle this if we don't insist on the > thousand's separator being located in the correct positions (in other > words, we'll allow the user to include them for their own purposes... > we'll just tolerate their presence). > > Function IsNumber(ByVal Value As String) As Boolean > Dim DP As String > Dim TS As String > ' Get local setting for decimal point > DP = Format$(0, ".") > ' Get local setting for thousand's separator > ' and eliminate them. Remove the next two lines > ' if you don't want your users being able to > ' type in the thousands separator at all. > TS = Mid$(Format$(1000, "#,###"), 2, 1) > Value = Replace$(Value, TS, "") > ' Leave the next statement out if you don't > ' want to provide for plus/minus signs > If Value Like "[+-]*" Then Value = Mid$(Value, 2) > IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ > Not Value Like "*" & DP & "*" & DP & "*" And _ > Len(Value) > 0 And Value <> DP And _ > Value <> vbNullString > End Function >
From: Tony Proctor on 30 Nov 2007 14:36 I was going to post a shorter variation of this Bert, i.e. European = (Format$(0,".") = ",") but then noticed Rick's assorted functions already include this trick :-) Tony Proctor "Bert van den Dongen" <bertdon(a)tiscali.nl> wrote in message news:473c0955$0$24408$5fc3050(a)news.tiscali.nl... > I solved that problem by introducing a global variable: > Public European as Boolean > > and then setting it True/False by: > > Temp = Format(123 / 100, "0.00") > European = (Mid(Temp, Len(Temp) - 2, 1) = ",") > > I also have a procedure which checks if a string is > numeric, which means: > - There may be a "-" as the first character only. > - The other characters may only be ",", "." and "0-9". > - There may only be ONE "." or ",". > - If European I convert "." to "," else "," to ".". > > Hope this helps. > Bert. > > > > "Dag Sunde" <me(a)dagsunde.com> schreef in bericht > news:473be720(a)news.broadpark.no... > > > > "Steve Gerrard" <mynamehere(a)comcast.net> wrote in message > > news:JtmdncPY64ZeN6banZ2dnUVZ_hisnZ2d(a)comcast.com... > >> > >> "Fernando Rodriguez" <fernandoREMOVE_THIS(a)easyjob.net> wrote in message > >> news:a33bd843d9ec8c9f4fa49953282(a)nntp.aioe.org... > >>> > >>> Hi, > >>> > >>> The result from CSng("3.5") apparently depends on the locale. If I try > >>> it on a Spanish PC, I'll get 35 back instead of 3.5 I can "fix" it by > >>> call CDbl() on "3,5" instead, but I need this to work with both "3.5" > >>> and "3,5" no matter the locale. > >>> > >>> How can I have this conversion done correctly for both cases? > >>> > >>> > >> > >> When your program is running in a locale that uses a comma for decimals, > >> there should not be any strings of the form "3.5". Where are those coming > >> from, or why do you have them in your program? > >> > > > > From confused users, I guess... > > > > The (pseudo) code below is far from solid enough, but is a starting point > > > > Function num2num( byval v as string) as double > > > > v = Trim$(v) > > v = Replace(v, ",", ".") > > v = Replace(v, " ", "") > > > > num2num = CDbl(Val(v)) > > > > End Function > > > > -- > > Dag. > > >
From: Bert van den Dongen on 1 Dec 2007 06:19 "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> schreef in bericht news:fiponl$79l$1(a)reader01.news.esat.net... >I was going to post a shorter variation of this Bert, i.e. > > European = (Format$(0,".") = ",") > > but then noticed Rick's assorted functions already include this trick :-) > > Tony Proctor Yes, much more elegant, Tony. However I could not find the "." in 'Format$(0,".") in the VB Help. "0" and "#" yes, but not ".". How did you get to that? Bert.
From: Mike Williams on 1 Dec 2007 07:16 On 1 Dec, 11:19, "Bert van den Dongen" <bert...(a)tiscali.nl> wrote: > However I could not find the "." in 'Format$(0,".") > in the VB Help. "0" and "#" yes, but not ".". I'm not sure whether your own MSDN is the same as mine, or whether you've got it all installed, but if I type Format into some code and hit F1 with the cursor on that word I get taken to the help page for the Format function. If I then click the little blue "See Also" link I get a pop up window in which I can select various related topics. The stuff you are looking for should be listed in that window as "User- defined Numeric Formats". If you haven't got MSDN installed then check out: http://msdn2.microsoft.com/en-us/library/aa263416(VS.60).aspx Mike
From: Bert van den Dongen on 1 Dec 2007 07:57
"Mike Williams" <gagamomo(a)yahoo.co.uk> schreef in bericht news:d8ebb246-93f7-4974-84b4-ebd015a9cb8c(a)e67g2000hsc.googlegroups.com... > On 1 Dec, 11:19, "Bert van den Dongen" <bert...(a)tiscali.nl> wrote: > >> However I could not find the "." in 'Format$(0,".") >> in the VB Help. "0" and "#" yes, but not ".". > > I'm not sure whether your own MSDN is the same as mine, or whether > you've got it all installed, but if I type Format into some code and > hit F1 with the cursor on that word I get taken to the help page for > the Format function. If I then click the little blue "See Also" link I > get a pop up window in which I can select various related topics. The > stuff you are looking for should be listed in that window as "User- > defined Numeric Formats". If you haven't got MSDN installed then check > out: > > http://msdn2.microsoft.com/en-us/library/aa263416(VS.60).aspx > > Mike > I do have the complete MSDN and I did all the things you suggested, but never went to "User-defined Numeric Formats", because I assumed that that would deal with formats you design yourself. And since Tony's Format$(0,".") worked rightaway I skipped that part. I know better now. Thanks Mike and Tony. Bert. |