使用.NumberFormat格式化Excel VBA中的小数位 [英] Use of .NumberFormat to Format Decimal Places in Excel VBA

问题描述

我正在将数据从Excel电子表格插入Word文档中.有一个超过2位小数的值.我正在使用以下代码尝试将其转换为2个小数位并将其粘贴到我的文档中.

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.

 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

该值现在仅显示2个小数位，但编辑栏显示的则更多.

The value now shows only 2 decimal places, but the formula bar is showing a lot more.

和?Selection.Value也显示89.43448051.并将其粘贴到我的文档中.

And ?Selection.Value in the Immediate Window of VBA console is showing 89.43448051 too. And this gets pasted into my doc.

为什么.NumberFormat函数可以将其更改为2个小数位?我需要更改什么?

Why can the .NumberFormat function change it to 2 decimal places? What do I need to change?

推荐答案

.NumberFormat属性设置呈现文本的格式，并且不会更改存储在单元格中的值.您可以使用"Range.Text"获取显示的值:

The .NumberFormat property formats the rendered text and doesn't alter the value stored in the cell. You can use 'Range.Text' to get the displayed value:

Range("A1") = 99.12345
Range("A1").NumberFormat = "0.00"
Debug.Print Range("A1").Text   '  > 99.12
Debug.Print Range("A1").Value  '  > 99.12345

这篇关于使用.NumberFormat格式化Excel VBA中的小数位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！