使用 .NumberFormat 在 Excel VBA 中格式化小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36878519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use of .NumberFormat to Format Decimal Places in Excel VBA
提问by Concerned_Citizen
I am inserting data into a Word doc from an Excel spreadsheet. There is a value that has more than 2 decimal places. I am using the following code to try to convert it to 2 decimal places and paste it to my doc.
我正在将数据从 Excel 电子表格插入 Word 文档。存在超过 2 个小数位的值。我正在使用以下代码尝试将其转换为 2 个小数位并将其粘贴到我的文档中。
wdApp.Selection.GoTo what:=-1, Name:="Device_Avail"
''Referring to the total count column in the Device Availability worksheet.
Set devAvailRow = shDevAvail.Range("A:A").Find("Overall Totals:",
After:=Cells(1, 1), searchdirection:=xlPrevious)
''Actual piece to format value to 2 decimal places.
shDevAvail.Cells(devAvailRow.Row, 3).NumberFormat = "0.00"
devAvailPer = shDevAvail.Cells(devAvailRow.Row, 3)
wdApp.Selection.TypeText devAvailPer
The value now shows only 2 decimal places, but the formula bar is showing a lot more.
该值现在只显示 2 个小数位，但公式栏显示的更多。
And ?Selection.Valuein the Immediate Window of VBA console is showing 89.43448051 too. And this gets pasted into my doc.
并且?Selection.Value在 VBA 控制台的即时窗口中也显示 89.43448051。这被粘贴到我的文档中。
Why can the .NumberFormatfunction change it to 2 decimal places? What do I need to change?
为什么.NumberFormat函数可以把它改成小数点后两位？我需要改变什么？
回答by Florent B.
The .NumberFormatproperty formats the rendered text and doesn't alter the value stored in the cell. You can use 'Range.Text' to get the displayed value:
该.NumberFormat属性格式化呈现的文本并且不会改变存储在单元格中的值。您可以使用 'Range.Text' 来获取显示值：
Range("A1") = 99.12345
Range("A1").NumberFormat = "0.00"
Debug.Print Range("A1").Text ' > 99.12
Debug.Print Range("A1").Value ' > 99.12345
回答by Hila DG
Number format only changes the format that you see. The underlying value is VARIANT anyways. Try using ROUND() function in order to make sure you import only 2 decimal places
数字格式只会更改您看到的格式。无论如何，基础值是 VARIANT。尝试使用 ROUND() 函数以确保只导入 2 个小数位
回答by Hila DG
Why can the .NumberFormat function change it to 2 decimal places? What do I need to change?
为什么 .NumberFormat 函数可以将其更改为 2 个小数位？我需要改变什么？
Generally speaking you really do not want to change the precision of the raw underlying value. When you do, the 'lost-a-penny' scenario often comes into play; particularly so when calculating and totalling percentages in taxes, interest rates, wholesale-to-retail markup, etc.
一般来说，你真的不想改变原始底层价值的精度。当你这样做时，“一分钱一分货”的情景经常出现；在计算和合计税收、利率、批发到零售加价等的百分比时尤其如此。
With that said, you can quickly change precision of the underlying value to what is displayed for the entire workbook with the following.
话虽如此，您可以使用以下内容快速将基础值的精度更改为为整个工作簿显示的精度。
ActiveWorkbook.PrecisionAsDisplayed = True
The .PrecisionAsDisplayed propertyis a member of the Workbook Object. As such it can be accessed with the ActiveWorkbook property, ThisWorkbook Propertyor a named workbook in the Workbooks Collection.
所述.PrecisionAsDisplayed属性是成员簿对象。因此，可以使用ActiveWorkbook 属性、ThisWorkbook 属性或Workbooks 集合中的命名工作簿访问它。
Warning!This action cannotbe undone. Once you truncate the decimal precision to the displayed value, you cannot get it back.
警告！此操作无法撤消。一旦将十进制精度截断为显示值，就无法恢复。
This option is also available within Excel Options, Advanced, When calculating this workbook, Set Precision as displayed. Expect a similar warning.
此选项也可在 Excel 选项、高级、计算此工作簿时，将精度设置为显示。期待类似的警告。
