How to Use VBA Mod Operator (9 Examples)

The VBA Mod is not a function whereas MOD is a function in an Excel worksheet. VBA Mod is an operator which divides two numbers and returns the remainder. The operator Mod is a short form of MODULO that is used in mathematics operations. The Mod operators round up the floating-point.

Overview of VBA Mod

In this article, I will show you various examples of using the Excel VBA Mod operator.


Download to Practice


Basics of VBA Mod Function: Summary & Syntax

Summary

The VBA Mod operator divides two numbers and returns the remainder. Where one is known as a divisor another is a number. The Mod operator divides the number by the divisor.

Syntax

Number1 Mod Number2 (Divisor)

Arguments

Arguments Required/Optional Explanation
Number1 Required It is a numeric expression
Number2 Required It is a numeric expression

Return Value

The VBA Mod operator returns a remainder.

Version

The VBA Mod operator is available for Excel 2000 and later.

I’m using Excel Microsoft 365 to implement these examples.


9 Examples of Using VBA Mod Function in Excel

1. Using VBA Mod to Get Remainder

If you want, you can get a remainder by using the VBA Mod operator.

Let me explain the procedure to you,

To begin with, open the Developer tab >> select Visual Basic.

Using VBA MOD to Get Remainder

➤ Now, a new window of Microsoft Visual Basic for Applications will appear.

Next, from Insert >> select Module

Now, type the following code in the Module.

Sub Get_Reminder()
Dim n As Integer
n = 29 Mod 3
MsgBox " 29 Mod 3 is " & n
End Sub

Using VBA MOD to Get Remainder

Here, in the Get_Reminder sub procedure, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

Again, open the Developer tab >> from Insert >> select Button from Form Controls

Now, Drag the Button to place it where you want to give the caption.

Next, you can give a name to the button.

➤ I named it Get Reminder.

Using VBA MOD to Get Remainder

⏩ Now, right click on the mouse a context menu of Assign Macro will appear.

From there select Assign Macro.

⏩ A dialog box of Assign Macro will appear.

Then, select the Macro name and Macros in.

⏩ I selected Get_Reminder from the Macro name and selected VBA Mod.xlsm from Macros in.

Finally, click OK.

Using VBA MOD to Get Remainder

Then, click on the button named Get_Reminder.

Hence, it will show a msg box with the remainder.

You can do it for all the numbers to get the remainder.

Read More: VBA Format Function in Excel (8 Uses with Examples)


2. Using Cell Reference in VBA Mod to Get Remainder

By using Cell Reference from the Excel sheet, you can get the remainder from VBA Mod.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Reminder_Using_CellReference()
Dim n As Integer
n = Range("B4").Value Mod Range("C4").Value
MsgBox Range("B4").Value & " Mod " & Range("C4").Value & " is " & n
End Sub

Using Cell Reference in VBA MOD to Get Remainder

Here, in Reminder_Using_CellReference, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Next, used the cell reference B4 as number1 and C4 as number2 (divisor)
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Cell Reference.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for the used cell reference.

Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)


3. Using VBA Mod to Get a Remainder from a Negative Number

The VBA Mod also supports negative numbers while calculating the remainder.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Reminder_From_NegativeNumber()
Dim n As Integer
n = Range("B5").Value Mod Range("C5").Value
MsgBox Range("B5").Value & " Mod " & Range("C5").Value & " is " & n
End Sub

Using VBA MOD to Get a Remainder from a Negative Number

Here, in Reminder_From_NegativeNumber, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Next, used the cell reference B5 as number1 and C5 as number2 (divisor)
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Reminder From Negative Number.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for the negative number.

Read More: How to Use VBA IsNumeric Function (9 Examples)


4. Using VBA Mod to Get Remainder in Cell

Instead of showing the remainder through the msg box you can place it in a cell by using the VBA MOD function.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Reminder_in_Cell()
ActiveCell.FormulaR1C1 = "=MOD(RC[-2],RC[-1])"
Range("D4").Select
End Sub

Using VBA MOD to Get Remainder in Cell

Here, in sub-procedure Reminder_in_Cell, I used the ActiveCell.FormulaR1C1 format to get the position of ActiveCell.
Then, used the MOD function to get the remainder.
Also, used the Select method.

Now, Save the code and go back to the worksheet.

To insert the Button and assign the VBA code follow the steps explained in section 1.
➤ I named the button Reminder in Cell.

Next, select the D4 cell.
Then, click on the Button to run the VBA code.

As a result, you will get the remainder in the selected cell.

Using VBA MOD to Get Remainder in Cell

By following the same process, you will get the remainder for the rest of the numbers.

Read More: How to Use MsgBox Function in Excel VBA (A Complete Guideline)


5. Using VBA Mod with Integer Divisor & Float Number

In case your divisor is an integer type, but your number is in float type then you can use the VBA Mod operator.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Reminder_From_Decimal_Number()
Dim n As Integer
n = Range("B5").Value Mod Range("C5").Value
MsgBox Range("B5").Value & " Mod " & Range("C5").Value & " is " & n
End Sub

Using VBA MOD with Integer Divisor & Float Number

Here, in the Reminder_From_Decimal_Number sub-procedure, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Next, used the cell reference B5 as number1 and C5 as number2 (divisor)
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Reminder From Decimal Number.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for the decimal number.

But there is a problem that VBA rounds up the decimal. Here, the result was supposed to be 2.25 but the VBA Mod rounded it to 2.

Remember if any decimal/floating point is greater than 0.5 in the VBA Mod then it will be rounded up to the next integer value.

