Md. Abdur Rahim Rasel
The Int function in Microsoft Excel returns the integer part of a value. The VBA Int is a built-in function in Excel. In Excel, it can be used as a worksheet function (WS) as well as a VBA function (VBA). The VBA Int function is a worksheet function that can be used in a formula in a worksheet cell. You can use this function as a VBA function in macro code written in the Microsoft Visual Basic Editor. Today, In this article, we’ll learn how to use VBA Int Function with three suitable examples in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to the VBA Int Function
▶ Function Objective
To find out the integer number, the VBA Int function is applied.
▶ Syntax
=INT( number)
▶ Arguments Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| number | Required | number (Double) or a cell reference should be provided. |
3 Suitable Examples to Use VBA Int Function in Excel
The VBA Int is a built-in function in Excel. The integer function is used to reduce a number to its smallest integer value. If the number is negative, the integer will round it down to the nearest integer. Here’s an overview of the dataset for today’s task. We will use this dataset to explain the VBA Int function.
1. Find Out the Positive Number by Using the VBA Int Function in Excel
Definitely, finding out the positive integer number by using the VBA Int Function in Excel is the easiest task. In this dataset below we need to find out the positive numbers. To find out the positive integer number, please follow the instructions below.
Step 1:
- First of all, from your Developer Tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic menu, a window named Microsoft Visual Basic Applications will appear in front of you.
Step 2:
- From the Microsoft Visual Basic Applications window, go to,
Insert → Module
- A new module pops up. Now, type the below VBA Code in the window. We have provided the code here, you can just copy-paste the code and use it in your worksheet.
Sub integerconversion()
Dim m As Integer
m = Int(125.25)
MsgBox m
End Sub
- After inserting the code, we need to run the code to get the positive integer value. For that, go to,
Run → Run Sub/UserForm
- While running the code, a new Microsoft Excel window will appear in front of you showing the positive integer value and the positive integer value is 125.
Read More: How to Use the VBA Chr Function (2 Examples)
2. Evaluate Negative Number by Using the VBA Int Function in Excel
Let’s try using a negative integer within the same code. Let’s observe how the result changes if the same number is presented as negative. To evaluate negative integer number, follow the steps below.
Steps:
- Similarly, insert the below code in the Module2. The code is,
Sub integerconversion()
Dim m As Integer
m = Int(-125.25)
MsgBox m
End Sub
- From the Microsoft Visual Basic Applications window, go to,
Run → Run Sub/UserForm
- After running the code a Microsoft Excel window pops up and shows the output as -126.
Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)
Similar Readings:
- How to Use VBA FileDateTime Function in Excel (3 Uses)
- Use VBA TimeSerial in Excel (3 Examples)
- How to Use VBA MkDir Function in Excel (6 Examples)
- How to Use VBA Replace Function in Excel (11 Applications)
- Excel Formula to Generate Random Number (5 examples)
3. Segregate Date and Time from a Single Cell by Using the VBA Int Function in Excel
From our dataset, we want to segregate the data into different cells because a few dates and times are specified in the same cell. In column A, several dates and times are given and we want to separate these data into two columns B and C. Follow the instructions below to learn!
Steps:
- Look at our dataset. Now, we’ll segregate the Date and Time from column A to Date and Time into columns B and C respectively.
- Insert the below code in the Module6,
Sub INT_Example3()
Dim k As Long
For k = 2 To 9
Cells(k, 2).Value = Int(Cells(k, 1).Value)
'This will return date to the second column
Cells(k, 3).Value = Cells(k, 1).Value - Cells(k, 2).Value
'This will return time to the third column
Next k
End Sub
- After that, run the code. To do that, go to,
Run → Run Sub/UserForm
- Finally, you will be able to separate Date and Time into Date and Time from column A to column B and column C which has been given in the below screenshot.
Read More: VBA Date Function (12 Uses of Macros with Examples)
Things to Remember
👉 If the given number is positive, the integer function will round it to an integer close to zero; if the number is negative, the integer function will round it down to an integer away from zero.
👉 The VBA Int Function will convert a real number to an integer number.
👉 When using the VBA INT function in Excel, keep the cells in the format to achieve a valid result.
Conclusion
I hope all of the suitable methods mentioned above to use the VBA Int function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
