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 mod with floating-point numbers may require extra attention:
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).
Function XLMod(a, b As Long)
' This replicates the Excel MOD function - the VBA mod function returns an integer
Dim templong As Long
templong = (b * Int(a / b))
XLMod = (a - templong)
End Function
I've found this to be the most reliable if excel's mod function is also giving incorrect results:
Originally even
XLMod = (a - (b * Int(a / b))) was giving me incorrect results so..
Function XLMod(a, b As Long)
' This replicates the Excel MOD function - the VBA mod function returns an integer
Dim templong As Long
templong = (b * Int(a / b))
XLMod = (a - templong)
End Function
发布评论
评论（9）
在vba中，该函数是MOD。
例如，
这是一个有用的链接。
In vba the function is MOD.
e.g
Here is a useful link.
您需要 mod 运算符。
编辑添加：
您可能需要考虑一些特殊情况，因为 Excel 使用浮点数学（并返回
float），而 VBA 函数返回整数。因此，对浮点数使用mod可能需要额外注意：Excel 的结果可能与您的预测不完全一致；对此进行了简要介绍此处（参见最上面的答案） 以及详细内容 此处。
正如 @André 在评论中指出的那样，负数可能会与您期望的方向相反。他建议的
Fix()函数已解释此处 (MSDN)。You want the mod operator.
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, usingmodwith floating-point numbers may require extra attention: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).我在 VBA 中复制 Excel 的
MOD(a,b)的方法是在 VBA 中使用XLMod(a,b)，其中包含以下函数：在 VBA 模块中
My way to replicate Excel's
MOD(a,b)in VBA is to useXLMod(a,b)in VBA where you include the function:in your VBA Module
使用 Excel
MOD(a,b)函数和 VBAa Mod b运算符时要非常小心。Excel 返回浮点结果，VBA 返回整数。
在 Excel 中
=Mod(90.123,90)返回0.123000000000005而不是 0.123在 VBA 中
90.123 Mod 90返回0它们当然不等价！
等效项有：
在 Excel 中： =Round(Mod(90.123,90),3) 返回 0.123 且
在 VBA 中：((90.123 * 1000) Mod 90000)/1000 也返回 0.123
Be very careful with the Excel
MOD(a,b)function and the VBAa Mod boperator.Excel returns a floating point result and VBA an integer.
In Excel
=Mod(90.123,90)returns0.123000000000005instead of 0.123In VBA
90.123 Mod 90returns0They 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
Mod运算符大致相当于MOD函数:number Mod divisor大致相当于MOD(number, divisor)。The
Modoperator, is roughly equivalent to theMODfunction:number Mod divisoris roughly equivalent toMOD(number, divisor).此函数始终有效，并且是 Excel 函数的精确副本。
This function always works and is the exact copy of the Excel function.
但如果你只是想区分奇数迭代和偶数迭代之间的区别，这很好用：
But if you just want to tell the difference between an odd iteration and an even iteration, this works just fine:
我发现如果 excel 的 mod 函数也给出不正确的结果，这是最可靠的：
最初甚至
XLMod = (a - (b * Int(a / b))) 给了我不正确的结果，所以..
I've found this to be the most reliable if excel's mod function is also giving incorrect results:
Originally even
XLMod = (a - (b * Int(a / b))) was giving me incorrect results so..
楼上的答案其实是错误的。
建议的方程：
a - (b * (a \ b))将求解：
a - a在所有情况下当然都是 0。
正确的等式是：
a - (b * INT(a \ b))或者，如果数字 (a) 可以为负数，请使用：
a - (b * FIX(a \ b))The top answer is actually wrong.
The suggested equation:
a - (b * (a \ b))Will solve to:
a - aWhich is of course 0 in all cases.
The correct equation is:
a - (b * INT(a \ b))Or, if the number (a) can be negative, use this:
a - (b * FIX(a \ b))