Am I doing something wrong or is the VBA Mod statement actually not working with floating point values like Doubles?
Therefore, I always thought that the VBA Mod operator would work with Doubles based on the VB documentation , but when I tried to find out why my rounding function does not work, I found some unexpected behavior of the mod.
Here is my code:
Public Function RoundUp(num As Double, Optional nearest As Double = 1) RoundUp = ((num \ nearest) - ((num Mod nearest) > 0)) * nearest End Function
RoundUp(12.34) returns 12 instead of 13 , so I went a little deeper and found that:
12.5 Mod 1 returns 0 with a return type of Long, whereas I expected 0.5 with a type of Double.
Conclusion
As @ ckuhn203 points out in his answer , according to the VBA specification,
The module or remainder operator divides the number 1 by number2 (rounding off floating point numbers with integers) and returns only the remainder as a result.
and
Typically, the result data type is a byte, byte, integer, Integer, Long, or Variant containing Long, regardless of whether the result is an integer. Any fractional part Truncated.
For my purposes, I need a floating point module, so I decided to use the following:
Public Function FMod(a As Double, b As Double) As Double FMod = a - Fix(a / b) * b 'http://en.wikipedia.org/wiki/Machine_epsilon 'Unfortunately, this function can only be accurate when `a / b` is outside [-2.22E-16,+2.22E-16] 'Without this correction, FMod(.66, .06) = 5.55111512312578E-17 when it should be 0 If FMod >= -2 ^ -52 And FMod <= 2 ^ -52 Then '+/- 2.22E-16 FMod = 0 End If End Function
Here are some examples:
FMod(12.5, 1) = 0.5 FMod(5.3, 2) = 1.3 FMod(18.5, 4.2) = 1.7
Using this in my rounding function solves my particular problem.