VBA MOD

In VBA, MOD is the same as the application in mathematics. For example, when we divide a number by its divisor, we get a reminder from that division. This function one may use to give us that remainder from the division. It is not a function in VBA. Rather, it is an 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.

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.

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

Code:

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 (+).

Code:

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.

Code:

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.

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.

Code:

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

Code:

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.

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.

Code:

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.

• 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 Statement
• VBA RoundUp
• VBA Cell References
• What is VBA Range?
• VBA Randomize