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:
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 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:
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.