Superior functions for:

• Rounding Currency, Decimal, and Double up, down, by 4/5, or to a specified count of significant figures
• Rounding (and scaling) all items of a series of numbers to have the sum to match a desired rounded total

Also:

• Rounding by the Power of Two (Base 2), up, down, or by 4/5
• Convert and format imperial distance (feet and inches) with high precision

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. Base 10 and Base 2
• Round up, with the option to round negative values away from zero. Base 10 and Base 2
• Round by 4/5, away from zero. Base 10 and Base 2
• Round by 4/5, to even (Banker's Rounding). Base 10
• Round to a count of significant figures. Base 10

The first three functions accept all the numeric data types.
The forth exists in three varieties - for Currency, Decimal, and Double respectively.

All functions accept a specified count of decimals - including a negative count which will round to tens, hundreds, etc. Those with Variant as return type will return Null for incomprehensible input.

Note please, that while VBA.Round is severely buggy, the Excel.WorksheetFunction.Round is not. Also, it performs correct 4/5 rounding like the function here, RoundMid, does (see module/file RoundingMethodsTestExcel.bas).

While the worksheet function conveniently is right at hand, it is, however, about three times slower than RoundMid.

The function RoundSum will round a series of numbers, so the sum of these matches the rounded sum of the unrounded values. Further, if a requested total is passed, the rounded values will be scaled, so the sum of these matches the rounded total. This can be useful when sharing a total equally. In cases where the sum of the rounded values doesn't match the rounded total, the rounded values will be adjusted where the applied error will be the relatively smallest.

This can be particular useful in Excel. See the included demo (function RoundDistribution) for how to fill one range with rounded values from another range.

Also a field of a recordset can be rounded to match either the original total or a new total. See sub RoundRecordSum in module RoundingMethodsDemo.

This will not round by:

1000, 100, 10, 1, 1/10, 1/100, 1/1000, etc. 

as for decimals, but by a power of two:

32, 16, 8, 4, 2, 1, 1/2, 1/4, 1/8, 1/16, 1/32, etc.

again, with extreme precision (down to 2^-21) and including very small or very large numbers (2^-96 to 2⁹⁶).

A practical usage of rounding by the power of two is to convert back and forth between metric and imperial measures:

meters <=> feet, inches, fractions

The functions provided will handle very large and very small values for inches:

from ±7922816299999618530273437599 
to 1/2097152 or the decimal value 0.000000476837158203125

The format of the imperial output covers a very wide range:

• Feet and inches, or inches only
• No fraction for a numerator of zero
• No fraction at all
• Dash or no dash between feet and inches
• Only feet if total of inches is 12 or more
• Zero feet if total of inches is smaller than 12
• No inches if feet are displayed and inches are zero
• No units
• Units spelled out as ft, ft., or foot/feet and in, in., or inch/inches

and many variations hereof.

Files can be imported into an existing VBA project, for example for module RoundingMethods with the command:

LoadFromText acModule, "RoundingMethods", "d:\path\RoundingMethods.bas"

Likewise for the other modules.

Top level documentation generated by MZ-Tools is included for Microsoft Access and Microsoft Excel.

Detailed documentation is in-line.

Articles on the topic can be found here:

Rounding values up, down, by 4/5, or to significant figures

Rounding values up, down, by 4/5, or to significant figures

Round elements of a sum to match a total

Round by the power of two

Convert and format imperial distance (feet and inches) with high precision

If you wish to support my work or need extended support or advice, feel free to: