Is this a bug in the VBA IsNumeric and CDbl () functions?

Question

Consider the following VBA function:

Function castAndAdd(inputValue As Variant) As Variant If IsNumeric(inputValue) Then castAndAdd = CDbl(inputValue) + 4 Else castAndAdd = inputValue End If End Function 

Calling this from an immediate window gives this output:

 ?castAndAdd("5,7") 61 ?castAndAdd("5, 7") 5, 7 

Making a call to "5.7", I found that IsNumeric("5,7") returns true . I thought that perhaps this gives such a result, because in Europe a comma is used as a decimal separator; this result is odd because I'm in the United States, so my locale should determine that Excel only recognizes the period as a decimal separator, right?

Even if we discard the Europe / USA problem, the big problem is that CDbl ("5.7") returns 57, so that CDbl("5,7") + 4 returns 61, not 9.7, as I would expect if the comma is a decimal separator. Is this a mistake, or am I just not understanding how to use CDbl() ?

Answer 1

A comma is not recognized as a decimal, but as a thousands separator. The mechanism is not smart enough to require at least three numbers to follow, but essentially it removes any of the thousands of separators when interpreted as numbers.

Thus, even CDbl("4,5,,6,7") will give 4567 as a number. All this is true when the comma is a thousands separator. If, as in some European countries, the dot is the thousands separator, then a similar thing will happen to the dots.