In Excel VBA, Int(number) returns the greatest whole-number value less than or equal to the number: for example, Int(7.9) is 7, while Int(-7.9) is -8. It rounds down toward negative infinity, so it differs from Fix, which simply removes the fractional part by rounding toward zero.
What the VBA Int Function Does
Int is a built-in VBA function that takes one numeric expression and returns its integer value after rounding down. Its syntax is Int(number), where number can be a variable, a literal number, or another expression that evaluates to a number. You do not need to enable a reference or install an add-in to use it in an Excel macro.
“Integer” in the function’s name describes the whole-number result; it does not mean that VBA will always store the result in a variable declared with the VBA Integer data type. The variable receiving the result determines how VBA stores it. That distinction matters because VBA’s Integer type only holds values from -32,768 through 32,767. For larger values, use a suitable type such as Long or Double, depending on the range and precision required.
The function also does not round to the nearest whole number. Values are always directed down the number line. Thus 4.99 becomes 4, not 5. For a negative number, moving down means becoming more negative: -4.01 becomes -5.
How Int Rounds Positive and Negative Numbers
For positive values, the result may look like ordinary removal of the decimal part. For negative values, that mental shortcut gives the wrong answer. Imagine a number line: the greatest integer less than or equal to -4.2 is -5, because -5 is below -4.2 and -4 is above it.
These examples show the rule:
Int(6.8)returns 6.Int(6)returns 6; an already whole value stays unchanged.Int(0.4)returns 0.Int(-0.4)returns -1.Int(-6.8)returns -7.Int(-6)returns -6.
A useful test is to ask whether the result is a whole number that is no greater than the original value. For -0.4, zero is greater than -0.4, so zero cannot be the answer; -1 is the correct result. This test helps catch errors when working with negative measurements, balances, or offsets.
Using Int in a VBA Procedure
Call Int directly in a procedure, just as you would call other built-in VBA functions. This example writes results to the Immediate window in the Visual Basic Editor:
Sub ShowIntExamples()
Dim positiveValue As Double
Dim negativeValue As Double
positiveValue = 7.9
negativeValue = -7.9
' Print each value and its rounded-down result.
Debug.Print Int(positiveValue) ' 7
Debug.Print Int(negativeValue) ' -8
End Sub
The Immediate window is useful for checking a calculation without changing worksheet cells. In the Visual Basic Editor, display it with View > Immediate Window. To open the editor on Windows, use Alt+F11 or choose Developer > Visual Basic. On Mac, choose Developer > Visual Basic; Option+F11 is a common shortcut, though some keyboards require Fn as well.
Before running a macro that changes workbook data, save a copy of the workbook. Macro actions cannot be reversed with Ctrl+Z, and closing without saving may be the only simple way to discard unintended changes.
Int Compared with Fix
The most important comparison is Int versus Fix. Both remove the fractional part for positive numbers, so their results match for values such as 7.9. For negative non-integers, they differ: Int(-7.9) returns -8, while Fix(-7.9) returns -7. Fix moves toward zero; Int moves down toward negative infinity.
Choose based on the meaning of the operation, not just on which result seems more familiar:
- Use
Intwhen you need the lower whole-number boundary, such as assigning a value to a floor-based interval. - Use
Fixwhen you need to discard the decimal portion while keeping the result closer to zero. - Use a rounding function when the requirement is to choose the nearest whole number; neither
IntnorFixdoes that.
For instance, if a calculation produces -2.3 and the task is to count complete units in the direction of zero, Fix gives -2. If the task is to find the integer boundary at or below that point, Int gives -3. Define what “down,” “whole units,” or “complete” means in the business rule before choosing.
VBA Int and the Excel Worksheet INT Function
Excel also has a worksheet function named INT with the same rounding-down behavior. It is available directly in worksheet formulas and is distinct from VBA code: a formula is entered in a cell, whereas VBA’s Int is called from a procedure or function. The casing does not change the behavior.
For example, with the value 7.9 in cell A2, enter =INT(A2) in B2; the result is 7. With -7.9 in A2, the same formula in B2 returns -8. The cell layout is A2 containing the input value and B2 containing the formula. This is useful when the calculation should update visibly with worksheet data without running a macro.
VBA can also read a cell’s value, apply its built-in Int function, and write the result back. If the calculation belongs in a worksheet, the formula is usually easier to audit and recalculate. If it is part of a larger automated task, such as preparing data before export, VBA may be a better fit. Avoid assuming that a cell’s displayed format controls the value: formatting 7.9 to show no decimal places does not make the stored value 7.
Practical Uses and Choosing the Right Method
Rounding down is useful when a calculation needs a lower whole-number boundary. For example, if a positive quantity represents how many full groups of 12 fit into a total, the quotient can be passed to Int. With a total of 53 in variable total, the full-group count is 4 because 53 divided by 12 is 4.416…, and the value rounds down to 4. In many cases integer division or a remainder calculation may express the task more clearly, so choose the method that matches the purpose.
Other situations where the distinction can matter include:
- Placing values into intervals defined by lower boundaries.
- Removing a time portion from a positive Excel date-time serial value in a calculation.
- Calculating completed positive units when any partial unit should not count.
- Processing negative coordinates or adjustments, where rounding toward zero would give a different boundary.
For date-time values, Excel stores a date and time together as a serial value, with the time represented by a fractional part. Applying the worksheet INT to a positive date-time serial removes that fractional time portion and leaves the date serial. This is a numeric operation, not a change to the cell’s date format. Check that the value uses Excel’s date system and that the desired outcome is to discard the time rather than round to another date.
Common Problems and Edge Cases
Negative numbers are the most frequent source of surprise. If the requirement says “drop the digits after the decimal point,” a reader may expect -7.9 to become -7. That is Fix behavior, not Int behavior. Test a negative example whenever a macro might process values below zero, even if the positive cases appear correct.
Binary floating-point precision can also affect boundary calculations. Some decimal fractions cannot be represented exactly in binary, so a value intended to be 3 may internally be a very small amount below 3. Applying Int to that stored value can produce 2. If a result is close to an integer boundary, inspect the underlying value or deliberately apply a suitable rounding step based on the task’s rules. Do not rely on the number format to fix the stored value: formatting changes display, not the underlying calculation.
Be careful with nonnumeric inputs. If a variable may contain text, an empty cell, or a worksheet error, validate or handle that input before applying a numeric operation. VBA variants can carry different kinds of values, and invalid data can cause a run-time error rather than a useful integer result. Also consider the destination variable’s range: assigning a valid large result to a too-small VBA Integer variable can cause an overflow.
Frequently Asked Questions
Does VBA Int round up or down?
It rounds down to the greatest integer less than or equal to the input. Positive 3.8 becomes 3, and negative -3.8 becomes -4. It does not round to the nearest integer.
What is the difference between Int and Fix in VBA?
Int rounds toward negative infinity, while Fix truncates toward zero. They agree for positive values but differ for negative values with a fractional part: Int(-3.8) is -4, and Fix(-3.8) is -3.
Does Int convert a value to the VBA Integer data type?
No. It returns a numeric whole-number result; the destination variable’s declared type determines how VBA stores the assignment. Use a type with a suitable range, such as Long or Double, when the possible values exceed the VBA Integer range.
Is VBA Int the same as Excel’s INT worksheet function?
They share the same rounding-down rule. Use VBA’s Int(number) in VBA code and enter a worksheet formula such as =INT(A2) in a cell when the calculation should appear and update on the worksheet.
Can Int remove the time from an Excel date and time?
For a positive Excel date-time serial, applying Int removes the fractional time portion and keeps the date serial. Confirm that discarding the time is appropriate, and format the resulting cell as a date if you want it displayed as one.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Sheet Open VBA: Open and Activate a WorksheetTo open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If…
- VBA Script to Send Email From Excel With OutlookTo send an email from Excel on Windows, use a VBA macro to create an Outlook message, fill…
- VBA Range Address: Get, Set, and Understand ReferencesIn Excel VBA, use a range’s Address property to return its cell reference as text: for example, Range("B2:D5").Address…
- Excel VBA Like Operator: Pattern Matching ExplainedYes. In Excel VBA, the Like operator compares a string with a pattern, letting you test for text…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
- VBA Print Variable in a MsgBox: Syntax and ExamplesTo display a VBA variable in a message box, use MsgBox variableName, or combine a label and the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.