2

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.

Eddy Alleman
  • 1,096
  • 2
  • 10
  • 21
Junyong Kim
  • 279
  • 1
  • 2
  • 10

1 Answers1

3

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

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • As a newbie, I didn't know (1) if `rnd(-1)` is a single, (2) the way `round` handles a single and a double, (3) how to use `cdbl`, and (4) whether `worksheetfunction` alone is enough. Much appreciate this laborious answer. – Junyong Kim Sep 02 '19 at 00:04
  • @JunyongKim Happy to help. FYI, you can change the accepted answer if you want to (no pressure...) – chris neilsen Sep 02 '19 at 00:08
  • Ahh, right. My bad. I stand corrected. Thought it must have been the same issue but this make sense! I can't delete my answer untill OP marks this as answered I'm afraid. + (Btw, the second part of the question doesn't need the apostrophe per se. Format does the trick (for me) while your second option does not work I'm afraid. Op just wanted to have the trailing zero.) – JvdV Sep 02 '19 at 05:17
  • @Jdv re the `"'" &` the OP said (in a comment) _but I need not formats but strings._ For me using `Cells(1, 4) = Format(Cells(1, 2), "0.0000")` Excel converts back to a number - unless you pre-format the cell as Text (sure it _displays_ with trailing `0`'s, but it's a number, not a string). And `"'" & Cells(1, 2).Text` _does_ work for me. Maybe we have some regional settings differences? – chris neilsen Sep 02 '19 at 06:08
  • For me the `Format` function does exactly as explained [here](https://www.techonthenet.com/excel/formulas/format_string.php) and returns a string. Im on a Dutch version of excel 2019 and 2010. Both worked fine and will display the green check mark with the warning that says "The number in this cell is formatted as text or preceded by an apostrophe" as described [here](https://www.ablebits.com/office-addins-blog/2018/07/18/excel-convert-text-to-number/). – JvdV Sep 02 '19 at 06:47
  • See [here](https://imgur.com/a/Gv2eEpP) (code ran without pre-formatted cells). Both options format to text with the excel warning tip that it's a number formatted as text or preceded by an apostrophe. Not sure what causes your excel to behave differently. – JvdV Sep 02 '19 at 06:57