xl vba mod function floating point number code example

Example: excel vba equivalent to Excel's mod function

'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

'
'
'