excel vba modulo
'VBA does have the 'Mod' operator: MsgBox 5 Mod 2 '<--displays: 1 (5 ÷ 2 has a REMAINDER of 1) 'But bear in mind that it returns an integer value... always. MsgBox 11.5 Mod 3 '<--displays: 0 (But it should be 2.5) 'And in some cases it fails completely: MsgBox 11 Mod 0.4 '<--Fails: Runtime error 11: division by zero 'It also does not always handle negative numbers correctly: MsgBox -11 Mod 3 '<--displays: -2 (But it should be 1) MsgBox 11 Mod -3 '<--displays: 2 (But it should be -1) 'The VBA 'Mod' operator also imposes limits on the size of 'the operands. The operands are limited by the Long Integer data type, 'which maxes out at 2147483647: MsgBox (2147483647 + 1) Mod 5 '<--Fails: Runtime error 6: overflow 'In contrast, the 'MOD()' worksheet function returns a Double 'floating point value... always. Also the 'MOD()' worksheet function 'handles negative and decimal operands correctly: MsgBox [MOD(-11,3)] '<--displays: 1 (Correct) MsgBox [MOD(11,-3)] '<--displays: -1 (Correct) MsgBox [MOD(11.5,3)] '<--displays: 2.5 (Correct) MsgBox [MOD(2147483648,5)] '<--displays: 3 (Correct) MsgBox [MOD(11,0.4)] '<--displays: 0.199999999999999 'But notice the floating point rounding error on the last example 'above. It should calculate a result of precisely: 0.2 'And, calling the Excel object model is always inefficient, so it 'is better to stay with pure VBA when possible. 'So here is a superior pure VBA function: Function Mod2(n, divisor) Mod2 = CDec(n) - divisor * Int(n / divisor) End Function 'This VBA function has all of the advantages of the worksheet function 'but is more precise and MUCH faster when called from VBA: '1) It can correctly return a decimal value '2) It correctly handles negative arguments '3) It uses the Decimal Variant data subtype to reduce ' floating point error '4) Because of the Decimal subtype it can handle parameters MUCH ' larger than even the worksheet function can '5) It does not call the Excel object model (faster). This also ' allows it to be used in other VBA environments like Word and Access MsgBox Mod2(11, 0.4) '<--displays: 0.2 (Correct, no rounding error) MsgBox Mod2(11.5, 3) '<--displays: 2.5 (Correct) MsgBox Mod2(-11, 3) '<--displays: 1 (Correct) MsgBox Mod2(11, -3) '<--displays: -1 (Correct) MsgBox Mod2("9851201567410588", 1349) '<--displays: 948 (Correct) ' 'Notice in the final example we pass the first argument as a string. 'This is done to preserve value accuracy as the number is too large 'for even a Double to represent accurately. Notice that the worksheet 'function fails here, whereas the 'Mod2()' function works just fine: MsgBox [MOD(9851201567410588,1349)] '<--Fails: Runtime error 13: type mismatch ' ' '
