Floor function in VBA code

[Lupison]

I found Round() but I can't find a Floor() math function within the VBA code side of the excel.

For those not math inclined, Floor(5.65) = 5, whereas Round(5.65) = 6.

 

[erik.van.geit]

Hi,

perhaps some vba-mathexpert will know if it exists
this is my workaround

Sub test()
Dim result As Long

Const i = 5.65

result = Int(i) - 1 * (Int(i) > i)
MsgBox result

End Sub

kind regards,
Erik

 

[Norie]

Why not use Application.WorksheetFunction.Floor?

 

[erik.van.geit]

Why not use Application.WorksheetFunction.Floor?

I thought it was slower, but didn't test
and doesn't work for negative numbers

 

[Norie]

Erik

I don't really know if it would be slower, I can't recall seeing a discussion on the matter anywhere, I'll perhaps have a look and post any interesting links if I find any.

Just seems to me to make sense to use what's available rather than trying to recreate something.

Also I'm not sure exactly how you would go about testing the efficiency - perhaps some sort of loop?

 

[erik.van.geit]

Sub test()
Dim result As Long
Dim starttime As Double
Dim i As Long

starttime = Timer
For i = 1 To 3000000
'result = Int(5.65) - 1 * (Int(5.65) > i)
result = Application.WorksheetFunction.Floor(5.65, 1)
Next i
MsgBox Timer - starttime
End Sub

 

[Norie]

Erik

Just tried this code, based on yours.

Here are the result.

42.4518749999988
1.86906250000175

So it appears you're right about the speed issue.

By the way I reversed the order of the code just to make sure I wasn't seeing things and got a similar result.

Sub test()
Dim result As Long
Dim starttime As Double
Dim i As Long

    starttime = Timer
    For i = 1 To 3000000
        'result = Int(5.65) - 1 * (Int(5.65) > i)
        result = Application.WorksheetFunction.Floor(5.65, 1)
    Next i
    Debug.Print Timer - starttime
    starttime = Timer
    
    For i = 1 To 3000000
        result = Int(5.65) - 1 * (Int(5.65) > i)
        'result = Application.WorksheetFunction.Floor(5.65, 1)
    Next i
    Debug.Print Timer - starttime
End Sub

 

[erik.van.geit]

of course we're talking about 3 million operations
would be about 45 entire columns of data to convert
yeah, why not

 

[tusharm]

Floor(x) is defined as the largest integer not greater than x.

Int(x) gives that result.

I found Round() but I can't find a Floor() math function within the VBA code side of the excel.

For those not math inclined, Floor(5.65) = 5, whereas Round(5.65) = 6.

 

[erik.van.geit]

OK, tusharm,
you were thinking clearly

result = Int(5.65) '''''''''''''''' - 1 * (Int(5.65) > i)
result = Int(5.65) '''''''''''''''' - 0