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.

Morphogenesis

What do you mean with "VBA is taking a decimal point and changing the point to a comma."? In VBA you can only use the point as decimal a = 5.3 even in German systems. Can you show your code please (edit the question and add it)? – Outing

I know this is a strange issue. It happens on multiple lines when calling out a cell. For example SpNum = (Sheets("Input_Parameters").Cells(TargVerFirstRow + i, 4)) in the excel cell is 29.29 but in the vba code and out put I get 29,2857.... – Morphogenesis

Please don't use comments to add code use the edit button to edit your question. Add a full example and describe what your code actually does and what you expect it to do. No one can help if you post a single cryptic line of code without any explanation. Also add screenshots to show whats going wrong if this helps you to explain. Your question is very unclear. – Outing

Try MsgBox Sheets("Input_Parameters").Cells(25, 2).Value (without the extra parentheses) – Libido

Didn't work still getting the same result – Morphogenesis

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

Age

This was indeed the issue. Pushing the value out as a string converted the point to comma. In the end the value is write but the comma should be a point. Thanks – Morphogenesis

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

Armilda

Answer 3

I had the same Problem. My MsgBox also printed all cells with a comma instead of a dot. I had changed the settings in Excel etc. My Solution was in the Windows Region Settings. I didnt change the Country (Germany) or Region. But changed the Regional Format to English (United Kingdom). From what I understand. VBA takes the Cells and outputs them into the Windows Format.

I encountered the Problem, when writing everything into an txt File. Every Cell was changed to a , which is just crazy. I know the question is old, but it is still visited by other people.

Greetings from Germany

Lacylad

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Aguish