EDUCBA

Home VBA VBA Resources VBA Math & Trig Functions VBA INT
 

VBA INT

Madhuri Thakur
Article byMadhuri Thakur

Updated June 12, 2023

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 that is not a fraction that can be negative, positive, or 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

INT function is grouped under ‘Math & Trigonometry’ functions. The integer function rounds the particular number down to its nearest integer number. If the number is negative, the integer will round the number away concerning zero.

How to Use INT Function in Excel VBA?

Like the Excel function, the Integer function is used by supplying a single argument.

Below is the syntax of Excel VBA INT:

Syntax of INT

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

We define a variable as an integer and apply the function to it.

Dim n As Integer
n = Int (34.98)

Now we will change and round down the supplied number to the nearest given integer.

Examples of Excel VBA INT Function

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

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

Excel VBA INT – Example #1

How does the INT function work with positive numbers?

Let’s see how it works with the INT function if the given number is a positive double or fraction number.

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

VBA INT Example 1-1

Step 2: Since the result will be an integer, we need a variable as an integer to assign the result of the 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

VBA INT Example 1-2

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

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(

End Sub

VBA INT Example 1-3

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 is given to the INt function as an argument.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(34.98)

End Sub

VBA INT Example 1-4

Step 5: We now operate the INT function and store the result in 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

VBA INT Example 1-5

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.

Result of Example 1-6

Check the message inside the message box. The result rounds down the number 34.98 to the nearest integer, 34.

Excel VBA INT – Example #2

How does the INT function work with negative numbers?

Within the same code, let’s try to use a negative number. Let’s analyze the result change when supplying the same number as a negative value.

Follow the below steps to use Excel VBA INT Function:

Step 1: We will change the same code as below, supplying the negative fraction number ‘-34.98’ within the INT function.

Code:

Private Sub integerconversion()

Dim n As Integer
n = Int(-34.98)

End Sub

VBA INT Example 2-1

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

VBA INT Example 2-2

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

Result of Example 2-3

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

When we apply the INT function, we round the number -34.98 to -35. 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 a few dates with time. Here we want to split the data into two different columns.

Example 3-1

Let’s use the INT function to separate the data into different columns, dates, and times. Create a button that can produce the expected result in a single click.

VBA INT Example 3-2

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 the command button, and the code window will appear below.

Code:

Private Sub CommandButton1_Click()

End Sub

VBA INT Example 3-3

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

VBA INT Example 3-4

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

VBA INT Example 3-5

Step 4: Using the INT function, split the date into the 2nd, Where the date only will be split into the 2nd 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

VBA INT Example 3-6

Step 5: By subtracting the INT function result from the first column, get the remaining data into a 3rd 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

VBA INT Example 3-7

Step 6: To get the results correctly, format the cells as below. Set the date and time column in the format you want. If not, the result will display as random numbers or garbage values.

Date Format:

Date Format

Time Format:

Time Format

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

Result of Example 3-10

We will split the data given in the first column 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 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA Long
  2. VBA Sort
  3. Excel VBA Macro
  4. VBA CDEC