# A Rounding We Will Go

Two kinds of rounding, the VBA language spec vs. the Office VBA implementation, and a drop-in replacement for VBA.Round().

Rounding is a very simple concept in math. Most of us learned it in elementary school. Anything less than 0.5 gets rounded down; anything 0.5 or higher gets rounded up. This is known as **arithmetic rounding**.

### Arithmetic Rounding vs. Banker's Rounding

It turns out arithmetic rounding is not the only rounding game in town. There is another form of rounding, known as **banker's rounding** or, simply, **round-to-even**. This rounding exists to account for arithmetic rounding's tendency to overinflate its estimates.

The problem becomes obvious if you step back and think about it for a minute. The number 1.5 is just as close to 1 as it is to 2. And yet, we always round it *up* to 2. Likewise, the number 2.5 is just as close to 2 as it is to 3. But again, we always round it *up* to 3. Over a large enough sample of numbers, our estimates will be too high if we use arithmetic rounding.

Banker's rounding addresses this issue by saying, "Let's take every other number and round it *down* instead of *up*." So, 1.5 and 2.5 would both round to 2, the closest even number (hence the term, "round to even").

Below is a quick example of the problem of arithmetic rounding and the solution of banker's rounding. I'm adding up the numbers 0.5 plus 1.5 et cetera up to 9.5. The unrounded sum is 50. The arithmetic-rounded sum is 55. Meanwhile, the banker's-rounded sum is exactly 50. This contrived example is pretty close to a worst case, but the difference in the two forms of rounding is striking. Clearly, banker's rounding has its uses.

```
'--== Original Addition ==--
?.5 + 1.5 + 2.5 + 3.5 + 4.5 + 5.5 + 6.5 + 7.5 + 8.5 + 9.5
50
'--== Traditional (Arithmetic) Rounding ==--
? 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10
55
'--== Banker's Rounding (Round to Even) ==--
? 0 + 2 + 2 + 4 + 4 + 6 + 6 + 8 + 8 + 10
50
```

### VBA.Round() - Banker's Rounding

The Round() function is included in the VBA specification as part of the standard library. Interestingly, the VBA specification says nothing about whether this function should be implemented using arithmetic or banker's rounding:

Runtime Semantics§ Returns a number rounded to a specified number of decimal places.

The specification leaves this choice as an implementation detail. The Office VBA reference is clear that it implements Round() using banker's rounding:

NoteThis VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

VBA itself is a programming language *specification*. While most users and developers are only familiar with the Office VBA *implementation*, several other non-Microsoft software companies have implemented VBA (at least partially) into their own applications.

### Arithmetic Rounding in VBA

The Office VBA Round() function uses banker's rounding. We've discussed the advantages of that approach. The one drawback--and it's a big one--is that very few people expect rounding to work that way. So, we often find ourselves in need of a function to perform the more familiar arithmetic rounding.

The following function is a drop-in replacement for the VBA **Round()** function. It uses the **Format()** function to perform the arithmetic rounding. Note that the $ after the function name indicates that the function should return a String variable rather than a Variant.

Since the function matches the name of the original VBA library function, it will override that function anywhere you have a call to **Round()**. You can access the original VBA Round() function by using a fully qualified call, `VBA.Round()`

. Do note, however, that you will not be able to do this from within an *expression*. If you go this route, you may want to use the **BankersRound()** function which is nothing more than a wrapper around the built-in VBA.Round() function.

### Source Code

*The lines that begin with '>>> in the comments are doc tests.*

```
'>>> Round(1.5)
' 2
'>>> Round(2.5)
' 3
'>>> Round(3.125, 2)
' 3.13
Function Round(Number As Variant, _
Optional DigitsAfterDecimal As Long = 0) As Variant
If IsNull(Number) Or IsMissing(Number) Then
Round = Number
Exit Function
End If
Select Case VarType(Number)
Case vbInteger, vbLong, vbByte
Round = Number
#If Win64 Then
Case vbLongLong
Round = Number
#End If
Case vbDouble, vbSingle, vbCurrency, vbDecimal, vbString
Dim NumFormat As String
NumFormat = "#." & String$(DigitsAfterDecimal, "#")
Round = Val(Format$(Number, NumFormat))
'Force the result back into a specific type
' to mimic the VBA.Round() function
Select Case VarType(Number)
Case vbDouble: Round = CDbl(Round)
Case vbSingle: Round = CSng(Round)
Case vbCurrency: Round = CCur(Round)
Case vbDecimal: Round = CDec(Round)
Case vbString: Round = CDbl(Round)
End Select
Case Else
'This is a custom function that is part of my error handling module;
' you can use an Err.Raise statement in its place
Throw "Unsupported variable type: {0}", VarType(Number)
End Select
End Function
'>>> BankersRound(1.5)
' 2
'>>> BankersRound(2.5)
' 2
'>>> BankersRound(3.125, 2)
' 3.12
Function BankersRound(Number As Variant, _
Optional NumDigitsAfterDecimal As Long = 0) As Variant
BankersRound = VBA.Round(Number, NumDigitsAfterDecimal)
End Function
```

*Image by SplitShire from Pixabay*

## Comments

Sign in or become a No Longer Set member to join the conversation.

Just enter your email below to get a log in link.

(This will also subscribe you to my weekly newsletter.)