2

I guess it has something to do with the precision but I don't understand it. I have this piece of code that should perform rounding to 2 places (value is the input):

Dim a As Double
Dim b As Double
Dim c As Double
Dim result As Double

a = CDbl(value) * 100
b = a + 0.5 //because Int in the next line performs floor operation, 0.5 is added to ensure proper rounding, example for 123.5: Int(123.5) = 123 but we need Int(123.5 + 0.5) = 124
c = Int(b)
result = c / 100

The thing is, it doesn't work properly for a value 134.605. While debugging I found out that a value is calculated incorrectly. In fact I have checked this:

?13460.5 = (134.605*100)
False
?(134.605*100) - 13460.5
-1,02318153949454E-12

And I'm stuck. I can either rewrite the rounding function differently, but I don't have an idea for it without *100 multiplication. Or I could find out how to make *100 operation work correctly for all the values. Could anyone try to explain me why it happens and suggest a solution?

Erik A
  • 31,639
  • 12
  • 42
  • 67
agnieszka
  • 14,897
  • 30
  • 95
  • 113
  • 1
    This may help http://wiki.lessthandot.com/index.php/Simple_Maths,_Wrong_Answers – Fionnuala Mar 11 '14 at 10:31
  • actually @Remou the links provided by you use the same algorithm I am using, just in a different way, and it has the same error and produces the same faulty reasult – agnieszka Mar 11 '14 at 10:47
  • 1
    You may have noticed that currency data type and decimal data type do not give a faulty result. – Fionnuala Mar 11 '14 at 10:48
  • oh true, I didn't notice that. I was just surprised that the function provided by microsoft in one of the articles produced the faulty result in this case as well. – agnieszka Mar 11 '14 at 10:52
  • You may wish to read http://sqlanywhere.blogspot.ie/2011/01/be-very-afraid-of-floating-point.html – Fionnuala Mar 11 '14 at 10:56

1 Answers1

3

Try Decimal data type. Although you cannot explicitly declare a variable of this type, you can convert it to such implicitly by declaring it as Variant and converting using CDec.

Debug.Print 13460.5 = CDec(134.605 * 100) 'Output - True
Kapol
  • 6,383
  • 3
  • 21
  • 46
  • well it seems that it works, I just need to check some more cases to be 100% sure. But could you explain why? – agnieszka Mar 11 '14 at 10:53
  • Just find any decent article explaining differences between floating points and decimal data types. Doesn't have to be related specifically to VBA. – Kapol Mar 11 '14 at 10:55
  • We have talk about flying cars, re-usable rockets, and sending people to Mars, and this is how we have to divide in VBA? – Ryan Mortensen Aug 11 '19 at 23:21
  • 1
    @RyanMortensen VBA had been created before all of these things :) – Kapol Aug 16 '19 at 14:36