Excel VBA MOD Operator

MOD is nothing but MODULO, a mathematical operation. It is the same as the division, but the result is slightly different where division takes the divided amount. But, MOD takes the remainder of the division. For example: If you divide 21 by 2 divisions, the result is 10.50 by MOD is the remainder of the division, i.e., 1. (Number 2 can divide only 20, not 21, so the remainder is 1).

In normal Excel, it is a function. But in VBA, it is not a function. Instead, it is just a mathematical operator. In this article, we will look into this operator in detail.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA MOD (wallstreetmojo.com)

Syntax

To remind you, this is not a function to have syntax. But, for our reader’s understanding, let me put it in the word.

Number 1 MOD Number 2 (Divisor)

Number 1 is nothing, but what is the number we are trying to divide?

Number 2 is the divisor, i.e., we will divide Number 1 by this divisor.

MOD is the result given by Number 1 / Number 2.

How to use MOD in VBA?

Example #1

Follow the below steps to write the code.

Step 1: Create a macro name.

Code:

Sub MOD_Example1()

End Sub

Step 2: Define one of the variables as “Integer.”

Sub MOD_Example1()

Dim i As Integer

End Sub

Step 3: Now perform the calculation as “i = 20 MOD 2.”

As we said in the beginning, MOD is an operator, not a function. So, we have used the word MOD like how we enter a plus (+).

Sub MOD_Example1() Dim i As Integer i = 21 Mod 2

End Sub

Step 4: Now, assign the value of “I” to the message box.

Sub MOD_Example1() Dim i As Integer

i = 21 Mod 2 MsgBox i

End Sub

Step 5: Run the code message box that will show the value of “I.”

Example #2

The MOD function in VBA always returns an integer value, i.e., without decimals if you supply the number in decimals. For example, look at the below code.

Sub MOD_Example2() Dim i As Integer

i = 26.25 Mod 3 MsgBox i

End Sub

Divisor 3 can divide 24, so the remainder here is 2.25. But the MOD operator returns the integer value, i.e., 2, not 2.25.

Now, we will modify the number to 26.51 and see the difference.

Sub MOD_Example2() Dim i As Integer

i = 26.51 Mod 3 MsgBox i

End Sub

We will run this code and see what the result is.

We have got zero as the answer. We got zero because VBA roundsVBA RoundRound function in VBA is a mathematical function that rounds up or down the given number to the specific set of decimal places specified by the user to ease calculation.read more the numbers like our bankers do, i.e., it will round up any decimal point greater than 0.5 to the next integer value. So, in this case, 26.51 is rounded up to 27.

Since 3 can divide the 27 by 9, we will not get any remainder values, so the value of i equals zero.

Now, we will supply the divisor value also in decimal points.

Sub MOD_Example2() Dim i As Integer

i = 26.51 Mod 3.51 MsgBox i

End Sub

Step 6: Run this code and see what the result is.

We got 3 as the answer because 26.51 rounded up to 27, and the divisor value 3.51 will be rounded up to 4.

So, if you divide 27 by 4, the remainder is 3.

Excel MOD Function vs. VBA MOD Operator

Step 1: Now, look at the difference between excel and VBA MOD operator. We have a value of 54.24. The divisor value is 10.

Step 2: If we apply the MOD function, we will get the result of 4.25.

Step 3: But if you do the same operation with VBA, we will get 4 as the remainder, not 4.25.

Sub MOD_Example2() Dim i As Integer

i = 54.25 Mod 10 MsgBox i

End Sub

Step 4: Run this code and see what the result is.

Things to Remember

  • It is not a function, but it is an arithmetic operator.It is roundup and rounddown decimal values, unlike our MOD function in the worksheet function.

This article has been a guide to VBA MOD Function. Here, we learned how to use the modulo, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • VBA Return StatementVBA RoundUpVBA Cell ReferencesWhat is VBA Range?VBA Randomize