CODEWITHSUNDEEP
excelvbalocaledecimal-point
oneindelijk
oneindelijk

Reputation: 636

Inconsistent behavior of conversion to/from locales

I'm noticing strange and anoying 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 like this (normally the string "0,5" is replaced by textbox.value)

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

Also when using the numberformat() function this get me into this kind of troubles.

At first I had a the KeyPresses in the textbox limited like this:

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

But the vbDecimal would only allow me to enter '.' as a decimal point and then at runtime it would be interpreted as times 1000 the value.

Any light on this ?

Upvotes: 4

Views: 2252

Answers (4)

Mnyikka
Mnyikka

Reputation: 1261

            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
            Dim PowerLength As Long

            IndexOne = LBound(SplitArray)
            IndexTwo = UBound(SplitArray)
            FirstNumber = CDbl(SplitArray(IndexOne))
            GetNumber = FirstNumber

            If (IndexTwo > IndexOne) Then
                SecondNumber = CDbl(SplitArray(IndexTwo))

                PowerLength = Len("" & CDbl("1" & SplitArray(IndexTwo)))
                PowerLength = PowerLength - 1

                If (FirstNumber < 0) Then
                    SecondNumber = SecondNumber * -1
                End If
                GetNumber = GetNumber + (SecondNumber / Application.WorksheetFunction.Power(10, PowerLength))
            End If



            EndNow:
            End Function

Upvotes: -1

Fabio Pereira
Fabio Pereira

Reputation: 348

Here in Brazil we have the same number notation ("," as decimal separator, "." as thousands delimiter). I normally use CDbl() to convert numbers because it takes into account the regional locale setting. As you already put out, val() is too inconsistent for any real world use for me. It gets easily confused with the commas and dots.

This is what I get with each function. Remember that the value is returned in the regional setting (in this case, comma as decimal separator), but internally it's independent of the notation.

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

Here, val() stops reading the string on the comma - thus reading only "2.500", and considers the dot as a decimal separator. On the other hand, CDbl() recognizes the number completely.

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

And here, everything becomes a mess as you can see... once again val() stops reading on the comma (even though the number is in US notation), and CDbl() gets confused with the "misplaced" thousands delimiter after the decimal separator and just ignores it.

Upvotes: 1

Automate This
Automate This

Reputation: 31364

Edit Found a good example here of overriding 'Regional Locale Numeric Settings'

You could try to accomodate either European or USA seperators by looking at the local environmental 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 somthing like this:

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

I'm sure there is a more efficient method but if your loading a dll you can now have two different dll's load depending on your support.

Upvotes: 1

user2480047
user2480047

Reputation:

The Val function only recognises "." as decimal separator (as you can read in the corresponding MSDN article); equivalent thing happens with other VBA functions. Thus, if you modify the decimal separator being accounted by VBA/Excel, quite a few functions wouldn't care (and would continue using "." anyway). What I usually do is setting up a custom function which analyses all the input data (it can consist just in the basic Replace you are referring in your question; but I also take advantage from this and adapt the number format to the one I am expecting, for example: maximum number of decimal positions), making sure that all the decimal separators accounted during the calculations are ".". Once all the calculations are done, I have another function to adapt the outputs to the expected format (via modifying the Excel cell or the numeric variables themselves). It is not the ideal situation neither a too-difficult-to-be-fixed problem if you know it.

Upvotes: 2

Related Questions