# 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