VBA 相当于 Excel 的 mod 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378047/
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
VBA equivalent to Excel's mod function
提问by Arlen Beiler
Is there a vba equivalent to excel's modfunction?
是否有相当于 excelmod功能的 vba ？
采纳答案by egrunin
You want the mod operator.
你想要mod 运算符。
The expression a Mod b is equivalent to the following formula:
a - (b * (a \ b))
Edited to add:
编辑添加：
There are some special cases you may have to consider, because Excel is using floating point math (and returns a float), which the VBA function returns an integer. Because of this, using modwith floating-point numbers may require extra attention:
您可能需要考虑一些特殊情况，因为 Excel 使用浮点数学（并返回 a float），而 VBA 函数返回一个整数。因此，使用mod浮点数可能需要额外注意：
Excel's results may not correspond exactly with what you would predict; this is covered briefly here (see topmost answer)and at great length here.
As @André points out in the comments, negative numbers may round in the opposite direction from what you expect. The
Fix()function he suggests is explained here (MSDN).
Excel 的结果可能与您的预测不完全一致；此处简要介绍了这一点（请参阅最上面的答案），并在此处详细介绍。
回答by Vincent Ramdhanie
回答by Michael Wilson
My way to replicate Excel's MOD(a,b)in VBA is to use XLMod(a,b)in VBA where you include the function:
我MOD(a,b)在 VBA 中复制 Excel 的方法是XLMod(a,b)在包含函数的 VBA 中使用：
Function XLMod(a, b)
' This replicates the Excel MOD function
XLMod = a - b * Int(a / b)
End Function
in your VBA Module
在您的 VBA 模块中
回答by Carlo
Be very careful with the Excel MOD(a,b) function and the VBA a Mod b operator. Excel returns a floating point result and VBA an integer.
使用 Excel MOD(a,b) 函数和 VBA a Mod b 运算符时要非常小心。Excel 返回一个浮点结果，VBA 返回一个整数。
In Excel =Mod(90.123,90) returns 0.123000000000005 instead of 0.123 In VBA 90.123 Mod 90 returns 0
在 Excel 中 =Mod(90.123,90) 返回 0.123000000000005 而不是 0.123 在 VBA 90.123 Mod 90 中返回 0
They are certainly not equivalent!
它们当然不等价！
Equivalent are: In Excel: =Round(Mod(90.123,90),3) returning 0.123 and In VBA: ((90.123 * 1000) Mod 90000)/1000 returning also 0.123
等效项是：在 Excel 中：=Round(Mod(90.123,90),3) 返回 0.123 和在 VBA 中：((90.123 * 1000) Mod 90000)/1000 也返回 0.123
回答by LukeH
The Modoperator, is roughly equivalent to the MODfunction:
number Mod divisoris roughly equivalent to MOD(number, divisor).
number Mod divisor大致相当于MOD(number, divisor).
回答by Riccardo La Marca
Function Remainder(Dividend As Variant, Divisor As Variant) As Variant
Remainder = Dividend - Divisor * Int(Dividend / Divisor)
End Function
This function always works and is the exact copy of the Excel function.
此函数始终有效，并且是 Excel 函数的精确副本。
回答by Rhys
But if you just want to tell the difference between an odd iteration and an even iteration, this works just fine:
但是，如果您只想区分奇数迭代和偶数迭代之间的区别，那么这很好用：
If i Mod 2 > 0 then 'this is an odd
'Do Something
Else 'it is even
'Do Something Else
End If
回答by Stu
The top answer is actually wrong.
上面的答案实际上是错误的。
The suggested equation:
a - (b * (a \ b))
建议的等式：
a - (b * (a \ b))
Will solve to: a - a
将解决为： a - a
Which is of course 0 in all cases.
当然在所有情况下都是 0。
The correct equation is:
正确的等式是：
a - (b * INT(a \ b))
a - (b * INT(a \ b))
Or, if the number (a) can be negative, use this:
或者，如果数字 (a) 可以是负数，请使用：
a - (b * FIX(a \ b))
a - (b * FIX(a \ b))
