VBA为什么自动将小数转换为逗号？ [英] Why is VBA changing decimal to comma automatically?

问题描述

我在VBA中有一个Excel宏。昨天一切正常，今天早上VBA将小数点更改为逗号。那是从5.1到5,1。

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.

我使用的是德语系统，并且在Excel的高级选项中设置了点是小数点和逗号是千分之一。

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

例如，我在Excel工作表中将每日Scrum会议的值设置为3.75

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

Excel工作表输入：< br>

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

我用逗号得到值。

VBA函数输出：

VBA function output:

VBA环境中是否有设置可以解决此问题，或者我是否有此设置失踪。我对宏的所有备份都显示相同的影响，所以我知道在当今版本的代码中我没有更改。

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.

推荐答案

在具有德语默认语言环境Windows设置的德国系统上，这是正常现象（逗号在Windows设置中为小数点分隔符，并在Windows设置中指向千分号）。

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

以格式返回值Windows语言环境默认设置。

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

在Excel选项中设置的 Application.DecimalSeparator 会影响在其中显示的内容。

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.

因此，您可以使用

MsgBox .Cells(25, 2).Text

以文本形式返回值

另一种解决方法是将逗号替换为 replace（）函数：

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

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

这篇关于VBA为什么自动将小数转换为逗号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！