why this strange calc error in vba

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

I'm fiddling with a function and I'm encountering something strange - what's
going on?
It seems the decimals are off.

Function MyRound(sngNumber As Single) As Single
Dim iInt As Integer
Dim sngRest As Single
iInt = Int(sngNumber)
sngRest = sngNumber - iInt
MyRound = sngRest
End Function

?MyRound(5.31) = 0.3099999
?MyRound(5.30) = 0.3000002
?MyRound(5.29) = 0.29
?MyRound(5.28) = 0.2800002

/ Jesper
 
I'm fiddling with a function and I'm encountering something strange - what's
going on?
It seems the decimals are off.

Function MyRound(sngNumber As Single) As Single
Dim iInt As Integer
Dim sngRest As Single
iInt = Int(sngNumber)
sngRest = sngNumber - iInt
MyRound = sngRest
End Function

?MyRound(5.31) = 0.3099999
?MyRound(5.30) = 0.3000002
?MyRound(5.29) = 0.29
?MyRound(5.28) = 0.2800002

/ Jesper
Click to expand...

is there something in particular you don't like about the built-in
Round function?
 
Public Sub TestAdd()

Dim MyNumber As Single
Dim i As Integer
For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub


Here is the actual outpput of the above:


1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 additions..already rounding is occurring

and if we add the following line of code to the end of the above:


if MyNumber = 10.1 = True then


msgbox "the number is 10.1"


else
msgbox "the number is something else"
endif


The above will actually produce:


the number is something else

It turns out that when using "real" numbers, the computer standard to store
these numbers is ONLY an approximation of the value. (this is one of the
first things you learn in a computing class!!!).

So, if you don't want rounding issues, then don't use double, or single
"real" numbers.

If you only need 2, or 4 decimal places then use the currency format, as it
is an actual integer, and will not suffer the rounding issue...
 
Jesper said:
I'm fiddling with a function and I'm encountering something strange - what's
going on?
It seems the decimals are off.

Function MyRound(sngNumber As Single) As Single
Dim iInt As Integer
Dim sngRest As Single
iInt = Int(sngNumber)
sngRest = sngNumber - iInt
MyRound = sngRest
End Function

?MyRound(5.31) = 0.3099999
?MyRound(5.30) = 0.3000002
?MyRound(5.29) = 0.29
?MyRound(5.28) = 0.2800002
Click to expand...


Small errors like that are inherent in floating point
numbers, especially Single. You might get closer by using
Double, but there will still be small(er) errors.

If your numbers always have 4 or fewer decimal places, use
the Currency data type.
 
Small errors like that are inherent in floating point
numbers, especially Single. You might get closer by using
Double, but there will still be small(er) errors.
Click to expand...

Okay, thanks everyone.

/ Jesper
 
Back
Top