result = number1 MOD number1

Returns the remainder after division operator (Integer).


number1The number you want the remainder of (Double).
number2The number you want to divide by (Double).

REMARKS
* This is actually an operator and not a function.
* The equivalent Excel function is Application.WorksheetFunction.MOD
* The Excel calculation is different to the VBA calculation so you can get different answers.
* The VBA Operator uses integer division.
* The Excel function uses floating point division.
* link - https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
* For the Microsoft documentation refer to docs.microsoft.com

Debug.Print 3 Mod 2           '= 1  
Debug.Print -3 Mod 2 '= -1
Debug.Print 3 Mod -2 '= 1
Debug.Print -3 Mod -2 '= -1
Debug.Print 2 Mod 3 '= 2
Debug.Print 3 Mod 3 '= 0
Debug.Print 8 Mod 3 '= 2
Debug.Print 8 Mod 30 '= 8
Debug.Print -30 Mod 28 '= -2 , Excel returns 26
Debug.Print 30 Mod 28 '= 2
Debug.Print 15.86 Mod 2 '= 0 , Excel returns 1.86
Debug.Print 100 Mod 40 '= 20
Debug.Print True Mod True '= 0
Debug.Print 2 ^ 31 Mod 7 '= Overflow , Excel returns 2
Debug.Print False Mod False '= #DIV/0!
Debug.Print 20 Mod 0 '= #DIV/0!

Public Function XLMod(a As Single, b As Single)
   XLMod = a - (b * VBA.Int(a/b))
End Function

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top