VBA INT (Integer) Function
“INT” stands for “INTEGER” in VBA or Excel. When we work with numbers in excel we may require to convert the number to an integer number and in these cases, we may need to use “INT” function in excel. VBA “INT” function is available with both worksheets as well as in “VBA”. In this dedicated article, we will show you how to use “INT” function in VBA.
“INT” function in excel is categorized as a “Math & Trigonometry” excel function. This function is available with the worksheet as well as in VBA as well. Excel VBA Integer function rounds a specific number down to the nearest integer number.
VBA Excel Integer function can also be used in Sub Procedure and Function Procedure. Now take a look at the formula of INT function in VBA.
VBA INT function has only one argument to supply i.e. Number
Number is the number we are trying to convert to INTEGER number. When the number is supplied INT function rounds down to the nearest integer.
For example, if the supplied number is 68.54 then Excel VBA Integer function rounds down to 68 which is towards zero. If the supplied number is -68.54 then INT function rounded the number to 69 which is away from zero.
Examples of VBA INT Function
Below are a few practical examples of the VBA INT Function.
Now, look at the simple example of “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.
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 with double quotes.
Sub INT_Example1() Dim K As Integer K = Int(84.55) End Sub
Step 5: Now passing the result of the variable in the VBA message box.
Sub INT_Example1() Dim K As Integer K = Int(84.55) MsgBox K End Sub
Ok, let’s run the code and see what happens.
So, the result is 84 and it is rounded down to the nearest integer value.
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 I run this code it has rounded the number to -85.
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 an example look at the below data of Date & Time.
How do you extract Date Portion and Time?
First, we need to Extract Date portion, for this let’s apply VBA INT function. 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 time column apply the date formatting as “HH:MM:SS AM/PM”
You can download this VBA INT Function Excel here – VBA INT Function Excel Template
This has been a guide to VBA INT Function. Here we learn how to use Excel VBA INT (Integer) Function along with practical examples and a downloadable template. Below are some useful excel articles related to VBA –