ROUND and WORKSHEETFUNCTION.ROUND

Question

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.

Answer 1

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)

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