vba mod function floating point number
'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
'
'
'