How to use MOD in VBA

10 Mod 3 returns 1, the remainder after dividing 10 by 3. In VBA, Mod is an operator, not a function, so it goes between the numbers: Range("B1").Value = Range("A1").Value Mod 3.

The common use is a test inside a loop. If n Mod 2 = 0 Then checks for an even number, and If i Mod 3 = 0 Then acts on every third row as i counts up.

VBA’s Mod doesn’t always match Excel’s MOD function. It rounds both numbers to whole numbers first, so 5.7 Mod 2 gives 0 where =MOD(5.7,2) gives 1.7. With a negative number, the result takes the sign of the first number, so -7 Mod 3 gives -1 where =MOD(-7,3) gives 2.

To get Excel’s result in VBA, use a - b * Int(a / b). Dividing by zero, as in 10 Mod 0, stops the macro with run-time error 11, and an empty cell counts as zero, so check the divisor when it comes from a cell.

Table of Content hide

Error in MOD

Related Tutorials

In VBA, MOD is an operator, not a function and this operator helps you to divide two numbers and returns the remainder value in the result.

It’s equivalent to the mod function in Excel.

You can use the mod in so many ways and, in this tutorial, we will see some examples. Use the below steps to use the mod operator in VBA:

Range("A1") = 10 Mod 3

Specify the first number which you want to get divided.
After that, enter the “mod” operator.
Now, enter a number that you want to divide by.
In the end, use a message box or a cell to get the remainder from the division.

In the same way, you can also get the remainder using a message box.

And in the following code, we used a message box and then used the mod operator to get the remainder after dividing.

MsgBox 9 Mod 3

And when you run this code, it returns zero in the result in the message box as when you divide 9 by 3 there’s no remainder left, so as in the result of this code.

Note: As I said, there’s also a function in Excel to get the remainder from the division of two numbers and there are a few situations where you will find that the result you get from Excel will be different from the result you got in VBA.

Error in MOD

If you try to divide a number with zero it always returns a division by zero error.

Debug.Print 10 Mod 0

And the above code returns that error.

What is VBA