Inconsistent conversion behavior to / from locales

Question

I notice strange and anonymous behavior in excel, probably due to the different number notation in Belgium. When I type this in the immediate window, I get counter-intuitive results:

?val("0,5") 0 ?val("0.5") 0,5 

so my workaround is to use it that way (usually the string “0.5” is replaced with textbox.value)

  val(replace("0,5",",",".")) 

Also, when you use the numberformat () function, this leads me to such problems.

First, I had a Keypresses restriction in the text box:

 Select Case KeyAscii Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyDelete, vbKeyLeft, _ vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab, vbDecimal Case Else KeyAscii = 0 Beep End Select 

But vbDecimal would let me type '.' as a decimal point, and then at runtime it will be interpreted as a time value of 1000.

Any light?

Answer 1

Val only recognizes "." as a decimal separator (as you can read in the corresponding MSDN article); a similar thing happens with other VBA functions. Thus, if you change the decimal separator that VBA / Excel will take into account, many functions will be indifferent (and will continue to use the "." Anyway). Usually I create a custom function that analyzes all the input data (it can only consist in the base Replace , which you refer to your question, but I also use this and adapt the number format to the one I expect, for example: maximum number of decimal places ), making sure that all decimal separators taken into account during the calculations are "." . Once all the calculations have been completed, I have one more function for adapting the outputs to the expected format (by changing the Excel cell or the numeric variables themselves). If you know this, this is not an ideal situation or a problem that is not too difficult for you.

Answer 2

Edit Found a good example here about changing "regional language digital settings"

You can try placing European or American secateurs by looking at local environment variables.

 'Locale support Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long Const LOCALE_ICOUNTRY = &H5 ' country code Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country Const LOCALE_USER_DEFAULT = &H400 

Then in your code do something like this:

 If getLocale <= 1 Then 'US 'use commas else 'UK 'use period End If 

I am sure there is a more efficient method, but if you download the dll, now you can have two different DLL loads depending on your support.

Answer 3

Here in Brazil, we have a number designation ("," as the decimal separator, "." As the thousands separator). I usually use CDbl() to convert numbers, since it takes into account the locale's regional settings. As you put it, val() too inconsistent for any use in the real world for me. It mixes easily with commas and dots.

This is what I get with every function. Remember that the value is returned in the regional setting (in this case, a comma as a decimal separator), but internally does not depend on the notation.

 ? val("2.500,50") 2,5 ? cdbl("2.500,50") 2500,5 

Here val() stops reading the line in comma - thus, reading only "2.500" and treating the point as a decimal separator. CDbl() , CDbl() other hand, fully recognizes a number.

 ? val("2,500.50") 2 ? cdbl("2,500.50") 2,5005 

And here everything becomes useless, as you can see ... again val() stops reading the comma (although the number is in US notation), and CDbl() gets confused with the "inappropriate" thousands separator after the decimal separator and just ignores it.

Answer 4

  Public Function GetNumber(ByVal Subject As String, ByVal Default As Double) As Double GetNumber = Default On Error GoTo EndNow Subject = Replace(Subject, ",", ".") Subject = Trim(Subject) Dim SplitArray() As String SplitArray = Split(Subject, ".", 2) Dim FirstNumber As Double Dim SecondNumber As Double SecondNumber = 1 Dim IndexOne As Long Dim IndexTwo As Long IndexOne = LBound(SplitArray) IndexTwo = UBound(SplitArray) FirstNumber = CDbl(SplitArray(IndexOne)) GetNumber = FirstNumber If (IndexTwo > IndexOne) Then SecondNumber = CDbl(SplitArray(IndexTwo)) If (FirstNumber < 0) Then SecondNumber = SecondNumber * -1 End If GetNumber = GetNumber + (SecondNumber / Application.WorksheetFunction.Power(10, Len(("" & SecondNumber)))) End If EndNow: End Function