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

Swiss Money Coins

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.


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.


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