How to Use MOD Operator in VBA (With Examples)

You can use the Mod operator in VBA to calculate the remainder of a division.

Here are two common ways to use this operator in practice.

Method 1: Use Mod Operator with Hard Coded Values

Sub UseMod()
Range("A1") = 20 Mod 6
End Sub

This particular example will return the remainder of 20 divided by 6 in cell A1.

Method 2: Use Mod Operator with Cell References

Sub UseMod()
Range("C2") = Range("A2") Mod Range("B2")
End Sub

This particular example will calculate the remainder of the value in cell A2 divided by the value in cell B2 and output the result in cell C2.

The following examples show how to use each method in practice.

Example 1: Use Mod Operator with Hard Coded Values

Suppose we would like to calculate the remainder of 20 divided by 6 and output the result in cell A1.

We can create the following macro to do so:

Sub UseMod()
Range("A1") = 20 Mod 6
End Sub

When we run this macro, we receive the following output:

The result of 20 Mod 6 turns is 2.

This value is shown in cell A1, just as we specified in the macro.

Note: The value “6” goes into “20” three times and has a remainder of 2. This is how 20 Mod 6 is calculated.

Suppose we would like to calculate the remainder of the value in cell A2 divided by the value in cell B2 and output the result in cell C2.

We can create the following macro to do so:

Sub UseMod()
Range("C2") = Range("A2") Mod Range("B2")
End Sub

When we run this macro, we receive the following output:

The result of 20 Mod 6 turns is 2.

This value is shown in cell C2, just as we specified in the macro.

Note: You can find the complete documentation for the VBA Mod operator here.

The following tutorials explain how to perform other common tasks in VBA: