How to use Mod Operator in VBA

To get the remainder of a division, you may use the VBA Mod operator.

For example:

Syntax of Mod:

The mod takes two numbers:

It divides two numbers and returns the remainder.

We will show you simple as well as examples by taking user input to get the Mod in VBA programs.

A simple example to divide two numbers and display Mod in the message box

Sub Mod_ex()

Dim num1, num2
num1 = 50
num2 = 12

MsgBox "The remainder is: " & num1 Mod num2

End Sub

Result:

Another example of Mod

Sub Mod_ex()
Dim num1, num2

num1 = 10
num2 = 2

MsgBox "The remainder is: " & num1 Mod num2

End Sub

Result:

A program to tell if a number is even or odd

By using the Mod operator, we can tell if the number is Even or Odd.

As principally, an even number is divisible by 2 i.e. it returns 0 remainder. So, we will write this using VBA logic.

We have an input box that asks the user to enter a number.

This is followed by using the Mod operator in the If statement.

If the remainder is zero, it’s an even number, otherwise Odd. Have a look:

Sub Mod_ex()

Dim num, remainder

num = InputBox("Enter a Number?")

remainder = num Mod 2

If remainder = 0 Then

    MsgBox num & " = Even"

Else

    MsgBox num & " = Odd"

End If

End Sub

Outputs as we entered number two times:

Using a Range of cells with Mod example

For this example, we have a range of cells from A2 to C6.

VBA code:

Sub Mod_ex()
Dim rng_mod As Range

Dim x

x = 2

Set rng_mod = Range("A2:A10")

For Each cell In rng_mod

    Range("C" & x) = cell Mod Range("B" & x)

    x = x + 1

Next

End Sub

Result:

You can see we also use negative values in different cells.

You can see the result for Mod on each.