How to round a number to a five cents multiple in VB/A

In switzerland, we like rounding monetary numbers to 2 decimal places, with multiples of 5 for the fractional part. This is (probably) because our (physical) money has no “cent” coins, but we have 5 cents coins. That’s commonly called “5 cents” rounding.

Example:

  • 1.6777777 becomes 1.70
  • 1.4444444 becomes 1.45
  • 1.0333333 becomes 1.05
and so on.

Years ago, I was “gifted” with the knowledge of a “magic” trick to achieve that.
And that’s how it was gifted to me:

multiply by 20, cut the integer part, compare to .5 to do the rounding, adding 1 if it equals or overflows .5, and finally divide by 20 for the result.
Pretty simple. It works. However, this may not be a so easy task to do in VB/A as it seems to be.

Here’s my version of this algorithm, in the form of my FiveCt() function.
Note that I do use Double to Currency type conversion in this code, it will also work with Single type values.

You can use this function in Excel or Access (or any VB/A) calculations, as in Access queries for example.

FiveCt()

Round a double precision number to a five cents value.
Double in, Double out.

Notes and references on floating point variable types in VB/A