In a recent post we learnt a useful VBA function call Fix function. Today I’m going to explain about very similar function. Name of this new VBA function is Int. Like Fix function this also returns the integer part of the number. But there is a small difference between these two functions. These two functions behave slightly different when dealing with negative numbers. If there is a decimal place in a negative value then Fix function will return the first negative number greater than the value.

Ex -

Fix(-29.1) will return -29

If there is a decimal place in the negative value then Int function will return first negative number less than the value.

Ex -

Int(-29.1) will return -30

However both functions treat positive values in same manner.

Now let’s consider this below subroutine. We can get a clear idea about VBA Int function if we run it.

Sub IntFunctionExample() Dim SampleValues(7) As Double Dim i As Integer SampleValues(0) = 0.32 SampleValues(1) = 5 SampleValues(2) = 7.1 SampleValues(3) = 7.8 SampleValues(4) = 0 SampleValues(5) = -15.1 SampleValues(6) = -15.9 SampleValues(7) = -30 For i = LBound(SampleValues) To UBound(SampleValues) Debug.Print Int(SampleValues(i)) Next i End Sub |

So if we run the above code it will show us below result in immediate window.

0

5

7

7

0

-16

-16

-30

Compare number arguments of the function and return values to understand how Int function works.