This tutorial explains difference between Excel VBA Round Function and Excel worksheet Round Function, and create a custom Excel Round Function.
Excel VBA Round Function and custom Round Function
Excel VBA Round Function is completely different from Excel worksheet Round Function.
For Excel spreadsheet Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.
For Excel VBA Round Function, as well as Access VBA, the Round Function uses “Round to even” logic. Integer rounds up if decimal part >=0.6, round down if <=0.4. For case decimal part exactly =0.5, then round down, otherwise round up(round up to even integer), same logic applies to negative number (See example).
I summarize the Excel VBA round to even logic below.
If decimal part >=0.6 Then Round up ElseIf decimal part <=0.4 Then Round down ElseIf decimal part = 0.5 Then If integer part is even number Then Round down ElseIf integer part is odd number Then Round up End If End If
Round to even logic has other names:
-unbiased rounding
-convergent rounding
-statistician’s rounding
-Dutch rounding
-Gaussian rounding
-odd-even rounding
-bankers’ rounding
-broken rounding
Syntax of Excel VBA Round Function – Round to even logic
Round ( expression, [ decimal_places ] )
If [decimal_places] is omitted, default is 0 (round to integer)
Example of Excel VBA Round Function – Round to even logic
Round([Number],0)
| Number | VBA Round | spreadsheet Round | Check Diff |
| 0.1 | 0 | 0 | |
| 0.2 | 0 | 0 | |
| 0.3 | 0 | 0 | |
| 0.4 | 0 | 0 | |
| 0.5 | 0 | 1 | integer part is even number, round down |
| 0.6 | 1 | 1 | |
| 0.7 | 1 | 1 | |
| 0.8 | 1 | 1 | |
| 0.9 | 1 | 1 | |
| 1 | 1 | 1 | |
| 1.1 | 1 | 1 | |
| 1.2 | 1 | 1 | |
| 1.3 | 1 | 1 | |
| 1.4 | 1 | 1 | |
| 1.5 | 2 | 2 | integer part is odd number, round up |
| 1.6 | 2 | 2 | |
| 1.7 | 2 | 2 | |
| 1.8 | 2 | 2 | |
| 1.9 | 2 | 2 | |
| 2 | 2 | 2 | |
| 2.1 | 2 | 2 | |
| 2.2 | 2 | 2 | |
| 2.3 | 2 | 2 | |
| 2.4 | 2 | 2 | |
| 2.5 | 2 | 3 | integer part is even number, round down |
| 2.6 | 3 | 3 | |
| 2.7 | 3 | 3 | |
| 2.8 | 3 | 3 | |
| 2.9 | 3 | 3 | |
| 3 | 3 | 3 | |
| 3.1 | 3 | 3 | |
| 3.2 | 3 | 3 | |
| 3.3 | 3 | 3 | |
| 3.4 | 3 | 3 | |
| 3.5 | 4 | 4 | integer part is odd number, round up |
| 3.6 | 4 | 4 | |
| 3.7 | 4 | 4 | |
| 3.8 | 4 | 4 | |
| 3.9 | 4 | 4 | |
| 4 | 4 | 4 | |
| 4.1 | 4 | 4 | |
| 4.2 | 4 | 4 | |
| 4.3 | 4 | 4 | |
| 4.4 | 4 | 4 | |
| -4.5 | 4 | 5 | integer part is even number, round down |
| -4.6 | 5 | 5 | |
| -4.7 | 5 | 5 | |
| -4.8 | 5 | 5 | |
| -4.9 | 5 | 5 | |
| -5 | 5 | 5 | |
| -5.1 | 5 | 5 | |
| -5.2 | 5 | 5 | |
| -5.3 | 5 | 5 | |
| -5.4 | 5 | 5 | |
| -5.5 | 6 | 6 | integer part is odd number, round up |
Workaround to apply spreadsheet Round Function
In Excel VBA, you can apply Excel worksheet Round Function in the below two ways
Range("B1").Value = "=Round(A1,0)" OR
Range("B1").Value = Application.WorksheetFunction.Round(Range("A1").Value, 0)
Alternatively, you can create a custom Excel Round Function.
VBA Code of custom Excel Round Function – simulate Excel spreadsheet Round Function
Some custom Round Function you can find on the Internet do not consider cases for negative number , but this one does.
Function wRound(pValue As Double, digit) As Double Dim ExpandedValue Dim IntPart As Long Dim FractionPart As Double Dim negNumber As Boolean If pValue < 0 Then pValue = 0 - pValue negNumber = True End If ExpandedValue = pValue * (10 ^ digit) 'Retrieve integer part of the number IntPart = Int(ExpandedValue) FractionPart = ExpandedValue - IntPart 'Retrieve the fraction part of the number If FractionPart < 0.5 Then result = IntPart / 10 ^ digit ElseIf FractionPart >= 0.5 Then result = (IntPart + 1) / 10 ^ digit End If If negNumber = True Then wRound = 0 - result Else wRound = result End If End Function
Syntax of custom Excel VBA Round Function – simulate Excel Round function
wRound(pValue, digit)
| pValue | the decimal value you want to convert |
| digit | the number of decimal places you want to convert to |
Outbound References
http://www.techonthenet.com/excel/formulas/round_vba.php