0

I would like to copy a number from one to another worksheet. The copied number may have more than 2 digits after the decimal. I am using this code:

Dim MyLimit As Single
MyLimit = Round(Worksheets("Sheet1").Range("Y1").Value, 2)
Debug.Print MyLimit
Worksheets("Sheet2").Range("AB2") = MyLimit

Debug.Print is showing 0,93.

The Value of Cell AB2 is showing "0,930000007152557".

How do I get the correct value of 0,93 into the cell AB2?

Ben
  • 73
  • 7
  • 1
    Don't declare it as a float type (`single`). Floats are, by their nature, imprecise. [See here](https://stackoverflow.com/questions/30917438/declare-a-variable-as-decimal) – JNevill Sep 23 '21 at 17:27
  • Thx thats it. It is working with "Dim MyLimit". – Ben Sep 23 '21 at 18:05
  • 1
    `Dim MyLimit` will declare it as `Variant`, Change it to `Dim MyLimit As Double` – Siddharth Rout Sep 23 '21 at 19:38
  • 1
    @SiddharthRout Correct me if I'm wrong, but Double is also a Float type and could very well lead to imprecision once again. I think, in that link I shared, the solution is to declare as a variant and set using `CDec()` to ensure precision. I haven't run into this exact issue with a float in VBA before, so that may not be 100% – JNevill Sep 23 '21 at 20:03
  • Use the round function... https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function – Tragamor Sep 23 '21 at 21:15
  • Set it as a variant and then add a watch on `MyLimit`. See what datatype, vba considers it as :) @JNevill – Siddharth Rout Sep 24 '21 at 04:23

1 Answers1

1

Use Decimal (or Currency):

Dim MyLimit As Variant
    
MyLimit = CDec(Round(Worksheets("Sheet1").Range("Y1").Value, 2))

However, Round is quite buggy and also performs Banker's Rounding which may not be what you want. A simple method to perform normal 4/5 rounding is to use Format:

Dim MyLimit As Variant

MyLimit = CDec(Format(Worksheets("Sheet1").Range("Y1").Value, "0.00"))

For extended and/or extreme rounding, study VBA.Round.

Gustav
  • 53,498
  • 7
  • 29
  • 55