Solved: int() does this weird thing. Can someone explain this to me?

[skyblue955]

I was testing what Int() does to a number. Here is what I have:

sub test()
z = int(3.55 * 100)
application.statusbar = z
end sub

Result: z = 3.54

Then I tried this:

sub test()
x = 3.55 * 100
z = int(x)
application.statusbar = z
end sub

Result: z = 3.55

Can someone explain why is that?

[SamT]

First look in VBA Menu >> Tools >> options to set the editor to find all mistakes and to turn on Option Explicit

The try capitalizing int (Int)

[Aussiebear]

Not sure how you arrive at results 3.54 and 3.55. Since multiplying any two decimal place number by 100 will always create a whole number. The Int function returns the next lowest whole number so

x = Int(3.55*100) returns 355 because the multiplication takes place first ( being inside the brackets)

x = Int(3.55)*100 returns 300 because of the Int function on the 3.55 (returns 3 which is then multiplied by 100)

Which then brings me to the point of using Application.Statusbar. Within the code you provided you are trying to display the formula result in the status bar, which might satisfy you as the user, but as your current code stands you are locking in the result. You need to find a method of clearing the result in the status bar after giving the user a time period to review the result.

[Kenneth Hobs]

AussieBear, I am not sure how you got 355 for the first case. I get 354 though 355 should be the answer.

I use the Immediate window to test code. I prefer the worksheet function for Int().

Option Explicit

Sub test()
Dim z As Double, x As Double
z = Int(3.55 * 100)
Debug.Print z
x = 3.55 * 100
z = Int(x)
Debug.Print z
z = Evaluate("=Int(3.55 * 100)")
Debug.Print z
x = 3.55 * 100
z = Evaluate("=Int(" & x & ")")
Debug.Print z
End Sub

The results are:

354
355
355
355

[snb]

It happens with 3.55, 3.59, 3.65, etc.
So Int() rounds down in certain cases.

[shrivallabha]

VBA Help says,
Int(number)
the number argument is double.

Option Explicit
Sub test()
Dim z As Double, x As Double
z = Int(3.51 * 100000000000000#)
Debug.Print z
z = Int(3.52 * 100000000000000#)
Debug.Print z
z = Int(3.53 * 100000000000000#)
Debug.Print z
z = Int(3.54 * 100000000000000#)
Debug.Print z
z = Int(3.55 * 100000000000000#)
Debug.Print z
z = Int(3.56 * 100000000000000#)
Debug.Print z
z = Int(3.57 * 100000000000000#)
Debug.Print z
z = Int(3.58 * 100000000000000#)
Debug.Print z
z = Int(3.59 * 100000000000000#)
Debug.Print z
z = Int(3.6 * 100000000000000#)
Debug.Print z
End Sub
and the results are:
350999999999999
352000000000000
352999999999999
354000000000000
354999999999999
356000000000000
356999999999999
358000000000000
Since it is using floating point data type for number argument which leaves a bit of inaccuracy in converting data. Please see here [especially 2nd paragraph]:
http://msdn.microsoft.com/en-us/library/office/aa164573%28v=office.10%29.aspx
Somehow, all odd number entries are not handled properly due to this.

Edit: If you look at first 3 digits then it should explain rounding down.

[jolivanes]

@shrivallabha
Why would I get this?


Sub Try_This()
Dim j As Double, i As Single
j = 3.51
For i = 1 To 10
Cells(i, 2).Formula = "=Int(" & j & "*100000000000000)"
j = j + 0.01
Next i
End Sub


Result:
351000000000000
352000000000000
353000000000000
354000000000000
355000000000000
356000000000000
357000000000000
358000000000000
359000000000000
360000000000000

[shrivallabha]

@shrivallabha
Why would I get this?


Sub Try_This()
Dim j As Double, i As Single
j = 3.51
For i = 1 To 10
Cells(i, 2).Formula = "=Int(" & j & "*100000000000000)"
j = j + 0.01
Next i
End Sub

Result:
351000000000000
352000000000000
353000000000000
354000000000000
355000000000000
356000000000000
357000000000000
358000000000000
359000000000000
360000000000000
Please read Kenneth Hobs' last line. "VBA Int" and "worksheet function Int" are not exactly the same.

[Aussiebear]

I simply tested it in the worksheet.

[Kenneth Hobs]

I suspect it is the way that Microsoft wrote the function. They probably referenced the input as ByRef rather than ByVal.

You can force evaluation by using Evaluate. []'s is the same as Evaluate.

This returns 355 as one expects.
z = Int([3.55 * 100])

[skyblue955]

Thanks. That solved it. And thanks everybody for your contribution.