VBA Int() Function

[djt76010]

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.

Sub Test()

a = (151.2 * 100)
MsgBox Int(a)   'Returns 15119
MsgBox a         'Returns 15120
MsgBox a = 15120    'Returns False

End Sub

 

[shg]

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.

 

[djt76010]

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

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

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]

If you post some concrete examples that your code encounters, we could help you sort it out.

 

[djt76010]

Given the following 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]

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

 

[shg]

Or ...

Function test_fx(var1 As Double, var2 As Double) As Double
   test_fx = Evaluate("int(" & var1 & "*" & var2 & ")")
End Function

 

[djt76010]

I didn't know about the Evaluate function. Very cool