VBA将小数点自动更改为逗号 [英] VBA changing decimal to comma automaticaly
问题描述
例如,我有一个值用于每天的scrum会议Excel表设置为3.75
Excel工作表输入:
当我使用一个函数读取值如:
Sub TestFunction()
MsgBox(Sheets(Input_Parameters)。Cells(25,2))
End Sub
我得到的值为逗号。
VBA函数输出:
VBA环境中有一个设置来解决这个问题,还是有一些我错过的东西。所有我的宏的备份显示相同的影响,所以我知道在今天的版本的代码中没有改变。
感谢您的帮助。 >
这是德国系统的正常行为,具有德语默认语言环境Windows设置(逗号作为小数分隔符,在Windows设置中指向为千位分隔符)
MsgBox .Cells(25,2).Value
返回具有Windows区域设置默认格式的值。
应用程序。您在Excel选项中设置的DecimalSeparator
会影响单元格中显示的内容,但不会显示消息框。
因此,您可以使用
MsgBox .Cells(25,2).Text
$ b
另一个解决办法是用<$ c $替换逗号c> replace() function:
MsgBox Replace(.Cells(25,2).Value ,,,。)
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.
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, ",", ".")
这篇关于VBA将小数点自动更改为逗号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!