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).

Use the Text property of the cell, instead of the Value property. Does that help? — David Zemens
– David Zemens, Commented Mar 19, 2015 at 13:10

David Zemens · Accepted Answer · 2018-07-16 14:21:49Z

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.

FreeMan · Accepted Answer · 2015-03-18 20:57:40Z

1

I cannot speak with 100% certainty, but I believe that while Application.DecimalSeparator affects what is displayed in the cell, it appears that MsgBox uses the Windows locale default.

What happens if you try changing it at the Windows level? Also, are you certain that your SQL inserts are not interpreting it correctly?

If needed, a potential ~~workaround~~ kludge might be try:

Dim x as Integer
Dim xs as String
x = 1.5
xs = replace(cstr(x),".", ",")
SQLString = Stuff & xs & MoreStuff

edited Mar 18, 2015 at 20:57

answered Mar 18, 2015 at 20:42

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

Collectives™ on Stack Overflow

Changing decimal separator in VBA (not only in Excel)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related