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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
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,758
Office Version
2010
Platform
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
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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,758
Office Version
2010
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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).
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,015
Messages
5,508,814
Members
408,695
Latest member
MarcelCohen

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top