Mod with Doubles

Question

Am I doing something wrong or does the VBA Mod operator actually not work with floating point values like Doubles?

So I've always sort of assumed that the VBA Mod operator would work with Doubles based on the VB documentation, but in trying to figure out why my rounding function doesn't work, I found some unexpected Mod behavior.

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 dug a little deeper and found that:

12.5 Mod 1 returns 0 with the return type of Long, whereas I had expected 0.5 with a type of Double.

---

Conclusion

As @ckuhn203 points out in his answer, according to the VBA specification,

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result.

And

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.

For my purposes, I need a floating point modulo and so I have 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 issue.

Gameto

See the accepted answer here which might help you. VBA mod operator only accepts long/integer. The accepted answer at the other Q shows the formula used internally for MOD. Perhaps you can adapt it. – Walloper

@DavidZemens Thanks! I had seen that question in my searches, but ignored it because of the mention of a "runtime error" - now I see they actually address the fact that Mod does Longs :P – Gameto

good question, uh maybe it's because double in VBA is actually a floating number therefore any division of a floating number wouldn't really produce good results if multiplied by X thousands. – Nahamas

@mehow Any other suggestions for a more accurate round-to-nearest formula? For the magnitude of numbers I'm working with, 52 significant bits is plenty, but I'd love to make it more general. I suppose I could make a BigNum class that stores (practically) infinite precision numbers as text, but it seemed overkill for what I'm trying to achieve. I once tried to use CopyMemory to trick a Variant into thinking it was a Decimal... but VBA said nope, nope, nope. – Gameto

@Gameto I see, hm let me think about it. I know in 64bit you can use LongLong etc so making a BIGNUM class would not necessarily be a good solution. Maybe trying to use native C# like float.ininity in a COm dll wrapper.. – Nahamas

@Gameto I realize this is six years old, but in case you are still interested I use the following function and because of the greater precision of the Decimal subtype it gives the correct results without having to adjust after the calculation: Function Mod2(n, divisor) Mod2 = CDec(n) - divisor * Int(n / divisor) End Function – Conventionality

Answer 1

According to the VB6/VBA documentation

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. For example, in the following expression, A (result) equals 5. A = 19 Mod 6.7 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. However, if any expression is Null, result is Null. Any expression that is Empty is treated as 0.

Remember, mod returns the remainder of the division. Any integer mod 1 = 0.

debug.print 12 mod 1 
'12/1 = 12 r 0

The real culprit here though is that vba truncates (rounds down) the double to an integer before performing the modulo.

?13 mod 10
 '==>3 
?12.5 mod 10
 '==>2 

debug.print 12.5 mod 1
'vba truncates 12.5 to 12
debug.print 12 mod 1
'==> 0

Handbreadth

However, 1 doesn't divide into 0.5. 12.5 % 1 should be 0.5. I would agree that any integer Mod 1 = 0 – Gameto

@Gameto That's not how math works. x/1 = x. Always. 0.5/1 IS = 0.5 – Handbreadth

That's not quite correct. For a Mod b = c, Integer Modulo assumes that a and b will only ever be integers, so If b = 1 then a = c always. But for Floating Point Modulo, it can be different. For example with 12.5 Mod 1, 1 divides into 12.5 evenly 12 times, with a remainder of 0.5. – Gameto

Here's fmod in C++. Note the examples down near the bottom. fmod(5.3,2) = 1.3 – Gameto

Actually, I should say that is correct, but modulo != division – Gameto

@Gameto That doesn't change the FACT that anything divided by one will not have a remainder. – Handbreadth

I don't want to drag this out anymore, so I'll end with this point: Modulo is about performing integer division and asking about the remaining value. a Mod b = c is "b divides evenly into a x times with a remaining value of c" (x, the quotient, is discarded). Certainly 12.5 / 1 = 12.5, but 12.5 \ 1 = 12 (Integer division) and 12.5 Mod 1 = 0.5. – Gameto

