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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Excel does some cosmetic rounding for reasons that escape me, and are not done in vba. I think vba generally runs closer to the hardware.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109
My problem is that I am writing a UDF that performs multiple calculations on input variables and one of the calculations involves the fractional portion of a number. The code
Code:
a = (151.2*100)
does not appear in my UDF, and was just included in my questions to illustrate the problem I was having. The issue with converting the number to a Long is that it involves rounding that works in the case like the one above where that the value of 15120 is represented in VBA by the number 15119.999999999999999 (give or take the number of 9's on the decimal side), but not for other cases where 15119.5 <= 'a' < 15119.999999999999999. I don't know ahead of time what the value of 'a' will be as there are many calculations that take place before this step of the process. If instead my code came across something like
Code:
Sub Test()
Dim a As Long
    a = 15119.6
    MsgBox a
End Sub
I would want 'a' to be 15119, but the Long conversion would result in 15120.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
If you post some concrete examples that your code encounters, we could help you sort it out.
 

djt76010

Board Regular
Joined
Feb 23, 2007
Messages
109
Given the following code
Code:
Function test_fx(Var1, Var2)
    
   Dim a As Long
    
   a = Var1 * Var2
   
   test_fx = a
       
End Function

Var1 = 151.2 & Var2 = 100 returns Test_fx = 15120 as I would like it to.
Var1 = 151.196 & Var2 = 100 returns Test_fx = 15120 where I would like it to return 15119, like in Excel with Int().
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Code:
Function test_fx(var1 As Double, var2 As Double) As Double
   test_fx = Int(1.00000000000001 * var1 * var2)
End Function
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Or ...
Code:
Function test_fx(var1 As Double, var2 As Double) As Double
   test_fx = Evaluate("int(" & var1 & "*" & var2 & ")")
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,108,860
Messages
5,525,241
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top