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

• Currency [64 bits (8 bytes)]
  Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right.
  This representation provides a range of:
  -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  ?CCur(1/6)
  0.1667

• Double (double-precision floating-point) [64 bits (8 bytes)]
  Variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from:
  -1.79769313486231E308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232E308 for positive values
  ?CDbl(1/6)
  0.166666666666667

• Floats (single or double) VS Currency discussion on stack overflow: “Use the Currency data type to avoid rounding errors when precision is of the utmost importance.”

• VB’s Int() and Fix() functions documentation: