12

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?

Concerned_Citizen
  • 6,548
  • 18
  • 57
  • 75
  • 4
    *NumberFormat* does not alter the number itself. It only affects the way the number is visually displayed. (It would be really bad if it *did* modify the values, especially when you were dealing with complex accounting or financial calculations. Imagine the errors that would cause?) – Ken White Apr 27 '16 at 01:35
  • Take a look here https://stackoverflow.com/questions/20648149/what-are-numberformat-options-in-excel-vba/49621979#49621979 – NoWar Apr 03 '18 at 04:13

3 Answers3

20

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
Florent B.
  • 41,537
  • 7
  • 86
  • 101
3

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

Hila DG
  • 688
  • 4
  • 12
2

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.

  • 1
    Since we got rid of the penny here in Canada, does that mean we don't need to worry about the 'lost-a-penny" scenario 8) – Forward Ed Apr 27 '16 at 07:49
  • Actually, since 2 and 7 round down and 3 and 8 round up, it's more like 'lost-a-nickel'.We never lost lost the 'cents' in our prices; just the ability to accurately pay said prices. –  Apr 27 '16 at 12:37