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__

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

➤ 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 *s*ub-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 R*eminder_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,

`=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**.

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.

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

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