How Excel VBA rounds Doubles to Integers?

Question

I am trying to understand the type of errors that could happen when a wrong type of variable is declared in VBA.

This is the code I am using:

Sub testTypes()

Dim test1 As Integer
test1 = 0.5

Debug.Print test1

End Sub

I tried to use Double number types on purpose to see how VBA will round them (up or down) to make them an Integer, given that the number ends on .5

I got puzzling results:

5.567 --> 6
5.5 --> 6
4.5 --> 4
3.5 --> 4
2.5 --> 2
1.5 --> 2
0.5 --> 0

Could anyone explain how Excel determines whether it will round up or down?

It rounds towards the even number. It is called "Banker's Rounding". — Scott Craner, Oct 20 '17 at 14:11
@Scott never knew that, thanks for the tip! @Sandra if you want to force rounding up or down, you can use `WorksheetFunction.RoundUp(test1, 0)` (or `RoundDown` with the same syntax). — barvobot, Oct 20 '17 at 14:16
Hey @ScottCraner thanks for naming the problem! I think this definition I found makes it even more clear: "It rounds up when the digit before the 5 is odd. If it's even it rounds down. Example 1.1235 rounds to 1.124 1.1225 rounds to 1.122 — Sandra, Oct 21 '17 at 00:56
It was an IEEE 754 at play here. Check the answer by Ostemar in [Why does .NET use banker's rounding as default?](https://stackoverflow.com/questions/311696/why-does-net-use-bankers-rounding-as-default). Also this [MS article](https://blogs.msdn.microsoft.com/ericlippert/2003/09/26/bankers-rounding/) back in 2003! Imagine the sum of a billion of these round off errors can have to actual sum without this standard! — PatricK, Nov 26 '17 at 23:27

T.M. · Answer 1 · 2017-12-31T12:35:54.560

In order to avoid so called banker's rounding (= midpoint value 5 always rounds to the nearest even number ) you can use

(1) WorkSheetFunction.Round
(2) a user defined function.

Banker's rounding is a standard form of rounding used in financial and statistical operations in order to minimize significant rounding errors over multiple rounding operations by consistently rounding midpoint values in a single direction.

(1) Example using the WorksheetFunction Round()

Sub RoundWithWorkSheetFunction()
' Purpose: avoid so called bankers' rounding in VBA (5 always rounds even)
With WorksheetFunction
    Debug.Print "WorksheetFunction.Round(3.5, 0)=" & .Round(3.5, 0), ":| VBA rounds to " & Round(3.5, 0)
    Debug.Print "WorksheetFunction.Round(4.5, 0)=" & .Round(4.5, 0), ":| VBA rounds to " & Round(4.5, 0)
End With

End Sub

(2) An alternative to the worksheet function (avoiding bankers' rounding):

Function roundIt(ByVal d As Double, ByVal nDigits As Integer) As Double
' Purpose: avoid so called bankers' rounding in VBA (5 always rounds even)
If nDigits > 0 Then
   ' if continental european colon instead of point separartor
   ' roundIt= val(Replace(Format(d, "0." & String(nDigits, "0")), ",", "."))
     roundIt = Val(Format(d, "0." & String(nDigits, "0")))
Else
   ' if continental european colon instead of point separartor
   ' roundIt =  val(Replace(Format(d / (10 ^ nDigits), "0."), ",", "."))
   roundIt = Val(Format(d / (10 ^ nDigits), "0."))
End If
End Function

score 0 · Answer 2 · answered Dec 30 '17 at 21:37

0

Sandra, it will round up or down depending if it is an even or odd number. If it is an even number, it will be round down. Otherwise, it will round up.

answered Dec 30 '17 at 21:37

codeLearner

86
4
14

How Excel VBA rounds Doubles to Integers?

2 Answers2

Linked