VBA MOD

Excel VBA MOD Operator

In VBA MOD is same as to the application in mathematics, when a number is divided by its divisor and we get a reminder from that division, this function is used to give us that remainder from the division, it is not a function in VBA rather than it is an operator.

MOD is nothing but MODULO is a mathematical operation. It is exactly 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 division result is 10.50 by MOD is remainder of the division i.e. 1. (Number 2 can divide only 20, not 21, so the remainder is 1).

In the normal excel, it is a function but in VBA it is not a function, it’s just a mathematical operator. In this article, we will look into this operator in detail.

Syntax

Just to remind you this is not a function to have syntax. 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 this is the divisor i.e. we are going to divide Number 1 by this divisor.

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

Code:

Sub MOD_Example1()

Dim i As Integer

End Sub

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

As I told, in the beginning, MOD is an operator, not a function. So I have used the word MOD like how I 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 will show the value of “I”.

Example #2

Mod in vba always returns 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 MOD operator returns the integer value i.e. 2, not 2.25.

Now I 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

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

Wow!!! We have got zero as the answer. The reason we got zero because VBA round the numbers like our bankers do i.e. any decimal point which is greater than 0.5 will be rounded up to 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 is equal to zero.

Now I 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 will be rounded up to 27 and divisor value 3.51 will be rounded up to 4.

So if you divide 27 by 4 remainder is 3.

Excel MOD Function vs VBA MOD Operator

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

Step 2: Now If I apply the MOD function I will get the result as 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.

Things to Remember

• It is not a function but it is an arithmetic operator.
• This is roundup and rounds down decimal values unlike out MOD function in worksheet function.

Recommended Articles

This has been a guide to VBA MOD Function. Here we learned how to use the modulo along with practical examples and 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