Excel has a MOD function that returns the remainder after a division. Excel VBA also has the Mod operator. Unfortunately, they return different results when it comes to negatives.
The Excel MOD function works correctly – see examples below.
The Mod operator works ok for positive numbers – image below.
But it returns the wrong result for negatives – image below.
The fix is to create your own function for Mod in VBA – image below.
You can see the results for the NewMod function in the images below.
The Excel MOD function is useful and offers many solutions to common problems. I have written about it a few times, links below.
If you need to handle the mod calculation in VBA, and you might encounter negatives, then you will need to create your own function.
Just noting that you can tap into Excel’s MOD function from within the VBA environment. This function will duplicate the one you posted in your blog (although I renamed it)…
Function xlMOD(Num As Long, Div As Long) As Long
xlMOD = Evaluate(“MOD(” & Num & “,” & Div & “)”)
End Function
Thanks Rick
Resorting the Evaluate function – the function of last resort.