Search code examples
excelvbarounding

ROUND and WORKSHEETFUNCTION.ROUND

Why are the resulting A1 (0.224) and B1 (0.224000006914139) different?

Sub a()
Cells(1, 1) = Application.WorksheetFunction.Round(Rnd(-1), 4)
Cells(1, 2) = Round(Rnd(-1), 4)
Cells(1, 3) = "=a1=b1"
Cells(1, 4) = "'0.2240"
End Sub

And how can one copy B1 and paste to D1 with four decimal places without losing the trailing zero? Can I alter the last "'0.2240" by something with rnd(-1) or cells(1,2)? Many thanks.

Solution

  • Your issue stems from the fact that Rnd returns a datatype Single. See here

    When Round is passed numdecimalplaces> 0 and a Single value, it honors that data type, and returns a Single. WorksheetFunction.Round does not: it returns a double. (If Round is passed a Double, it returns a Double)

    This does not matter within VBA itself (see image of Watch Window below for evidence)

    The issue occurs when the values are placed in Excel Cells, and the are converted to Excel's cell data type. The conversion of Single incurs Floating point precision issues

    To fix this, your code could be

    Cells(1, 2) = Round(CDbl(Rnd(-1)), 4)

    enter image description here

    To place the result in Cell(1, 4) as text you can use

    Cells(1, 4) = "'" & Format(Cells(1, 2), "0.0000")

    or

    Cells(1, 4) = "'" & Cells(1, 2).Text

    Note: the "'" & is necassary because Excel recognises the string as a number, and "helpfully" converts it back to number