vba 带双打的 Mod
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23549947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mod with Doubles
提问by Blackhawk
Am I doing something wrong or does the VBA Mod operator actually not work with floating point values like Doubles?
我做错了什么还是 VBA Mod 运算符实际上不适用于像 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.
因此，我一直假设 VBA Mod 运算符可以根据VB 文档与 Doubles 一起使用，但是在试图弄清楚为什么我的舍入函数不起作用时，我发现了一些意外的 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 12instead of 13so I dug a little deeper and found that:
RoundUp(12.34)返回12而不是13所以我挖得更深一点，发现：
12.5 Mod 1returns 0with the return type of Long, whereas I had expected 0.5 with a type of Double.
12.5 Mod 1返回0类型为 Long 的返回值，而我预期返回类型为 Double 的 0.5。
Conclusion
结论
As @ckuhn203 points out in his answer, according to the VBA specification,
正如@ckuhn203 在他的回答中指出的那样，根据 VBA 规范，
The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result.
模数或余数运算符将 number1 除以 number2（将浮点数四舍五入为整数）并仅返回余数作为结果。
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.
通常，result 的数据类型是 Byte、Byte 变体、Integer、Integer 变体、Long 或包含 Long 的 Variant，无论 result 是否为整数。任何小数部分都会被截断。
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.5FMod(5.3, 2) = 1.3FMod(18.5, 4.2) = 1.7
FMod(12.5, 1) = 0.5FMod(5.3, 2) = 1.3FMod(18.5, 4.2) = 1.7
Using this in my rounding function solves my particular issue.
在我的舍入函数中使用它可以解决我的特定问题。
采纳答案by RubberDuck
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.
模数或余数运算符将 number1 除以 number2（将浮点数四舍五入为整数）并仅返回余数作为结果。例如，在下面的表达式中，A (result) 等于 5。 A = 19 Mod 6.7 通常，result 的数据类型是 Byte、Byte 变体、Integer、Integer 变体、Long 或包含 Long 的 Variant，无论是否或不是结果是一个整数。任何小数部分都会被截断。但是，如果任何表达式为 Null，则结果为 Null。任何为 Empty 的表达式都被视为 0。
Remember, mod returns the remainderof the division. Any integermod 1 = 0.
请记住， 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 beforeperforming the modulo.
不过，真正的罪魁祸首是 vba在执行模运算之前将双精度截断（向下舍入）为整数。
?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
回答by Davis
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.
我相信 Mod 运算符仅使用 long 类型进行计算。您提供的链接用于 VB.Net，它与您在 MSAccess 中使用的 VBA 不同。
The operator in VBA appears to accept a double type, but simply converts it to a long internally.
VBA 中的运算符似乎接受 double 类型，但只是在内部将其转换为 long。
This test yielded a result of 1.
本次测试结果为 1。
9 Mod 4.5
This test yielded a result of 0.
该测试的结果为 0。
8 Mod 4.5
回答by Russell Munro
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.
作为一种解决方法，您可以对这些值进行一些简单的数学运算。要获得两位小数的精度，只需将输入值乘以 100，然后将结果除以 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.
我参加聚会迟到了，但以防万一这个答案仍然对某人有帮助。
