VBA equivalent to Excel's mod function
You want the mod operator.
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).
My way to replicate Excel's MOD(a,b) in VBA is to use XLMod(a,b) in VBA where you include the function:
Function XLMod(a, b) ' This replicates the Excel MOD function XLMod = a - b * Int(a / b)End Functionin your VBA Module