CODEWITHSUNDEEP
vbaexcel
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Use of .NumberFormat to Format Decimal Places in Excel VBA

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

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

enter image description here

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

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

Upvotes: 13

Views: 139371

Answers (3)

user4039065
user4039065

Reputation:

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

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 property is a member of the Workbook Object. As such it can be accessed with the ActiveWorkbook property, ThisWorkbook Property or a named workbook in the Workbooks Collection.

Warning! This action cannot be 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.

Upvotes: 2

Florent B.
Florent B.

Reputation: 42518

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

Upvotes: 21

Hila DG
Hila DG

Reputation: 728

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

Upvotes: 3

Related Questions