Why is VBA changing decimal to comma automatically?

Question

I have an Excel macro in VBA. Yesterday everything worked fine, this morning VBA is changing a decimal point to a comma. That is from 5.1 to 5,1.

I am using a German system and have set in Excel's advanced options that a point is a decimal and comma is thousands.

For example I have a value for daily scrum meeting in the Excel sheet set at 3.75

Excel sheet input:

when I use a function to read in the value such as:

Sub TestFunction()
    MsgBox (Sheets("Input_Parameters").Cells(25, 2))
End Sub

I get the value with commas.

VBA function output:

Is there a setting in the VBA environment to fix this or is there something I am missing. All my backups of the macros are showing the same affect so I know it is nothing I have changed in the code in today's version.

Solution 1

This is a normal behavior on German systems with German default locale Windows settings (comma as decimal separator and point as thousand separator in Windows settings).

MsgBox .Cells(25, 2).Value

returns the value with the format of the Windows locale default.

The Application.DecimalSeparator that you set in your Excel options affects what is displayed in the cell, but not what is displayed by message boxes.

Therefore you can use

MsgBox .Cells(25, 2).Text

which returns the value as text formatted like in the cell.

---

Another workaround is to replace the commas with replace() function:

MsgBox Replace(.Cells(25, 2).Value, ",", ".")

Solution 2

One of the first functions I have created, after I came to Germany was this one:

Public Function ChangeCommas(ByVal myValue As Variant) As String

    Dim temp As String
    temp = CStr(myValue)
    ChangeCommas = Replace(temp, ",", ".")

End Function

Since the last 2 years it is present in every VBA project.