Why differs the results from the function Int and CInt/CLng depending on the
value in this example?
E.g. see the values 6957 and 6960.

Private Sub CommandButton1_Click()
Dim tal As Double
Dim r As Integer

With ThisWorkbook.Sheets("Blad1")
tal = 69.5
r = 1
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(1, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i

tal = 169.5
r = 13
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(r, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i
End With
End Sub

Value! Int Cint CLng
6951 6951 6951 6951
6952 6952 6952 6952
6953 6953 6953 6953
6954 6954 6954 6954
6955 6955 6955 6955
6956 6956 6956 6956
6957 6956 6957 6957
6958 6958 6958 6958
6959 6959 6959 6959
6960 6959 6960 6960

Value! Int Cint CLng
16951 16951 16951 16951
16952 16952 16952 16952
16953 16953 16953 16953
16954 16954 16954 16954
16955 16955 16955 16955
16956 16956 16956 16956
16957 16957 16957 16957
16958 16958 16958 16958
16959 16959 16959 16959
16960 16960 16960 16960

The Int function and the CInt function are not designed to do the same
thing. The Int function **always** rounds its argument **down** to the next
lower integer value. CInt and CLng perform an **actual** rounding operation,
up or down, around 0.5 (it actually uses Banker's Rounding so numbers ending
exactly in 0.5 rounds to the nearest even integer). So, for a number like
12.9...

Int(12.9) === 12

CInt(12.9) === 13

Rick



"Totte" wrote in message
...
Why differs the results from the function Int and CInt/CLng depending on
the value in this example?
E.g. see the values 6957 and 6960.

Private Sub CommandButton1_Click()
Dim tal As Double
Dim r As Integer

With ThisWorkbook.Sheets("Blad1")
tal = 69.5
r = 1
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(1, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i

tal = 169.5
r = 13
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(r, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i
End With
End Sub

Value! Int Cint CLng
6951 6951 6951 6951
6952 6952 6952 6952
6953 6953 6953 6953
6954 6954 6954 6954
6955 6955 6955 6955
6956 6956 6956 6956
6957 6956 6957 6957
6958 6958 6958 6958
6959 6959 6959 6959
6960 6959 6960 6960

Value! Int Cint CLng
16951 16951 16951 16951
16952 16952 16952 16952
16953 16953 16953 16953
16954 16954 16954 16954
16955 16955 16955 16955
16956 16956 16956 16956
16957 16957 16957 16957
16958 16958 16958 16958
16959 16959 16959 16959
16960 16960 16960 16960

Just to add, calculations you might expect to result as an whole number can
result as +/- a very small decimal. Calculation can also differ depending on
the "type" of numbers used in the calculation. In your example try declaring
i as a Long, vs your As Variant, and you will get different incorrect
results.

Dim i as Long ' or as byte or as Integer
vs
Dim i As Variant


As Rick says the Int function rounds down to the nearest whole number.
Where very small "precision" errors may be expected you can do something
like this

Const df As Double = 0.000000000001

and change
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
to
..Cells(r + i, 2) = Int(100 * (tal + i / 100) + df)

Regards,
Peter T



"Rick Rothstein (MVP - VB)" wrote in
message ...
The Int function and the CInt function are not designed to do the same
thing. The Int function **always** rounds its argument **down** to the
next lower integer value. CInt and CLng perform an **actual** rounding
operation, up or down, around 0.5 (it actually uses Banker's Rounding so
numbers ending exactly in 0.5 rounds to the nearest even integer). So, for
a number like 12.9...

Int(12.9) === 12

CInt(12.9) === 13

Rick



"Totte" wrote in message
...
Why differs the results from the function Int and CInt/CLng depending on
the value in this example?
E.g. see the values 6957 and 6960.

Private Sub CommandButton1_Click()
Dim tal As Double
Dim r As Integer

With ThisWorkbook.Sheets("Blad1")
tal = 69.5
r = 1
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(1, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i

tal = 169.5
r = 13
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(r, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i
End With
End Sub

Value! Int Cint CLng
6951 6951 6951 6951
6952 6952 6952 6952
6953 6953 6953 6953
6954 6954 6954 6954
6955 6955 6955 6955
6956 6956 6956 6956
6957 6956 6957 6957
6958 6958 6958 6958
6959 6959 6959 6959
6960 6959 6960 6960

Value! Int Cint CLng
16951 16951 16951 16951
16952 16952 16952 16952
16953 16953 16953 16953
16954 16954 16954 16954
16955 16955 16955 16955
16956 16956 16956 16956
16957 16957 16957 16957
16958 16958 16958 16958
16959 16959 16959 16959
16960 16960 16960 16960

Just to add, calculations you might expect to result as an whole number
can result as +/- a very small decimal.

And here is a perfect example of this problem. Consider this calculation
(perform it in the Immediate window for simplicity)...

Print 12.565 * 100 + 0.5

As expected, 12.565 * 100 yields 1256.5 and, after adding 0.5 to it, the
expected result of 1257 is printed out. However, slap an Int function call
around it...

Print Int(12.565 * 100 + 0.5)

and, lo-and-behold, it only prints out 1256. What happened? Well, my guess
is that 12.565 cannot be represented exactly in Binary and the nearest
Binary number to it is a number that is ever so slightly less than 12.565
(hidden from us by VBA's use of a guard digit so that the rounded value, for
display, looks correct). Multiply that slightly less number by 100 and then
add 0.5 yields a number that is ever so slightly less than 1257. In the
direct Print example, this ever so slightly smaller number is rounded from
it guard digit to the whole number 1257 we saw originally; however, applying
the Int function to this ever so slightly smaller number simply rounds it
down to the previous lower whole number of 1256. Isn't floating point
arithmetic fun?<g

Rick


Calculation can also differ depending on the "type" of numbers used in the
calculation. In your example try declaring i as a Long, vs your As
Variant, and you will get different incorrect results.

Dim i as Long ' or as byte or as Integer
vs
Dim i As Variant


As Rick says the Int function rounds down to the nearest whole number.
Where very small "precision" errors may be expected you can do something
like this

Const df As Double = 0.000000000001

and change
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
to
.Cells(r + i, 2) = Int(100 * (tal + i / 100) + df)

Regards,
Peter T



"Rick Rothstein (MVP - VB)" wrote in
message ...
The Int function and the CInt function are not designed to do the same
thing. The Int function **always** rounds its argument **down** to the
next lower integer value. CInt and CLng perform an **actual** rounding
operation, up or down, around 0.5 (it actually uses Banker's Rounding so
numbers ending exactly in 0.5 rounds to the nearest even integer). So,
for a number like 12.9...

Int(12.9) === 12

CInt(12.9) === 13

Rick



"Totte" wrote in message
...
Why differs the results from the function Int and CInt/CLng depending on
the value in this example?
E.g. see the values 6957 and 6960.

Private Sub CommandButton1_Click()
Dim tal As Double
Dim r As Integer

With ThisWorkbook.Sheets("Blad1")
tal = 69.5
r = 1
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(1, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i

tal = 169.5
r = 13
For i = 1 To 10
.Cells(r, 1) = "Value!"
.Cells(r + i, 1) = 100 * (tal + i / 100)
.Cells(r, 2) = "Int"
.Cells(r + i, 2) = Int(100 * (tal + i / 100))
.Cells(r, 3) = "Cint"
.Cells(r + i, 3) = CInt(100 * (tal + i / 100))
.Cells(r, 4) = "CLng"
.Cells(r + i, 4) = CInt(100 * (tal + i / 100))
Next i
End With
End Sub

Value! Int Cint CLng
6951 6951 6951 6951
6952 6952 6952 6952
6953 6953 6953 6953
6954 6954 6954 6954
6955 6955 6955 6955
6956 6956 6956 6956
6957 6956 6957 6957
6958 6958 6958 6958
6959 6959 6959 6959
6960 6959 6960 6960

Value! Int Cint CLng
16951 16951 16951 16951
16952 16952 16952 16952
16953 16953 16953 16953
16954 16954 16954 16954
16955 16955 16955 16955
16956 16956 16956 16956
16957 16957 16957 16957
16958 16958 16958 16958
16959 16959 16959 16959
16960 16960 16960 16960

"Rick Rothstein (MVP - VB)" wrote in
message ...
Just to add, calculations you might expect to result as an whole number
can result as +/- a very small decimal.

And here is a perfect example of this problem. Consider this calculation
(perform it in the Immediate window for simplicity)...

Print 12.565 * 100 + 0.5

As expected, 12.565 * 100 yields 1256.5 and, after adding 0.5 to it, the
expected result of 1257 is printed out. However, slap an Int function call
around it...

Print Int(12.565 * 100 + 0.5)

and, lo-and-behold, it only prints out 1256.
<snip
Isn't floating point
arithmetic fun?<g


Another one - not unreasonable to assume if you add 0.1 ten times you'll get
1, afraid not, only 'almost' one which is hopefully near enough !

For i = 1 To 10
d = d + 0.1
Next
Debug.Print d = Int(d), (1 - d) * 1E+12!

Regards,
Peter T

"Peter T" <peter_t@discussions skrev i meddelandet
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
Just to add, calculations you might expect to result as an whole number
can result as +/- a very small decimal.

And here is a perfect example of this problem. Consider this calculation
(perform it in the Immediate window for simplicity)...

Print 12.565 * 100 + 0.5

As expected, 12.565 * 100 yields 1256.5 and, after adding 0.5 to it, the
expected result of 1257 is printed out. However, slap an Int function
call
around it...

Print Int(12.565 * 100 + 0.5)

and, lo-and-behold, it only prints out 1256.
<snip
Isn't floating point
arithmetic fun?<g


Another one - not unreasonable to assume if you add 0.1 ten times you'll
get
1, afraid not, only 'almost' one which is hopefully near enough !

For i = 1 To 10
d = d + 0.1
Next
Debug.Print d = Int(d), (1 - d) * 1E+12!

Regards,
Peter T





Thanks for all! Now I know how to make a "work around" to the "problem".

Totte