Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management

and troubleshooting tips and tricks

Published on

7,247 Points

This collection of functions covers all the normal rounding methods of just about any numeric value.

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, while the last exists in three varieties - for Currency, Decimal, and Double respectively.

They all accept a specified count of decimals - including a

More than ten years ago,

If you are convinced that

```
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 other functions, a function for rounding to significant figures was added.

It's important to stress, that there is no

It can be useful to list examples that shows the differences between the different rounding methods and how the 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 examples can be found in the two modules in the code with suffix The main function - rounding by 4/5 - goes like this. Please note the in-line comments for details:

```
' Common constants.
'
Public Const Base10 As Double = 10
' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimals.
'
' Rounds to integer if NumDigitsAfterDecimals is zero.
'
' Rounds correctly Value until max/min value limited by a Scaling of 10
' raised to the power of (the number of decimals).
'
' Uses CDec() for correcting bit errors of reals.
'
' Execution time is about 1µs.
'
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
' Only round if Value is numeric and ReturnValue can be different from zero.
If Not IsNumeric(Value) Then
' Nothing to do.
ReturnValue = Null
ElseIf Value = 0 Then
' Nothing to round.
' Return Value as is.
ReturnValue = Value
Else
Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)
If Scaling = 0 Then
' A very large value for Digits has minimized scaling.
' Return Value as is.
ReturnValue = Value
ElseIf MidwayRoundingToEven Then
' Banker's rounding.
If Scaling = 1 Then
ReturnValue = Round(Value)
Else
' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
' Very large values for NumDigitsAfterDecimals can cause an out-of-range error
' when dividing.
On Error Resume Next
ScaledValue = Round(CDec(Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
' Decimal overflow.
' Round Value without conversion to Decimal.
ReturnValue = Round(Value * Scaling) / Scaling
End If
End If
Else
' Standard 4/5 rounding.
' Very large values for NumDigitsAfterDecimals can cause an out-of-range error
' when dividing.
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
' Decimal overflow.
' Round Value without conversion to Decimal.
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
' Rounding failed because values are near one of the boundaries of type Double.
' Return value as is.
ReturnValue = Value
End If
End If
RoundMid = ReturnValue
End Function
```

Using it requires nothing more than importing (or copy/paste) the module ```
RoundedValue = RoundMid(32.675, 2)
```

However, it performs a normal 4/5 by default, and optionally Banker's Rounding.It is supplemented by the rounding up or down functions:

- RoundUp
- RoundDown

These act basically like

Rounding to

```
' Rounds Value to have significant figures as specified with parameter Digits.
'
' Performs no rounding if Digits is zero.
' Rounds to integer if NoDecimals is True.
' Digits can be any value between 1 and 14.
'
' Will accept values until about max/min Value of Double type.
' At extreme values (beyond approx. E+/-300) with significant
' figures of 10 and above, rounding is not 100% perfect due to
' the limited precision of Double.
'
' For rounding of values within the range of type Decimal, use the
' function RoundSignificantDec.
'
' Requires:
' Function Log10.
'
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
' Only round if result can be different from zero.
If (Value = 0 Or Digits <= 0) Then
' Nothing to round.
' Return Value as is.
ReturnValue = Value
Else
' Calculate scaling factor.
Exponent = Int(Log10(Abs(Value))) + 1 - Digits
If NoDecimals = True Then
' No decimals.
If Exponent < 0 Then
Exponent = 0
End If
End If
Scaling = Base10 ^ Exponent
If Scaling = 0 Then
' A very large value for Digits has minimized scaling.
' Return Value as is.
ReturnValue = Value
Else
' Very large values for Digits can cause an out-of-range error when dividing.
On Error Resume Next
ScaledValue = CDec(Value / Scaling)
If Err.Number <> 0 Then
' Return value as is.
ReturnValue = Value
Else
' Perform rounding.
If MidwayRoundingToEven = False Then
' Round away from zero.
Half = CDec(Sgn(Value) / 2)
ReturnValue = CDbl(Fix(ScaledValue + Half)) * Scaling
Else
' Round to even.
ReturnValue = CDbl(Round(ScaledValue)) * Scaling
End If
If Err.Number <> 0 Then
' Rounding failed because values are near one of the boundaries of type Double.
' Return value as is.
ReturnValue = Value
End If
End If
End If
End If
RoundSignificantDbl = ReturnValue
End Function
' Returns Log 10 of Value.
'
Public Function Log10( _
ByVal Value As Double) _
As Double
' No error handling as this should be handled
' outside this function.
'
' Example:
'
' If MyValue > 0 then
' LogMyValue = Log10(MyValue)
' Else
' ' Do something else ...
' End If
Log10 = Log(Value) / Log(Base10)
End Function
```

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

If you wish to study the peculiars of the native

Also, a lot of variations is 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 up to the nearest "bargain price"```
RoundedValue = RoundUp(n) - 0.01
RoundedValue -> 128.99
```

Round to the nearest integer 5:```
RoundedValue = RoundMid(n / 5) * 5
RoundedValue = 130.00
```

The current version can always be found at

The initial version is here: Rounding.zip. This includes a Microsoft Access 2013 project.

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…

- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word

Course of the Month10 days, 4 hours left to enroll

Next Article:A Game of Draw Poker