VBA Int() Function

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
If you add:

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

it will work as you want it to
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Note that the Int() function in VBA truncates, whereas the Int() function in Excel rounds
Say what?

CLng rounds, not Int.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top