The round function is available for rounding numbers with VBA. The operation of the round function is very simple. The number to be rounded off is entered as the first parameter and the number of decimal places to which to round off as the second optional parameter. If the number of decimal places is not specified, it is rounded to an integer. Nothing to worry about so far, but what many people don't know is that the round function uses a fairly unusual rounding mechanism. This can lead to unexpected rounding off.
Banker’s rounding
The round function in VBA uses Banker's rounding. This means that a 5 is always rounded to the nearest even number. For example, 2.35 is rounded to 2.4, but 2.45 is also rounded to 2.4! This last rounding is unknown to many people and is usually even undesirable. It has grown out of history that Microsoft uses Banker's rounding with VBA and normal Visual Basic for the round function. This mechanism is not used with all Microsoft products, because, for example, Excel and SQL Server use different rounding mechanisms.
Adjust rounding 1
In most applications, however, a different rounding is requested. A number ending in 5 must then be rounded up with a positive number and down with a negative number. So 2.35 should become 2.4, 2.45 should become 2.5, and -2.45 should become -2.5. A separate function can of course be written for this, but it is better to continue to use the round function.
What is often used is to add or subtract a very small number from the number to be rounded. This could look like this, for example:
Function RoundHalfUp(dblNumber As Double, _
Optional iDecimal As Byte = 0) As Double
RoundHalfUp = Round(dblNumber + Sgn(dblNumber) * 0.000001, iDecimal)
End Function
0.000001 is added to each number, so 2.45 becomes 2.450001. This number is properly rounded to 2.5. In many cases this works well, but it is not completely waterproof. Firstly, the addition may result in a number that ends in 5. For example, if the base number is 2.349999 and 0.000001 is added to that, then the addition will be exactly 2.35. This is rounded to 2.4, while 2.349999 should be rounded to 2.3. Second, it appears that the addition in VBA can have different outcomes. See the examples below:
Debug.Print Round(2.649999+0.000001, 1)
2.7
Debug.Print Round(2.64999+0.00001, 1)
2.6
Debug.Print Round(2.6499+0.0001, 1)
2.7
Debug.Print Round(2.649+0.001, 1)
2.6
Each separate addition results in 2.65 and according to Banker’s rounding that should be rounded to 2.6, but that is not always the case.. This is most likely due to the inaccuracy that can arise from binary storage of decimal numbers (See for more info about this: Dealing with calculation errors in Excel). In any case, it indicates that the addition method is not reliable in these cases.
Adjust rounding 2
A better method is therefore to do the addition only if the number to be rounded ends in 5. This prevents the above problems. The function that can be used for this is:
Function RoundHalfUp(dblNumber As Double, _
Optional iDecimal As Byte = 0) As Double
If Mid(dblNumber - Fix(dblNumber), iDecimal + 2 + Len(CStr(Sgn(dblNumber))), 1) = "5" Then
dblNumber = dblNumber + Sgn(dblNumber) / 10 ^ (iDecimal + 1)
End If
RoundHalfUp = Round(dblNumber, iDecimal)
End Function
First a check is made whether there is a 5 on the position to be rounded off. If so, a small number is added to a positive number and a small number is subtracted from a negative number. The 5 thus becomes a 6. Then this number is rounded off with the round function. This function can be used to round numbers in the usual way with VBA.
Questions / suggestions
Hopefully this article helped you to round numbers with VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.