How to Use VBA Int Function in Excel ( 3 Examples)

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.

VBA Int


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

Find Out the Positive Number by Using the VBA Int Function in Excel

  • 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

 

Find Out the Positive Number by Using the VBA Int Function in Excel

  • After inserting the code, we need to run the code to get the positive integer value. For that, go to,

Run → Run Sub/UserForm

Find Out the Positive Number by Using the VBA Int Function in Excel

 

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

Evaluate Negative Number by Using the VBA Int Function in Excel

Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)


Similar Readings:


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.

Separate Date and Time from a Single Cell by Using the VBA Int Function in Excel

  • 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

Separate Date and Time from a Single Cell by Using the VBA Int Function in Excel

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

Separate Date and Time from a Single Cell by Using the VBA Int Function in Excel

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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo