title: Mod with Doubles
body: <p><strong>Am I doing something wrong or does the VBA Mod operator actually not work with floating point values like Doubles?</strong></p>
  
  <p>So I've always sort of assumed that the VBA Mod operator would work with Doubles based on the <a href="http://msdn.microsoft.com/en-us/library/se0w9esz.aspx" rel="noreferrer">VB documentation</a>, but in trying to figure out why my rounding function doesn't work, I found some unexpected Mod behavior.  </p>
  
  <p>Here is my code:</p>
  
  <pre><code>Public Function RoundUp(num As Double, Optional nearest As Double = 1)
      RoundUp = ((num \ nearest) - ((num Mod nearest) &gt; 0)) * nearest
  End Function
  </code></pre>
  
  <p><code>RoundUp(12.34)</code> returns <code>12</code> instead of <code>13</code> so I dug a little deeper and found that:</p>
  
  <p><em><code>12.5 Mod 1</code> returns <code>0</code> with the return type of Long, whereas I had expected 0.5 with a type of Double.</em></p>
  
  <hr>
  
  <p><strong>Conclusion</strong></p>
  
  <p>As <a href="https://stackoverflow.com/a/23550864/2832561">@ckuhn203 points out in his answer</a>, according to the VBA specification, </p>
  
  <blockquote>
    <p>The modulus, or remainder, operator divides number1 by number2
    (rounding floating-point numbers to integers) and returns only the
    remainder as result.</p>
  </blockquote>
  
  <p>And</p>
  
  <blockquote>
    <p>Usually, the data type of result is a Byte, Byte variant, Integer,
    Integer variant, Long, or Variant containing a Long, regardless of
    whether or not result is a whole number. Any fractional portion is
    truncated.</p>
  </blockquote>
  
  <p>For my purposes, I need a floating point modulo and so I have decided to use the following:</p>
  
  <pre><code>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 &gt;= -2 ^ -52 And FMod &lt;= 2 ^ -52 Then '+/- 2.22E-16
          FMod = 0
      End If
  End Function
  </code></pre>
  
  <p>Here are some examples:</p>
  
  <p><code>FMod(12.5, 1) = 0.5</code>
  <code>FMod(5.3, 2) = 1.3</code>
  <code>FMod(18.5, 4.2) = 1.7</code></p>
  
  <p>Using this in my rounding function solves my particular issue.</p>