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__

Arguments |
Required/Optional |
Explanation |
---|---|---|

Number1 |
Required | a numeric expression |

Number2 |
Required | a numeric expression |

__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 *s*ub-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 R*emainder_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**.

`=MOD(B6, C6)`

**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**

MOD Function |
VBA Mod Operator |
---|---|

The MOD function returns both Integer and Decimal numbers. |
The Mod operator returns Integer numbers only. |

A negative number in MOD doesn’t return a negative sign. |
For Negative numbers, it returns a negative sign. |

**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)**