Table of Contents
In VBA, the Int function is used to round values down to the nearest integer. For example, if you wanted to round the value 2.8 down, you would use the Int function as follows: Int(2.8) which would return the value 2. Additionally, the Fix function can also be used to round values down and is used similarly to the Int function. For example, Fix(2.8) would also return the value 2.
You can use the RoundDown method in VBA to round values down.
This function uses the following basic syntax:
Sub RoundDownValue()
Range("B1") = WorksheetFunction.RoundDown(Range("A1"), 0)
End Sub
This particular example will round down the value in cell A1 to the nearest whole number and display the result in cell B1.
Note that the second argument in the RoundDown method specifies the number of digits to round where:
- -3 rounds down to the nearest thousand
- -2 rounds down to the nearest hundred
- -1 rounds down to the nearest ten
- 0 rounds down to the nearest whole number
- 1 rounds down to the nearest tenth (one decimal place)
- 2 rounds down to the nearest hundredth (two decimal places)
- 3 rounds down to the nearest thousandth (three decimal places)
And so on.
The following examples show how to use the RoundDown method in practice.
Related:
Example 1: Round Down to Nearest Whole Number in VBA
We can create the following macro to round down the value in cell A1 to the nearest whole number and display the result in cell B1:
Sub RoundDownValue()
Range("B1") = WorksheetFunction.RoundDown(Range("A1"), 0)
End Sub
When we run this macro, we receive the following output:
Notice that the value 1,432.78 in cell A1 has been rounded down to the nearest whole number of 1,432 in cell B1.
Example 2: Round Down to Nearest Hundred in VBA
We can create the following macro to round down the value in cell A1 to the nearest hundred and display the result in cell B1:
Sub RoundDownValue()
Range("B1") = WorksheetFunction.RoundDown(Range("A1"), -2)
End Sub
When we run this macro, we receive the following output:
Notice that the value 1,432.78 in cell A1 has been rounded down to the nearest hundred of 1,400 in cell B1.
Example 3: Round Down to Nearest Tenth in VBA
We can create the following macro to round down the value in cell A1 to the nearest tenth (i.e. one decimal place) and display the result in cell B1:
Sub RoundDownValue()
Range("B1") = WorksheetFunction.RoundDown(Range("A1"), 1)
End Sub
When we run this macro, we receive the following output:
Notice that the value 1,432.78 in cell A1 has been rounded down to the nearest tenth of 1,432.7 in cell B1.
Note: You can find the complete documentation for the VBA RoundDown method .