How can I implement the Modulo function in VBA?

VBA 2003 does not support MOD (modulo arithmetic) as a WorksheetFunction, and there does not seem to be a direct MOD function in VBA. I've been dabbling with code that would do the same thing as MOD, so far with no success. Does anyone know how to do this in VBA? I assume it would be some combination of If...Then, Round (up or down), maybe a compare, and perhaps an iteration loop. I would think such a routine would be useful to many people.

Thanks in advance.

Hello batman,

Read the VBA Help files...

Mod Operator


Used to divide two numbers and return only the remainder.

Syntax

result = number1 Mod number2

The Mod operator syntax has these parts:

Part Description
result Required; any numeric variable.
number1 Required; any numeric expression.
number2 Required; any numeric expression.



Remarks

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. For example, in the following expression, A (result) equals 5.

A = 19 Mod 6.7

Usually, the data type of result is a Byte, Byte variant, Integer, Integer variant, Long, or Variant containing a Long, regardless of whether or not result is a whole number. Any fractional portion is truncated. However, if any expression is Null, result is Null. Any expression that is Empty is treated as 0.

VBA Mod handles negative numbers differently than the worksheet function MOD.

=MOD(-1, 3) returns 2

Please Login or Register  to view this content.

returns -1

Please Login or Register  to view this content.

will return the same value as =MOD(a,b)