## Introduction

In many areas, rounding that accurately follows specific rules are needed - accounting, statistics, insurance, etc.

Unfortunately, the native functions of VBA that can perform rounding are either missing, limited, inaccurate, or buggy, and all address only a single rounding method. The upside is that they are fast, and that may in some situations be important.

However, often precision is mandatory, and with the speed of computers today, a little slower processing will hardly be noticed, indeed not for processing of single values. All the functions presented here run at about 1 µs.

They cover the normal rounding methods:

- Round down, with the option to round negative values towards zero
- Round up, with the option to round negative values away from zero
- Round by 4/5, either
*away from zero *or *to even* (Banker's Rounding) - Round to a count of significant figures

The first three functions accept all the numeric data types. They all accept a specified count of decimals - including a *negative *count which will round to tens, hundreds, etc.

The last exists in three varieties - for `Currency`

, `Decimal`

, and `Double`

respectively.

Those with Variant as return type will return `Null`

for incomprehensible input.

## Background

More than a decade ago, *Donald Lessau *created a site dealing with Visual Basic issues: VBspeed. One of these issues was to create a replacement for `Round `

which was (and still is) buggy, though fast. Also, it could (and still can) only perform Banker's Rounding which may not be what you expect when you look for 4/5 rounding. Several suggestions were put forward, one extremely simple using Format; but `string`

handling, as it is, is not very fast, so other solutions were thought out, all more or less wrapped around the classic rounding method `Int(Value + 0.5)`

. They can all be found here.

If you are convinced that `Round `

is not buggy, just try this simple example:

RoundedValue = Round(32.675, 2)

It will return `32.67`

while both a normal 4/5 rounding as well as Banker's Rounding would return 32.68.

Today, computers are much faster, and while `Round`

is very fast, it will in many cases be preferable with a function that is a bit slower if it on the other hand always returns the expected result.

So - from these old contributions - I've brushed up the old 4/5 rounding function with an option for choosing Banker's Rounding, and added sibling functions for rounding up or down (also with options) with focus on the ability to correctly handle as wide a range of input values as possible. Still, they run at about 1 µs. Finally, for completeness and because it is quite different from the the other functions, a function for rounding to significant figures was added.

It's important to stress that there is no *right *or *wrong *rounding method, thus it makes no sense to argue why *Mid Rounding away from zero* is "better" than *Banker's Rounding*. What's important, however, is to know how each method operates and which side effects this may cause, so you can choose the optimum method for the current task.

## Examples

It can be useful to list examples that shows the differences between the different rounding methods and how they act upon positive as well as negative values. Here are just a few.

Value n 12.344 12.345 12.346 12.354 12.355 12.356
RoundUp(n, 2, False) 12.35 12.35 12.35 12.36 12.36 12.36
RoundUp(n, 2, True) 12.35 12.35 12.35 12.36 12.36 12.36
RoundDown(n, 2, False) 12.34 12.34 12.34 12.35 12.35 12.35
RoundDown(n, 2, True) 12.34 12.34 12.34 12.35 12.35 12.35
RoundMid(n, 2, False) 12.34 12.35 12.35 12.35 12.36 12.36
RoundMid(n, 2, True) 12.34 12.34 12.35 12.35 12.36 12.36
RoundSignificantDec(n, 4, , False) 12.34 12.35 12.35 12.35 12.36 12.36
RoundSignificantDec(n, 4, , True) 12.34 12.34 12.35 12.35 12.36 12.36
Value n -12.344 -12.345 -12.346 -12.354 -12.355 -12.356
RoundUp(n, 2, False) -12.34 -12.34 -12.34 -12.35 -12.35 -12.35
RoundUp(n, 2, True) -12.35 -12.35 -12.35 -12.36 -12.36 -12.36
RoundDown(n, 2, False) -12.35 -12.35 -12.35 -12.36 -12.36 -12.36
RoundDown(n, 2, True) -12.34 -12.34 -12.34 -12.35 -12.35 -12.35
RoundMid(n, 2, False) -12.34 -12.35 -12.35 -12.35 -12.36 -12.36
RoundMid(n, 2, True) -12.34 -12.34 -12.35 -12.35 -12.36 -12.36
RoundSignificantDec(n, 4, , False) -12.34 -12.35 -12.35 -12.35 -12.36 -12.36
RoundSignificantDec(n, 4, , True) -12.34 -12.34 -12.35 -12.35 -12.36 -12.36

More can be found in the two modules in the code with suffix `Test`

.

## Using the Code

#### Normal Rounding

The main function - rounding by 4/5 - goes like this. Please note the in-line comments for details:

Public Const Base10 As Double = 10
Public Function RoundMid( _
ByVal Value As Variant, _
Optional ByVal NumDigitsAfterDecimals As Long, _
Optional ByVal MidwayRoundingToEven As Boolean) _
As Variant
Dim Scaling As Variant
Dim Half As Variant
Dim ScaledValue As Variant
Dim ReturnValue As Variant
If Not IsNumeric(Value) Then
ReturnValue = Null
ElseIf Value = 0 Then
ReturnValue = Value
Else
Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)
If Scaling = 0 Then
ReturnValue = Value
ElseIf MidwayRoundingToEven Then
If Scaling = 1 Then
ReturnValue = Round(Value)
Else
On Error Resume Next
ScaledValue = Round(CDec(Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
ReturnValue = Round(Value * Scaling) / Scaling
End If
End If
Else
On Error Resume Next
Half = CDec(0.5)
If Value > 0 Then
ScaledValue = Int(CDec(Value) * Scaling + Half)
Else
ScaledValue = -Int(-CDec(Value) * Scaling + Half)
End If
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
Half = CDbl(0.5)
If Value > 0 Then
ScaledValue = Int(Value * Scaling + Half)
Else
ScaledValue = -Int(-Value * Scaling + Half)
End If
ReturnValue = ScaledValue / Scaling
End If
End If
If Err.Number <> 0 Then
ReturnValue = Value
End If
End If
RoundMid = ReturnValue
End Function

To perform the rounding, the value to be rounded is scaled up or down by a power of ten determined by the required number of decimals, converted to `Decimal`

to avoid bit errors, and an integer rounding is done. If this fails due to the limited range of `Decimal`

, it falls back using `Double`

. Finally, the value is scaled back and returned.

Using it requires nothing more than importing (or copy/paste) the module `RoundingMethods`

included in the zip into your project. Then the functions can be used in a similar way that you would use `Round`

:

RoundedValue = RoundMid(32.675, 2)

However, by default, it performs a normal 4/5 and optionally Banker's Rounding.

It is supplemented by the rounding up or down functions:

These act basically like *-Int(-n)* or *Int(n)* but also feature an option for rounding *away *from zero or *towards *zero respectively (see the example results above).

#### Rounding to Significant Figures

Rounding to significant figures is somewhat different, though scaling and rounding still is an essential part:

Public Function RoundSignificantDbl( _
ByVal Value As Double, _
ByVal Digits As Integer, _
Optional ByVal NoDecimals As Boolean, _
Optional ByVal MidwayRoundingToEven As Boolean) _
As Double
Dim Exponent As Double
Dim Scaling As Double
Dim Half As Variant
Dim ScaledValue As Variant
Dim ReturnValue As Double
If (Value = 0 Or Digits <= 0) Then
ReturnValue = Value
Else
Exponent = Int(Log10(Abs(Value))) + 1 - Digits
If NoDecimals = True Then
If Exponent < 0 Then
Exponent = 0
End If
End If
Scaling = Base10 ^ Exponent
If Scaling = 0 Then
ReturnValue = Value
Else
On Error Resume Next
ScaledValue = CDec(Value / Scaling)
If Err.Number <> 0 Then
ReturnValue = Value
Else
If MidwayRoundingToEven = False Then
Half = CDec(Sgn(Value) / 2)
ReturnValue = CDbl(Fix(ScaledValue + Half)) * Scaling
Else
ReturnValue = CDbl(Round(ScaledValue)) * Scaling
End If
If Err.Number <> 0 Then
ReturnValue = Value
End If
End If
End If
End If
RoundSignificantDbl = ReturnValue
End Function
Public Function Log10( _
ByVal Value As Double) _
As Double
Log10 = Log(Value) / Log(Base10)
End Function

To perform the rounding, the value to be rounded is scaled up or down by a power of ten determined by the `logarithm10`

of the value, converted to `Decimal`

to avoid bit errors, and an integer rounding is done. If this fails due to the limited range of `Decimal`

, it falls back using `Double`

. Finally, the value is scaled back and returned.

This is an example with one value scaled and rounded to four significant figures:

Input value: 0.030675 - Rounded value: 0.03068
Input value: 0.30675 - Rounded value: 0.3068
Input value: 3.0675 - Rounded value: 3.068
Input value: 30.675 - Rounded value: 30.68
Input value: 306.75 - Rounded value: 306.8
Input value: 3067.5 - Rounded value: 3068
Input value: 30675 - Rounded value: 30680
Input value: 306750 - Rounded value: 306800
Input value: 3067500 - Rounded value: 3068000
Input value: 30675000 - Rounded value: 30680000

For all functions, note that potential floating point errors are avoided by casting to `Decimal`

with `CDec`

.

The current version can always be found at GitHub.

The attached zip contains code for all the functions, a test module, as well as a *Microsoft Access 2013 *project.

## Variations

A lot of variations are possible using the functions as a base.

For example, given the value `n = 128.19`

:

Round to the nearest *quarter *(`0.25`

):

RoundedValued = RoundMid(n / 0.25) * 0.25
RoundedValued -> 128.25

Round to the nearest *integer *`5`

:

RoundedValue = RoundMid(n / 5) * 5
RoundedValue = 130.00

Round up to the nearest *bargain price*:

RoundedValue = RoundUp(n) - 0.01
RoundedValue -> 128.99

## Points of Interest

If you wish to study the peculiars of the native `Round`

, then study the module `RoundingMethodsTest `

where a lot of values and results can be found. Also, should you wish to modify a function for your specific purpose, as a minimum it should pass the test included in the test module.

## History

Microsoft MVP, Most Valuable Professional, since 2017.

It started with BASIC in the 80s, then moved to PDC Prolog, and then - when Access 1.0 was launched - "back to Basic", or rather Access Basic. Then followed VBA (Visual Basic for Applications) starting with Microsoft Access 95/97 and still going with Access 2019/365.

Still I do some VBA, but since 2008 my main focus has been C#.

Application area is mainly accounting and custom applications related to accounting.