VBA MOD

Excel VBA MOD Function

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

Formula of VBA MOD

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?

After all the theory question is how to use. Ok, let us jump into practicality now. Follow below steps to write the code for MOD in VBA.

Example #1

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” i.e. MOD value.

Example #2

Step 1: It 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

Step 2: Divisor 3 can divide 24 so remainder here is 2.25 but MOD operator in VBA returns the integer value i.e. 2, not 2.25.

Step 3: Now I will modify the number from 26.51 and see the difference.

Code:

Sub MOD_Example2()
  Dim i As Integer

   i = 26.51 Mod 3
  MsgBox i

End Sub

Step 4: Now 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 “I” value is equal to zero.

Step 5: 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 MOD function and VBA MOD operator. I have a value of 54.24 and 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 Excel VBA MOD we will 10 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 Here

• 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 VBA MODULO function along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• Instance of Dictionary with Excel VBA
• VBA Worksheet
• VBA Cell References
• What is VBA Range?
• VBA Certification Course
• Combo Box in VBA