Excel VBA INT (Integer) Function

This function is available with the worksheet as well as in VBA. The Integer function rounds a specific number down to the nearest integer number.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA INT (wallstreetmojo.com)

One can use the VBA Int function in the Sub and Function procedures. Now, look at the syntax of the INT function in VBA.

It has only one argument to supply, Number.

  • The number

  • is the number we are trying to convert to an INTEGER number. When we supply the number, the INT function rounds it down to the nearest Integer.

For example, if the supplied number is 68.54, the Excel VBA INT function rounds down to 68 toward zero. On the other hand, if the supplied number is -68.54, then the INT function rounded the number to 69, away from zero.

How to use VBA INT Function?

Example #1

Look at the simple example of the INT function in VBA. Let us perform the task of rounding the number 84.55 to the nearest integer number.

Step 1: Start the subprocedure.

Step 2: Declare the variable as Integer.

Code:

Sub INT_Example1()

Dim K As Integer

End Sub

Step 3: Now, assign the formula to the variable “k” by applying the INT function.

Sub INT_Example1()

Dim K As Integer

K = Int(

End Sub

Step 4: Supply the number as 84.55.

Note: Since it is a numerical number, we need not supply double quotes.

Sub INT_Example1()

Dim K As Integer

K = Int(84.55)

End Sub

Step 5: Now pass the variable in the VBA message boxVariable In The VBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Sub INT_Example1()

Dim K As Integer

K = Int(84.55) MsgBox K

End Sub

Let us run the code and see what happens.

So, the result is 84 and rounded down to the nearest integer value.

Example #2

Now with the same number with the negative sign, we will try to convert to the nearest integer value. Below is the code with a negative sign.

Sub INT_Example2()

Dim k As Integer

k = Int(-84.55) ‘Negative number with INT function MsgBox k

End Sub

When we run this code, it has rounded the number to -85.

Example #3 – Extract Date Portion from Date & Time

A few times in our careers, we have encountered the scenario of extracting the date and time separately from the combination of Date and Time.

For example, look at the below data of Date and Time.

How do you extract the Date and Time portion?

First, we need to extract the Date portion. For this, let us apply the INT function. The below code will extract the DATE portion from the above data.

Sub INT_Example2()

Dim k As Integer

For k = 2 To 12 Cells(k, 2).Value = Int(Cells(k, 1).Value) ‘This will extract date to the second column

Cells(k, 3).Value = Cells(k, 1).Value - Cells(k, 2).Value
'This will extract time to the third column

Next k

End Sub

Run this code. You might get the result like the below (if the formatting is not applied).

In the date column, apply the formatting as “DD-MMM-YYYY,” and for the time column, apply the date format in excelDate Format In ExcelThe date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.read more as “HH:MM: SS AM/PM.”

This article has been a guide to VBA INT Function. Here, we learn how to use the VBA INT function, practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • Find and Replace Function in VBAVBA CDBL FunctionCopy Paste in VBA1004 Error in VBAVBA Variable Types