VBA INT

Excel VBA INT (Integer) Function

VBA INT is an inbuilt function which is used to give us only the integer part of a number provided us an input, this function is used in those data whose decimal part is not so much affecting the data so to ease the calculations or analysis we use INT function to get only the integer values.

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

VBA INT

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

VBA Int function can be used in Sub Procedure and Function Procedure. Now take a look at the syntax of the INT function in VBA.

VBA INT Formula

It has only one argument to supply i.e., Number.

  • The number is the number we are trying to convert to INTEGER number. When the number is supplied, the INT function rounds down it to the nearest integer.

For example, if the supplied number is 68.54, then the Excel VBA Int function rounds down to 68, which is towards zero. If the supplied number is -68.54, then the INT function rounded the number to 69, which is away from zero.

How to use VBA INT Function?

You can download this VBA INT Excel Template here – VBA INT Excel Template

Example #1

Now, look at the simple example of the “INT” function in VBA. Let’s perform the task of round down 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
excel vba integer Example 1

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

Code:

Sub INT_Example1()

  Dim K As Integer

  K = Int(

End Sub
excel vba integer Example 1-1

Step 4: Supply the number as 84.55.

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

Code:

Sub INT_Example1()

  Dim K As Integer

  K = Int(84.55)

End Sub
VBA INT Example 1-2

Step 5: Now passing the result of 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.

Code:

Sub INT_Example1()

  Dim K As Integer

  K = Int(84.55)
  MsgBox K

End Sub
excel vba integer Example 1-3

Ok, let’s run the code and see what happens.

excel vba integer Example 1-4

So, the result is 84, and it is 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.

Code:

Sub INT_Example2()

  Dim k As Integer

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

End Sub

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

VBA INT Example 2

Example #3 – Extract Date Portion from Date & Time

Few times in my career, I have encountered the scenario of extracting the date & time separately from the combination of Date & Time.

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

Extract Date & Time Example 3

How do you extract Date Portion and Time?

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

Code:

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).

Extract Date & Time Example 3-1

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

Date & Time column

Recommended Articles

This has been a guide to VBA INT Function. Here we learn how to use VBA INT Function along with practical examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>