VBA changing decimal to comma automaticaly

问题:

I have an Excel Macro in VBA. Yesterday everything worked fine, this morning VBA is taking a decimal point and changing the point to a comma. That is from 5.1 to 5,1. I am using a German system and have set in Excels 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 backup of the macros are showing the same affect so I know it is nothing I have changed in the code in today's version.

Thanks for the help.

回答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, ",", ".")

回答2:

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

Public Function change_commas(ByVal myValue As Variant) As String

    Dim str_temp As String

    str_temp = CStr(myValue)
    change_commas = Replace(str_temp, ",", ".")

End Function

Since the last 2 years it is present in every VBA project. Although I have changed my style of writing function names and variables, this one has stayed.