CODEWITHSUNDEEP
excelvbalocale
JoshK
JoshK

Reputation: 89

Value2 Number as string

I've encountered some strange behavior. I often have to pass Double values as a string because I need to differ between "0" and "".

Look at this easy example: (I'm on a German locale, so my Decimal separator is a ",") 

Sub TestNumbers()
    With ActiveSheet
        .Cells(1, 1).Value2 = "1,6"
        .Cells(1, 2).Value2 = "1,67"
        .Cells(1, 3).Value2 = "1,678"
        .Cells(1, 4).Value = "1,6789"
    End With
End Sub

And now look at the Output: Why the hell?

Why does it omit the comma at 3 Decimal Places? It happens for .value and .value2 as you can see.

Upvotes: 2

Views: 661

Answers (1)

Rory
Rory

Reputation: 34075

VBA operates as if in a US locale. If you manually entered those items into cells in a US locale, you'd see exactly the same results, namely coercion to a number once there are sufficient digits to warrant the comma being a thousands separator.

I confess I don't really follow your logic with "I often have to pass Double values as a string because I need to differ between "0" and ""."

Upvotes: 1

Related Questions