VBA INT

VBA INT

There are different data types used with Excel VBA. According to the data we use different among them. An integer is a number which is not a fraction that can be negative, positive and zero. INT is referred as integer function. This is used to change the data into an integer in excel. This can be used as an excel function as well as VBA function.

INT function is grouped under ‘Math & Trigonometry’ functions. Integer function is used to round the particular number down to its nearest integer number. If the number is negative, then the integer will round the number away with respect to zero.

How to Use INT Function in Excel VBA?

Similar to the excel function Integer function is used by supplying a single argument.

Below is the syntax of Excel VBA INT:

• The number supplied should be a number(Double) or a cell reference.
• When the same is expressed in VBA code.

A variable is defined as an integer and the function is applied to the variable

Dim n As Integer
n = Int (34.98)

Now the supplied number will be changed into and rounded to a number down near to the given integer.

Examples of Excel VBA INT Function

Below are a few practical examples of the VBA INT Function in Excel.

Excel VBA INT – Example #1

How does the INT function work with positive numbers?

If the given number is a positive double number or fraction number, let’s see how it will be work while used with INT function.

Follow the below steps to use Excel VBA INT Function:

Step 1: Open the code window. To start the operation create a private function ‘integerconversion’ where the entire code will be return.

Code:

Private Sub integerconversion()

End Sub

Step 2: Since the result will be an integer we need a variable as integer to assign the result of INT function. Declare a variable within the function ‘n’ as an integer to assign the INT function to this variable.

Code:

Private Sub integerconversion()

Dim n As Integer

End Sub

Step 3: Once the variable is created assign the integer function formula to the created variable ‘n’. Now while operating the function the result will be stored into the integer variable.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(

End Sub

Step 4: A positive fraction number is supplied to the INT function to see how it will be rounded to its nearest integer. ’34.98’ is the number used and it is given to the INt function as an argument.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(34.98)

End Sub

Step 5: Now the INT function is operated and the result will be stored within the declared variable ‘n’. To see the result pass the variable into a message box, which will print the variable value in a message box.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(34.98)
MsgBox n

End Sub

Step 6: The code is completed now press F5 or run the code using the run button within the code window to get the final result. It will show the output in a message box.

Check the message inside the message box. The result is rounded to an integer 34 from 34.98 to the integer down near to 34.98

Excel VBA INT – Example #2

How does the INT function work with negative numbers?

Within the same code lets try to use a negative number. If the same number is supplied as negative, let’s see how the result will change

Follow the below steps to use Excel VBA INT Function:

Step 1: Here the same code will be changed as below where the number is supplied as negative fraction number within the INT function. ‘-34.98’ is used with the INt function.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(-34.98)

End Sub

Step 2: Use the message box to print the result to the window.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(-34.98)
MsgBox n

End Sub

Step 3: Run the code below and the result will be changed.

The result will be a negative integer. And is rounded to the near integer away from zero.

Here the number -34.98 is rounded to -35 while applying the INT function on it. The INT function will always round the number down to the next lowest integer.

Excel VBA INT – Example #3

How to Separate date and time from a single cell using VBA INT?

Few dates are mentioned with time in the same cell we want to separate the data into different cells.

The first column contains few dates with time. Here we want to split the data into two different columns.

To separate the data into the different columns date, time let’s use the INT function. Create a button which can produce the expected result in a single click.

Now let’s write the VBA code behind this command button. For this, Follow the below steps to use Excel VBA INT Function:

Step 1: Double click on the command button and code window will appear as below.

Code:

Private Sub CommandButton1_Click()

End Sub

Step 2: Create a variable as an integer to run a For loop to separate the data one by one.

Code:

Private Sub CommandButton1_Click()

Dim i As Integer

End Sub

Step 3: Implement a For loop, since we have 5 rows the loop should work 5 times. Where the row starts from 2.

Code:

Private Sub CommandButton1_Click()

Dim i As Integer
For i = 2 To 6

End Sub

Step 4: Using INT function split the date into the 2^nd Where the date only will be split into the 2^nd column.

Code:

Private Sub CommandButton1_Click()

Dim i As Integer
For i = 2 To 6
  Cells(i, 2).Value = Int(Cells(i, 1).Value)

End Sub

Step 5: By subtracting the INT function result from the first column get the remaining data into a 3^rd cell.

Code:

Private Sub CommandButton1_Click()

Dim i As Integer
For i = 2 To 6
  Cells(i, 2).Value = Int(Cells(i, 1).Value)
  Cells(i, 3).Value = Cells(i, 1).Value - Cells(i, 2).Value
Next

End Sub

Step 6: To get the results in a proper format make the cells formatted as below. The column date and time set into the format which you want. Otherwise, the result will be shown as some random numbers or some garbage value.

Date Format:

Time Format:

Step 7: Run the code by clicking the command button. The output will be as below.

The data given in the first column will be split into two columns date and time.

Things to Remember

• If a number is positive, then the integer function will round it to the integer near to zero, for negative values it will down the number to an integer away from zero.
• VBA INT function will round down the number to an integer value.
• Keep the cells in the format to get a proper result while using the INT function in Excel VBA.

Recommended Articles

This has been a guide to VBA INT Function. Here we discussed how to use Excel VBA INT Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Long
2. VBA Sort
3. VBA Get Cell Value
4. VBA CDEC