EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA INT
Secondary Sidebar
VBA Math & Trig Functions
  • VBA Math & Trig
    • VBA Random Number
    • VBA Number Format
    • VBA Integer
    • VBA MOD
    • VBA IsNumeric
    • VBA Round
    • VBA INT
    • VBA RoundUp
    • VBA Intersect
    • VBA Randomize
    • VBA Square Root

VBA INT

By Madhuri ThakurMadhuri Thakur

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,704 ratings)

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.

Watch our Demo Courses and Videos

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

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:

Syntax of 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.

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?

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

VBA INT Example 1-1

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

VBA INT Example 1-2

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

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 it 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: 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

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

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

Example 3-1

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.

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 on the command button and code window will appear as 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 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 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:

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

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
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more