Excel VBA Round Function and custom Round Function

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

 

Excel VBA Round Function and custom Round Function