VBA Round Vs RoundUp Vs RoundDown
If you are using Excel directly then it has a few functions to round numbers. These are:ROUND ROUNDUP ROUNDDOWN MROUND
Round function in VBA
If you are using Excel directly then it has a few functions to round numbers. These are:
You may learn about Rounding in Excel in detail.
For rounding numbers in Excel via VBA, you may use the Round function – which is the topic of this tutorial.
The Round function in VBA returns a number rounded to the specified number of decimal places.
Syntax:
Round(expression, [ numdecimalplaces ])
Let us show you examples of the usage of this function. First, we will simply display rounded number in the message box within the VBA editor and then with numbers in Excel cells.
A simple example rounding a number
We used the following number to round:
num = 5.5566
We will round it to 2 numbers in the VBA program below:
Sub round_ex()
Dim num
num = 5.5566
MsgBox ("Round Function Returned: " & Round(num, 2))
End Sub
Result:
Note: As MS Office official website says, you should be careful using VBA Round function. It returns something commonly referred to as “Bankers Rounding”.
For predictable results, you may also use the Worksheet Round function (Round, RoundUp, RoundDown). See examples in the coming section.
An example of using VBA Round with Excel sheet
In this example, we used a range of cells in the Round function. For that, we have different values from A2 to A 7 cells.
In VBA code, we used a For Each loop to iterate through each cell and executed the Round function. See the value and rounded number in B cells:
Sub round_ex()
Dim rng As Range
Set rng = Range("A2:A7")
x = 2
For Each cell In rng
Range("B" & x) = Round(cell, 2)
x = x + 1
Next
End Sub
Result:
Comparing with the WorksheetFunction object's round function example
As mentioned earlier, Excel directly has a few functions for rounding.
You may use those functions in VBA by WorksheetFunction object.
In the example below, we are using the same sheet as in the above example. In the output, we will round:
- By VBA Round function
- WorksheetFunction Round
- WorksheetFunction RoundUp
- WorksheetFunction RoundDown
The returned rounded numbers by each function are displayed in B, C, D, and E columns, respectively.
VBA code:
Sub round_ex()
Dim rng As Range
Set rng = Range("A2:A7")
x = 2
For Each cell In rng
Range("B" & x) = Round(cell, 2)
Range("C" & x) = WorksheetFunction.Round(cell, 2)
Range("D" & x) = WorksheetFunction.RoundUp(cell, 2)
Range("E" & x) = WorksheetFunction.RoundDown(cell, 2)
x = x + 1
Next
End Sub
Result:
