How to Use the VBA Mod Operator – 9 Examples

This is an overview.

Download to Practice

The VBA Mod Function

Summary

The VBA Mod operator divides the number by the divisor and returns the remainder.

Syntax

Number1 Mod Number2 (Divisor)

Arguments

Return Value

returns the remainder.

Example 1 – Using the VBA Mod Operator to Get the Remainder

• Open the Developer tab >> select Visual Basic.

In the Microsoft Visual Basic for Applications window:

• Select Insert >> choose Module

• Enter 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

In the Get_Remainder sub procedure, the variable n is declared as Integer and used to keep the value returned by the Mod operator.
The MsgBox is used to show the remainder.

• Save the code and go back to the worksheet.
• Open the Developer tab >> Insert >> select Button in Form Controls

• Drag the Button and place it in the worksheet.
• Name to the button. Here, Get Remainder.

• Right click and select Assign Macro.

• Select the Macro name: Get_Remainder and VBA Mod.xlsm in Macros in.
• Click OK.

• Click the Get_Remainder button.

It will show a msg box with the remainder.

Follow the same procedure for all numbers.

Read More: Excel VBA MsgBox Function (All MsgBox Types, Constants & Return Values)

Example 2 – Using a Cell Reference in VBA Mod to Get the Remainder

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In Remainder_Using_CellReference, the variable n is declared as Integer and used to keep the value returned by the Mod operator.
The cell reference B4 is used as number1 and C4 as number2 (divisor)
The MsgBox is used to show the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Cell Reference.
• Click the Button to run the VBA code.

You will get the remainder of the selected cell reference.

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

Example 3 – Using the VBA Mod Operator to Get the Remainder of a Negative Number

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In Remainder_From_NegativeNumber, the variable n is declared as Integer and used to keep the value returned by the Mod operator. The cell reference B5 is used as number1 and C5 as number2 (divisor)
The MsgBox shows the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Remainder From Negative Number.
• Click the Button to run the VBA code.

You will see the remainder of the negative number.

Example 4 – Using the VBA Mod Operator to Get the Remainder in a Cell

• Follow the steps described in Example 1 to open the VBA editor.
• Enter the following code in the Module.

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

In the sub-procedure Remainder_in_Cell, the ActiveCell.FormulaR1C1 format is used to get the position of the ActiveCell.
The MOD function is used to get the remainder.
The Select method is used.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Remainder in Cell.
• Select D4.
• Click the Button to run the VBA code.

You will see the remainder in the selected cell.

• Follow the same procedure 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

Example 5 – Using the VBA Mod Operator with an Integer Divisor and a Float Number

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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 Remainder_From_Decimal_Number sub-procedure, the variable n is declared as Integer and used it to keep the value returned by the Mod operator. The cell reference B5 is used as number1 and C5 as number2 (divisor)
The MsgBox shows the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Remainder From Decimal Number.
• Click the Button to run the VBA code.

You will get the remainder of the decimal number.

VBA rounds up the decimal: the result is 2.25 but is rounded to 2.

If any decimal/floating point is greater than 0.5 in the VBA Mod Operator, it is rounded to the next integer value.

If it is less than 0.5,  it is rounded to the existing integer value.

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

Example 6 – Using the VBA Mod Operator When both Divisor and Number Are Decimals

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In the sub-procedure Decimal_Both_Divisor_Number, the variable n is declared as Integer and used to keep the value returned by the Mod operator. The cell reference B5 is used as number1 and C5 as number2 (divisor)
The MsgBox shows the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Get Remainder From When Divisor & Number Decimal.
• Click the Button to run the VBA code.

You will get the remainder of both decimal divisor and numbers.

The result is 1.75 but is rounded to 2.

Example 7 – Using the VBA Mod Operator to Round up a Decimal Number Greater Than 0.5

• Enter the following formula in D4.

B6 is used as number and C6 as divisor.

• Press ENTER to get the remainder : 7.7.

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In the sub-procedure Decimal_Both_Divisor_Number, the variable n is declared as Integer and used to keep the value returned by the Mod operator. The cell reference B6 is used as number1 and C6 as number2 (divisor)
The MsgBox shows the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, RoundsUp Decimal Number.
• Click the Button to run the VBA code.

You will see the remainder of the selected cell reference.

The VBA Mod operator returned 0.

Example 8 – Determine Even or Odd Numbers

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In the sub-procedure Determine_Even_Or_Odd, the variable n is declared as Integer. A For loop is used to keep the value declared in the cell reference. In the IF function the criteria are set as n Mod 2 = 0. If the value is true, it will return an Even statement. Otherwise, Odd.
The MsgBox shows the statements.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Even or Odd.
• Click the Button to run the VBA code.

You will see the Even and the Odd numbers.

1 is Odd.

2 is Even.

Example 9 – Using a Cell Range in the VBA Mod Operator to Get the Remainder

• Follow the steps described in Example 1 to open the VBA editor.
• Enter 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

In the Get_Remainder_UsingVBA sub-procedure, the variable n is declared as Integer. A For loop is used to keep the value declared in the cell reference. The loop will work from row 4 to 9.
The MsgBox shows the remainder.

• Save the code and go back to the worksheet.
• Follow the steps described in Example 1 to insert a Button. Here, Dynamic Cell Reference.
• Click the Button to run the VBA code.

You will see the remainder of all used values one by one.
For number 29, the divisor is 3.

For number -47, the divisor is 5.

The loop works until it reaches row 9.

Difference Between the Excel MOD and the VBA Mod

Practice Section

Practice here.

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)
• How to Call a Sub in VBA in Excel (4 Examples)