4 Examples to Learn VBA Mod Operator

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

How to use Mod operator in VBA

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

For example:

10 Mod 2 = 0

Syntax of Mod:

Mod takes two numbers:

Num1 Mod Num2

It divides two numbers and returns the remainder.

We will show you simple as well as examples by taking user input for getting 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:

VBA-Mod

Another example of Mod

Sub Mod_ex()
Dim num1, num2

num1 = 10
num2 = 2

MsgBox "The remainder is: " & num1 Mod num2

End Sub

Result:

VBA-Mod-0

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 into 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 Range of cells with Mod example

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

The A column contains Dividends.

B column contains Divisor

In the C column, we will get the remainder for the corresponding Dividend and Divisor.

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:

VBA-Mod-Cells

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

So, we have:

  • Positive Dividend and divisor
  • Negative Dividend and positive divisor
  • Negative Dividend and negative divisor

You can see the result for Mod on each.