How to use Round function in VBA?

In VBA (Visual Basic for Applications), the `Round` function is used to round a number to a specified number of decimal places. The syntax for the `Round` function is as follows:

Round(expression, [numdecimalplaces])

`expression`: The numeric expression to be rounded.
`[numdecimalplaces]`: Optional. Specifies the number of decimal places to which `expression` is rounded. If omitted, the default is 0.

Here is an example of how to use the `Round` function in VBA:

Sub ExampleRound()
    Dim myNumber As Double
    Dim roundedNumber As Double

    ' Assign a value to myNumber
    myNumber = 3.14159

    ' Round the number to two decimal places
    roundedNumber = Round(myNumber, 2)

    ' Print the result in the Immediate Window (press Ctrl+G to view the Immediate Window)
    Debug.Print roundedNumber
End Sub

Run the `ExampleRound` subroutine and you’ll see `3.14` printed in the Immediate Window. This is because `myNumber` is rounded to two decimal places.

Do keep in mind that the `Round` function in VBA uses “Banker’s Rounding” or round to even, which is different from the standard rounding method you might expect. With Banker’s Rounding, when the number being rounded is exactly halfway between two values, the function rounds towards the nearest even number. For example:

Debug.Print Round(2.5, 0) ' Outputs 2, not 3
Debug.Print Round(3.5, 0) ' Outputs 4, not 3

If you want standard rounding (away from zero) in VBA, you’ll often need to write your own function or use an alternative approach like this:

Sub StandardRound()
    Dim numberToRound As Double
    Dim numDecimalPlaces As Integer
    Dim multiplier As Double
    Dim roundedNumber As Double

    numberToRound = 2.5
    numDecimalPlaces = 0
    multiplier = 10 ^ numDecimalPlaces

    ' Standard rounding: Away from zero
    roundedNumber = Int(numberToRound * multiplier + 0.5 * Sgn(numberToRound)) / multiplier

    Debug.Print roundedNumber ' Outputs 3
End Sub

This will round away from zero in all cases, providing the common rounding behavior most people expect.

facebooklinkedin