If it is less than 0.5 in the VBA Mod, then it will be rounded up to the existing integer value.

Read More: How to Use VBA Int Function in Excel ( 3 Examples)


Similar Readings:


6. Using VBA Mod When Divisor & Number Are Both Decimals

If your divisor and number both are in decimal/float type, then you can also use the VBA Mod operator.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Decimal_Both_Divisor_Number()
Dim n As Integer
n = Range("B5").Value Mod Range("C5").Value
MsgBox Range("B5").Value & " Mod " & Range("C5").Value & " is " & n
End Sub

Using VBA MOD When Divisor & Number Both Decimal

Here, in the sub-procedure Decimal_Both_Divisor_Number, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Next, used the cell reference B5 as number1 and C5 as number2 (divisor)
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Get Reminder From When Divisor & Number Decimal.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for both decimal divisor and numbers.

But there is a problem that VBA rounds up the decimal. Here, the result was supposed to be 1.75 but the VBA Mod rounded it to 2.

Related Content: VBA EXP Function in Excel (5 Examples)


7. VBA Mod to Round up Decimal Number Greater Than 0.5

Here, I will show you how the round up works in the VBA Mod.

To demonstrate to you the decimal point problem, first I will calculate the remainder using the Excel MOD function.

In cell D4, type the following formula,

=MOD(B6, C6)

Here, I used B6 as number C6 as a divisor.

Then, press ENTER to get the remainder and you will get the remainder which will be 7.7.

VBA MOD Rounds Up Decimal Number Greater Than 0.5

Now, let’s calculate through VBA Mod, to open the VBA editor follow the steps explained in section 1.

Then, type the following code in the Module.

Sub RoundsUp_Number()
Dim n As Integer
n = Range("B6").Value Mod Range("C6").Value
MsgBox Range("B6").Value & " Mod " & Range("C6").Value & " is " & n
End Sub

Here, in the sub-procedure Decimal_Both_Divisor_Number, I declared the variable n as Integer and used it to keep the returned value of the Mod operator.
Next, used the cell reference B6 as number1 and C6 as number2 (divisor)
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button RoundsUp Decimal Number.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for the used cell reference.

VBA MOD Rounds Up Decimal Number Greater Than 0.5

Look carefully at the remainder that the VBA Mod returned. The MOD function for the same values returned 7.7 but the VBA Mod operator returned 0. As the VBA rounded the values.

Read More: How to Use VBA Round Function in Excel (6 Quick Uses)


8. Determine Even or Odd Number

The VBA Mod also determines the Even or Odd number from a given range.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Determine_Even_Or_Odd()
Dim n As Integer
For n = Range("B4").Value To Range("B8").Value
If n Mod 2 = 0 Then
MsgBox n & " is an even number!"
Else
MsgBox n & " is an odd number!"
End If
Next n
End Sub

Using VBA MOD to Determine Even or Odd Number

Here, in the sub-procedure Determine_Even_Or_Odd, I declared the variable n as Integer.
Then, I used a For loop where I kept the value which I declared through cell reference.
Next, I used an IF function where I set the criteria as n Mod 2 = 0 if the value is true then it will return an Even statement otherwise Odd.
Then used the MsgBox to show the statements.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Even or Odd.

After that, click on the Button to run the VBA code.

Therefore, you will get to know which value is Even and which one is Odd.
Here, 1 is the Odd number.

Here, 2 is the Even number.

Read More: VBA If – Then – Else Statement in Excel (4 Examples)


9. Using Cell Range in VBA Mod to Get Remainder

You also can use the cell range to get the remainder by using the VBA Mod.

To open the VBA editor, follow the steps explained in section 1.

Then, type the following code in the Module.

Sub Get_Reminder_UsingVBA()
Dim n As Integer
For n = 4 To 9
MsgBox Cells(n, 2).Value Mod Cells(n, 3)
Next n
End Sub

Using Cell Range in VBA MOD to Get Remainder

Here, in the Get_Reminder_UsingVBA sub-procedure, I declared the variable n as Integer.
Then, I used a For loop where I kept the value which I declared through cell reference. The loop will work for the values from rows 4 to 9.
Then used the MsgBox to show the remainder.

Now, Save the code and go back to the worksheet.

To insert the Button, follow the steps explained in section 1.
➤ I named the button Dynamic Cell Reference.

After that, click on the Button to run the VBA code.

Therefore, you will get the remainder for all the used values one by one.
The first one is for the number 29 where the divisor is 3.

The 2nd one is for the number -47 where the divisor is 5.

Using Cell Range in VBA MOD to Get Remainder

The loop will work until it reaches row 9. The fifth one is for the number 59 where the divisor is 6.

Related Content: How to Use VBA Randomize Function in Excel (5 Examples)


Difference Between Excel MOD & VBA Mod

Though for most of the cases the return values are the same for the MOD function and the VBA Mod operator yet for some cases the result differs from each other. Let me show you the difference between them.

MOD Function VBA Mod Operator
The MOD function returns both Integer and Decimal numbers. Mod operator only returns Integer numbers.
While using a negative number in MOD it doesn’t return a negative sign. It supports negative numbers and then returns the negative sign.

Things to Remember

🔺 The operator will round up the decimal/floating points.


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.


Conclusion

In this article, I have shown 9 examples of the Excel VBA Mod operator. I also tried to cover the reasons to show errors frequently. Along with the things you will need to remember while using the operator. Feel free to comment down below for any types of queries and suggestions.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo