As you probably already know, one of the main benefits of using Microsoft Excel is that it allows you to easily perform a wide range of calculations in a very simple way. So, today, we decided to take a closer look at the excel VBA round function.
Discover everything you need to know about rounding numbers.
What Is The Excel VBA Round Function?
Simply put, this function allows you to easily get a number rounded to a specific number of digits. Besides, as a VBA function, you can easily use this function in macro code that is entered through the Microsoft Visual Basic Editor.
One of the things that it is important to keep in mind is that the VBA round function tends to behave in a peculiar way. You should also know that many people also tend to refer to it as bankers rounding.
The VBA Round Function Rules
Notice that this function uses round-to-even logic. This means that when you are rounding numbers that end with a 5, this function will round the expression so that the last digit is an even number.
If this sounds a bit confusing, it really isn’t. But let’s take a look at some practical examples.
Check out 3 ways you round numbers every day.
#1: Imagine that you want to round the number 12.55 using the VBA round function:
In this case, the formula comes like this:
Round(12.55, 1)
Result: 12.6 (rounds up)
#2: Imagine that you now want to use the Excel VBA round down function on the number 12.65:
In this case, the formula comes like this:
Round(12.65, 1)
Result: 12.6 (rounds down)
In these cases, the last digit after rounding is always an even number. So, be sure to only use the ROUND function if this is your desired result.
Understand the rules for fractions when rounding numbers.
The Syntax
If you check the examples we just showed you, it is very easy to understand the syntax of the round function in Microsoft Excel:
Round ( expression, [decimal_places] )
But let’s take a closer look at the parameters:
- expression: This is the numeric expression or the number that you want to round.
- decimal_places: Notice that this argument is optional. Whenever you don’t add anything here, the software will immediately consider that you want to round to an integer. In case you want to be specific, then you just need to add the number of decimal places to round the expression to.
Use our simple calculator to round numbers to the nearest tenth.
Tips To Avoid Bankers Rounding
In case you want to ensure that you avoid bankers rounding, then you need to create your own custom function as follows:
#1: The following function overcomes the bankers Rounding that occurs in the built-in VBA round function to deliver a symmetric numeric rounding:
Public Function StandardRound(pValue As Double, pDecimalPlaces As Integer) As Variant
Dim LValue As String
Dim LPos As Integer
Dim LNumDecimals As Long
Dim LDecimalSymbol As String
Dim QValue As Double
‘ Return an error if the decimal places provided is negative
If pDecimalPlaces < 0 Then
StandardRound = CVErr(2001)
Exit Function
End If
#2: If your country uses a different symbol than the “.” to denote a decimal:
You just need to change the following LDecimalSymbol variable to that character
LDecimalSymbol = “.”
#3: To determine the number of decimal places in the value provided, you should use the length of the value and the position of the decimal symbol:
LValue = CStr(pValue)
LPos = InStr(LValue, LDecimalSymbol)
LNumDecimals = Len(LValue) – LPos