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.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *