23

What is the JavaScript Math.Floor() equivalent in VBA?. this function should remove all the decimal places and return only an integer.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Sami Al-Subhi
  • 4,406
  • 9
  • 39
  • 66

5 Answers5

36

Of what i remember use the Int() function. ex 

int(2.99) = 2 ; int(2.1)=2

and so on.

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
Albi Patozi
  • 1,182
  • 13
  • 19
  • 2
    Keep in mind that Int(-5.8) will give -6 which might not be what you expect. – Cameron Castillo Sep 22 '15 at 20:21
  • I am using this to test if a double is an integer is `if x = int(x) then 'integer end if` Is there a better way, without having to throw an error? – OfficialBenWhite Dec 08 '16 at 14:49
  • Math.floor(-5.8) in javascript gives also -6, so it is exactly what asked. But if you want "real floor" than you can make -5.8 give -5 by bitwise operation OR 0 (in javascript -5.8|0 ) but i don't know even if it exists in VBA but if it does it will give you -5 – Albi Patozi Dec 12 '16 at 19:39
6

be careful that CInt() actually rounds the number, but Int() doesn't.

CInt(1.6) ~~ 2
Int(1.6) ~~ 1
user3849288
  • 61
  • 1
  • 2
4

It's Round()

Sub Sample()
    Dim dval As Double
    dval = 1.12345

    Debug.Print Round(dval, 0)
End Sub

0 above specifies the number of decimals you want.

EDIT:

Albi Patozi is right. The equivalent of Math.Floor() is int(). I was under the impression that you just wanted to return a number without the decimals. But then I looked up http://www.w3schools.com/jsref/jsref_floor.asp

The floor() method rounds a number DOWNWARDS to the nearest integer, and returns the result.

'~~> JavaScript floor() Method
'var a=Math.floor(0.60);    ~~> 0
'var b=Math.floor(0.40);    ~~> 0
'var c=Math.floor(5);       ~~> 5
'var d=Math.floor(5.1);     ~~> 5
'var e=Math.floor(-5.1);    ~~> -6
'var f=Math.floor(-5.9);    ~~> -6

Sub Sample()
    Dim dval(5) As Double, i As Long

    dval(0) = 0.6: dval(1) = 0.4: dval(2) = 5
    dval(3) = 5.1: dval(4) = -5.1: dval(5) = -5.9

    For i = LBound(dval) To UBound(dval)
        Debug.Print Round(dval(i), 0); " ~~ "; Int(dval(i))
    Next
End Sub

RESULT

ROUND() ~~ INT()

1 ~~ 0

0 ~~ 0

5 ~~ 5

5 ~~ 5

-5 ~~ -6

-6 ~~ -6

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
2

You could also call one of the Excel worksheet functions from VBA:

- Application.WorksheetFunction.Floor_Math

- Application.WorksheetFunction.Floor

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
-1

For positive values, VBA offers Int() and Fix() which are functionally equivalent to Math.floor(). However, for negative values, only Int() is functionally equivalent to Math.floor(). The VBA function Fix() might be what you want if you are only interested in getting the value that is on the left side of the decimal point.

Math.floor(2.8) == 2
Int(2.8) == 2
Fix(2.8) == 2

Math.floor(-2.8) == -3
Int(-2.8) == -3
Fix(-2.8) == -2

Office VBA Reference

Int, Fix Functions

Returns the integer portion of a number.

Syntax

Int(number)

Fix(number)

The required number argument is a Double or any valid numeric expression. If number contains Null, Null is returned.

Remarks

Both Int and Fix remove the fractional part of number and return the resulting integer value.

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

Fix(number) is equivalent to:

Sgn(number) * Int(Abs(number))

Mark
  • 5
  • 2
Mark
  • 1