# VBA Int() Function

#### djt76010

##### Board Regular
Is there a way to make the Int() function in VBA work like the one in Excel? I keep coming across instances where a number that appears to be an integer is rounded down with my VBA code.

Code:
``````Sub Test()

a = (151.2 * 100)
MsgBox Int(a)   'Returns 15119
MsgBox a         'Returns 15120
MsgBox a = 15120    'Returns False

End Sub``````

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### jproffer

##### Well-known Member

Code:
``Dim a as integer 'or long depending on the limit you need``

it will work as you want it to

#### xenou

##### MrExcel MVP
These will also work:
Code:
``````CInt(a)   'Rounds to nearest whole number
Round(a,0)        'Rounds to nearest whole number``````

jproffers idea is best, I think - just dim a as an Integer. I believe the effectively uses the algorithm CInt(a), which rounds to the nearest integer.

Note: I always use Long, though:
Dim a As Long
OR
CLng(a)
Or
Round(a,0)

Long are also integers - they won't crash on you if the value goes above 65,000 and rumor has it that all VBA integers are internally stored as Longs anyway. VBA integers are Short Ints everywhere else in the world.

Last edited:

#### djt76010

##### Board Regular
The code I listed wasn't the actual code I am dealing with, it was just to illustrate the issue that I had come across. I need my code to handle both integers and non-integers and perform calculations with the fractional portion of the number that could usually be isolated through the use of Excel's Int() function. When I was testing my code, the rounding was where I was coming into problems and I was hoping there a simple way to get around this.

#### xenou

##### MrExcel MVP

I think dimming a as a Long Integer is the simple solution then:
Code:
``````Sub Test()
[COLOR="Red"]Dim a As Long[/COLOR]
a = (151.2 * 100)
MsgBox a
End Sub``````
When you use a variable dimmed as an integer type, VBA will round doubles that it stores in this variable - so 15119.9999999999999999 will become 15120 which is the result you want.

Note that the Int() function in VBA truncates, whereas the Int() function in Excel rounds - so they are quite different.

-------------------------------------------------------------------------------------------------
Edit, if the variable must be a variant (to hold float or ints) then:
Code:
``````Sub Test()
a = CLng(151.2 * 100)
MsgBox a
End Sub
'Or:
Sub Test()
a = (151.2 * 100)
MsgBox CLng(a)
End Sub``````

Last edited:

#### shg

##### MrExcel MVP
Note that the Int() function in VBA truncates, whereas the Int() function in Excel rounds
Say what?

CLng rounds, not Int.

#### djt76010

##### Board Regular

I am not sure exactly what the theory is behind this, but for what I am trying to do I would like the functionality of the Int() in Excel.

Code:
``````Sub Test()

a = 15119.9999999999999999
MsgBox Int(a)   'Returns 15119 as desired

End Sub``````
Code:
``````Sub Test()

a = (151.2 * 100)
MsgBox Int(a)   'Returns 15119, but I would like it to be 15120

End Sub``````

#### xenou

##### MrExcel MVP
This will not work:
Code:
``````Sub Test()

a = (151.2 * 100)
MsgBox Int(a)   'Returns 15119, but I would like it to be 15120

End Sub``````

You must do something else.

I suggest dimming the variable a as Long:
Code:
``````Sub Test()
[COLOR="Red"]Dim a As Long[/COLOR]
a = (151.2 * 100)
MsgBox a
End Sub``````
OR use the CLng() Function rather than the Int() function:
Code:
``````Sub Test()
a = (151.2 * 100)
MsgBox [COLOR="Red"]CLng[/COLOR](a)
End Sub``````

The theory behind it is that the vba function Int() truncates while the Excel (Worksheet) function Int() rounds. So they are not at all the same. Floating point arithmetic often gives you numbers that are a very tiny bit less (or more) than their pen and paper counterparts. So 15,119.99999999 might be the result that you are getting which when truncated is 15,119 but when rounded is 15,120.

#### shg

##### MrExcel MVP
the Excel (Worksheet) function Int() rounds
What am I missing, xenou?

<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=214><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 4278" width=94><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 5485" width=120><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 70pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 width=94>Input</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 90pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=120>INT(Input)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32 height=16 align=right>1.99999999999999</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>1.00000000000000</TD></TR></TBODY></TABLE>

#### xenou

##### MrExcel MVP
I see. I stand corrected. Though my proposed solutions is the same, if not for the right reason. It's interesting that Int(151.20 * 100) is 15120 in the worksheet but not in the VBA environment, though both propose to do the same thing (return the integer portion of a number).

Replies
2
Views
65
Replies
11
Views
186
Legacy 456155
L
Replies
11
Views
67
Replies
3
Views
49
Replies
1
Views
51