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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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
3
Views
203
Replies
5
Views
111
Replies
6
Views
165
Replies
1
Views
172
Replies
8
Views
70

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,989
Members
425,321
Latest member
stefanov07

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.

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

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