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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
If you add:

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

it will work as you want it to
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Feb 23, 2007
Messages
109
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
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Note that the Int() function in VBA truncates, whereas the Int() function in Excel rounds
Say what?

CLng rounds, not Int.
 

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,660
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top