CODEWITHSUNDEEP
vbamathmath-functions
Sami Al-Subhi
Sami Al-Subhi

Reputation: 4672

Function to Remove the Decimal Places

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

Upvotes: 24

Views: 86225

Answers (5)

Mark
Mark

Reputation: 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))

Upvotes: -1

Albi Patozi
Albi Patozi

Reputation: 1468

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

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

and so on.

Upvotes: 37

ashleedawg
ashleedawg

Reputation: 21639

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

- Application.WorksheetFunction.Floor_Math

- Application.WorksheetFunction.Floor

Upvotes: 2

user3849288
user3849288

Reputation: 61

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

CInt(1.6) ~~ 2
Int(1.6) ~~ 1

Upvotes: 6

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

Upvotes: 4

Related Questions