Sorry for being kinda flamey :( and +1 for invoking the actual VBA specification :D – Gameto

@simoco I'm curious as to what you think of my rebuttal on the nature of the modulo operation – Gameto

@ckuhn203 I still disagree with your assertion that anthing Mod 1 = 0, but you have the first and best (VBA Spec, booya!) answer to my actual question of "Does Mod not work with Doubles?", so I'm giving you the checkmark. Thanks for your help! – Gameto

Thanks. I've been doing some research, and it seems to depend on how mod is defined and implemented. I never realized that. It was educational. Thanks for asking the question. – Handbreadth

@Gameto I want to thank you again. You are correct. When doing integer division, 12.5 mod 1 = 0.5 should be true. I'll come back to improve my answer later today to clarify that vba/vb6 seems to have implemented mod in a strange way that was changed/corrected in the .NET framework. – Handbreadth

After poking around the internet, it seems that in many languages the mod operator only works on integer values. A few have separate functions like "fmod" to perform floating point modulus. Fewer still have a mod operator that performs both. Wikipedia has a good list of integer/floating point modulo functions by language – Gameto

let us continue this discussion in chat – Handbreadth

@Blackhawk, as per worksheet functions, you're right: =MOD(12.5,1) returns 0.5 – Tunnell

@Gameto I updated my answer. Does that look better? – Handbreadth

Looks great! Thanks for a very complete answer :) – Gameto

Hey @Blackhawk. I don't know if you chat on SE at all, but I thought you might be interested in this. chat.stackexchange.com/rooms/14929/vba – Handbreadth

Answer 2

I believe that the Mod operator calculates with long type only. The link that you provided is for VB.Net, which is not the same as the VBA you use in MSAccess.

The operator in VBA appears to accept a double type, but simply converts it to a long internally.

This test yielded a result of 1.

9 Mod 4.5

This test yielded a result of 0.

8 Mod 4.5

Toombs

The MS page for Mod in VBA is this learn.microsoft.com/en-us/office/vba/language/reference/… and it doesn't mention limitations in data types, however I can't get it to work with currency numbers (confirming your supposition) – Grantland

Answer 3

As a work around your can do some simple math on the values. To get two decimal of precision just multiply the input values by 100 and then divide the result by 100.

result = (123.45*100 Mod 1*100)/100
result = (12345 Mod 100)/100
result = 0.45

I'm late to the party, but just incase this answer is still helpful to someone.

Acuna

Answer 4

Try This in VBS:

Option Explicit

Call Main()

Sub Main()

    WScript.Echo CStr(Is_Rest_Of_Divide_Equal_To_Zero(506.25, 1.5625))

End Sub


Function Is_Rest_Of_Divide_Equal_To_Zero(Divident, Divisor)

    Dim Result
    Dim DivideResult

    If Divident > Divisor Then
        DivideResult = Round(Divident/Divisor, 0)
        If (DivideResult * Divisor) > Divident Then
            Result = False
        ElseIf (DivideResult * Divisor) = Divident Then
            Result = True
        ElseIf (DivideResult * Divisor) < Divident Then
            Result = False
        End If
    ElseIf Divident = Divisor Then
        Result = True
    ElseIf Divident < Divisor Then
        Result = False
    End If

    Is_Rest_Of_Divide_Equal_To_Zero = Result

End Function

Hulett

First, this is not an answer to the question. Second, notice that Round performs Banker's Rounding leading to unexpected results. Third, Round is quite buggy (see for example VBA.Round). Forth, your code could be reduced somewhat. Fifth, this is VBS, not VBA as the question is tagged. – Dividivi

Answer 5

Public Function Modi(d as double) as double
Modi = d - Int(d)
End Function

Dim myDoule as Double
myDoule = 1.99999

Debug.Print Modi(myDoule)

---

0.99999

Futhark