Changing decimal separator in VBA (not only in Excel)

Question

Type in A1 cell =1/10

Run this macro:

Sub ChangeDecimalSeparator()
    Application.DecimalSeparator = "#"
    Application.UseSystemSeparators = False
    MsgBox Range("A1").Value
End Sub

Apparently now in cell A1 you can see that the decimal separator has been changed to new character #. Why on earth VBA still sees it as your default seperator, although you have changed it (I mean the MsgBox).

Why I need it? My default separator is , (comma). I want to insert data to SQL which used . (dot).

Answer 1

Application.DecimalSeparator affects what's displayed in the cell, and it's a minor, but important distinction that what's displayed in the cell is not always the same as the cell's .Value property, especially if we are tinkering with the way the value is formatted or displayed. This is apparent when using date values, for instance:

As you can see, no matter the format of the cell, the MsgBox function uses my (US) locale to format the date:

The exception, which is not really an exceptions, is if the MsgBox function is passed a string literal:

MsgBox(Format(#6/1/2018#,"dd-mm-yyyy"))

Here, we're not passing a date, but a string, and MsgBox will display that exactly per the Format function:

And this is kind of the same thing that's going on when you tinker with the DecimalSeparator or other separator properties. You haven't changed the underlying values, only the way that Excel Application represents them in the UI.

You might expect the MsgBox function to use the overrides that you've assigned through Application.DecimalSeparator, but the MsgBox function accepts only a String type for its first argument, so when you pass anything other than a string, there is an implicit conversion to string type, essentially what's happening is:

MsgBox(CStr(Range("A1").Value))

The value is still locale-agnostic, and now you're casting that value to a string. The only real logical output is to use the windows/system local to handle that cast.

TL;DR

You can use the cell's Text property to get at what's actually displayed in the cell, rather than the cell's true value.