Can someone help me to find solution:
Int(1.3 * 100) = 130
Int(1.4 * 100) = 139 (also Int(1.38 to 1.49 * 100 ) )
Int(1.5 * 100) = 150
(VBA:Retail 6.0.874)
Thanks in advace
Hello Grifi,
I don't know the reason but this should solve your problem
Chas
Sub test()
Dim a
Dim b
a = 1.38
b = 100
Debug.Print Int(a * b)
End Sub
That's brilliant.
It's to do with how computers store numbers. There is no binary equivalent to 1.4 only 1 (0001) or 2 (0010) so there is some process involved where the computer approximates a double (1.4) for storing it in memory. This generally works ok if you work like for like (although not always) but if you do some arithmetic and then convert to an integer bits get left off and in this case some funny results occur.
I'll see if I can find you a link that tells you more about it.
I am not sure why, but after some testing. It is pretty interesting.
Anyway, to solve this issue, use CInt instead of Int. The Int() function seems to give this strange result, but CInt is totally fine. Also this problem only happends when you use Int() function. If you just do msgbox cstr(1.4 * 100), it will give you 140.
Try this code to see how things behave.
' explicit double
Dim a1 As Double, a2 As Double
a1 = 1.4
a2 = 100
MsgBox "CStr(a1 * a2) = " + CStr(a1 * a2) + vbNewLine + _
"CStr(Int(a1 * a2)) = " + CStr(Int(a1 * a2)) + vbNewLine + _
"CStr(CInt(a1 * a2)) = " + CStr(CInt(a1 * a2))
' implicit
Dim b1, b2
b1 = 1.4
b2 = 100
MsgBox "CStr(b1 * b2) = " + CStr(b1 * b2) + vbNewLine + _
"CStr(Int(b1 * b2)) = " + CStr(Int(b1 * b2)) + vbNewLine + _
"CStr(CInt(b1 * b2)) = " + CStr(CInt(b1 * b2))
' direct const
MsgBox "CStr(1.4 * 100) = " + CStr(1.4 * 100) + vbNewLine + _
"CStr(CInt(1.4 * 100)) = " + CStr(CInt(1.4 * 100)) + vbNewLine + _
"CStr(Int(1.4 * 100)) = " + CStr(Int(1.4 * 100)) + vbNewLine + _
"CStr(Int(CDbl(1.4 * 100))) = " + CStr(Int(CDbl(1.4 * 100)))
Hi magicalclick, thank you for replay.
It seems CInt() is not "totally fine". Try this:
CInt(1.455 * 100) = 146
CInt(19.455 * 100) = 1945
Any ideas?
You're losing precision during the intermediate calculation; that's why you're getting 1945 instead of 1946. To get the "correct" answer, do the calculation using a Single variable, then convert to Integer. Like this:
Dim n As Integer, s As Single
s = CSng(19.455 * 100)
n = CInt(s)
MsgBox CStr(n)
You'll get the 1946 you were expecting. This isn't a bug or an error -- just a situation where you're expecting more precision in your final result than your input allows.
@o@ theses are some very strange behaviors.
I expect CInt() to neglect any fraction values. Not only it rounded up, but it rounded up inconsistently. Thanks for point that out and the solution to it. This is something I will never expect or understand by myself. Maybe these kinds of issues are not a big deal for VBA professionals, but I consider it as a bug or ill designed function. I remember C++ simply truncate fraction values when converting to int. I like how it behaves, it is really predictable. I would round up number myself if I wanted to. But this automatic inconsistent round up is simply not intuitive. It is kind of useless because I know I can't rely on it. But not only that, I have to deal with this inconsistent round up behavior.
I haven't tried this out in C#. I hope it is more like traditional truncate behavior. It is primitive, but consistent, which is much more important. Hopefully the move VBA to C# era, so, we don't have to deal with these kinds of issues in the future.
For the behaviour of CInt(), I don't see any problem will it. Basically, CInt() is a rounding function. And if you refer to the documentation, you will find that there is a line
When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number.
For the Int() function, if you look at the documentation, there is a line saying The required number argument is a Double or any valid numeric expression. If number contains Null
Pay extra attention that it says the argument is a Double.
If you do the following test, then you will know
Debug.Print Int(CDbl(1.49 * 100)) 'Output 149
Debug.Print Int(1.49 * 100) 'Output 148
The precision problem is because the lost of implicit conversion of the type of the variables.
Hope this will help.
Thank you. I looked it up the reference on those things also. It is pretty interesting. But I am starting to hate VB >o<. Because using those two functions, I will introduce precision problem. Then, I have to patch over patch over another patch.
MsgBox CStr(CInt(Int(1.4 * 100))) + vbNewLine + CStr(Int(1.4 * 100))
MsgBox CStr(CInt(Fix(1.4 * 100))) + vbNewLine + CStr(Fix(1.4 * 100))
All give me 139. In order to turncate a long or double. It seems like I have make suree precision is good first, then Fix(), then use CInt(), OMG. Maybe not Cint() but the example uses it, how weird.
If I am using C++ or Java, I simply do (int)(1.4*100) or do (int)(1.5), and they will give me simple answer, 140 and 1. I don't have to think about rounding, precision, and other other crazy stuff that comes after. Also in some business practice, it rounds up using different threashhold; I have to use my own round up rules. I don't need the build in roundup anyway.
It turns out I can't trust all three functions, CInt(), Int(), Fix() because one rounds up, two have precision issues, and one of them do absolute rounds up on negative. Seriously, why do they design them this way. I have three convertion functions available, and yet, none of it does premitive (int) casting. And I only need that primitive casting. TT
Anyway, thanks for clear everything up because I will never understand it on my own. This actually conflicts with my logic. LOL
After some testing, I think for every implicit type, it is better to do this way.
Fix(CDbl(1.5))
To turncate 1.5 to 1. So basically this is equivelent to C++ (Int)1.5. Yeah, I know this is freakish, but this is the only simple way I know. If anyone knows better solution, please share it Thank you. 
Haven't verify this.
But I think if you declare the variable with proper type should save you from the problem. Try to avoid variant type if you can.
Dim d as double
d = 1.2
Debug.Print Int(d)
I have the code in my first reply. I use explicit all the time. So, only the case when I use constant in the code, like 1.4*100 will give me bad result. I will definitly be careful when I use constant in the code. Always cast it to double first and use Fix to turncate it in the constant formula. 
