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.

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

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.

➤ 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

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.

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

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: Excel VBA MsgBox Function (All MsgBox Types, Constants & Return Values)

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

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.

Read More: 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

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.

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

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.

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

Similar Readings

• How to Use VBA Val Function in Excel (7 Examples)
• Use Concatenate in Excel VBA (4 Methods)
• How to Use VBA TimeValue Function (6 Relevant Examples)
• Use VBA Case Statement (13 Examples)
• How to Use the VBA DateAdd Function in Excel

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

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 Round Function in Excel (6 Quick Uses)

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

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.

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,

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.

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.

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.

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

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.

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

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.

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

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.

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

• How to Execute VBA Function Procedure in Excel (2 Easy Ways)
• VBA Format Function in Excel (8 Uses with Examples)
• How to Use VBA Function Procedure with Arguments in Excel
• Use VBA Input Function in Excel (2 Examples)
• How to Use VBA Asc Function (5 Practical Examples)
• Use InStr Function in VBA (3 Easy Examples)
• How to Call a Sub in VBA in Excel (4 Examples)