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 it from the immediate window gives this output:
?castAndAdd("5,7")
61
?castAndAdd("5, 7")
5, 7
Stepping through the "5,7" call, I find that IsNumeric("5,7") returns true. I was thinking that maybe it gives this 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 a period as a decimal separator, right?
Even if we set aside the Europe/US issue, the bigger problem is that CDbl("5,7") returns 57, so that CDbl("5,7") + 4 returns 61, not 9.7 as I would have expected if the comma is a decimal separator. Is this a bug, or am I just not understanding how to use CDbl()?