**What is Excel VBA Mod operator?**

The Mod function in Excel VBA calculates the remainder when dividing a number by another. The VBA Mod command works with numerical data types such as integers, longs, and doubles. However, it’s important to note that it can produce unexpected results when used with double-precision numbers.

It is because double-precision numbers are represented as binary fractions, which can have rounding errors and precision issues when compared to decimal fractions. Consider the following example:

Here, ‘1’ will be printed in the Immediate box since ten divided by 3 gives a remainder, one.

##### Table of Contents

###### Key Takeaways

- MOD finds the remainder when one number is divided by another. The syntax of the Mod operator is: result = number1 Mod number2.
- The Mod operator has a lower precedence than other arithmetic operators, such as multiplication and division. If you use the Mod operator with other operators in a single statement, you may need to use parentheses to control the order of operations.
- The Mod operator can be used with different data types, including integer, long, and double. However, rounding errors can occur with double-precision numbers, producing unexpected results.

### Syntax

The syntax for Excel VBA Mod division is as shown below:

**Result = number1 Mod number2**

where

- ‘number1’ (Required) is the numeric dividend
- ‘number2’ (Required) is the numeric divisor
- ‘Result’ is the remainder

### How to use MOD in VBA?

To use VBA Mod, write the necessary code to implement it.

**Step 1: **Go to the “**Developer**” tab in the toolbar and click the “**Visual Basic**” option. Now, the VBA Editor opens to add functions and sub-procedures. Next, click the “**Insert**” button followed by “**Module**” to create a new module or blank page.

**Step 2: **Consider an example where we must check whether the input is an even or odd number. It can be checked using the VBA Mod command, as shown below.

Create a function called OddorEven() to check whether the number is even or odd.

**Step 3: **Define a variable called “num” as an Integer. Use the InputBox() in Excel VBA, which accepts input from the user using a popup box, and Excel reads it.

**Step 4: **Initialize an If-Else statement to check whether the input number is odd or even. Here, Mod returns zero if the number is even.

It will print the input number and the statement in the tab in VBA.

**Code:**

**Sub OddOrEven() Dim num As Integer num = InputBox(“Enter number:”) If num Mod 2 = 0 Then Debug.Print num & ” is an even number.” Else Debug.Print num & ” is an odd number.”End IfEnd Sub**

The above code will bring a popup box when executed (using F5) where the user can input the number to be checked. Here, we give the input ’22,’ which will print ‘22 is an even number’.

**Step 5: **Run the code by pressing F5 or the Run button. It will print as mentioned above.

### Examples

Here are a few examples to help understand the VBA Mod division and its functionalities.

#### Example #1

Consider an application where the user can input two numbers using the InputBox() function and check if one number is divisible by the other.

The InputBox() opens a prompt for the user to enter values. After pressing the “OK” button, Excel reads the user’s input.

**Step 1: **Create a new sub-procedure DivisibleBy() to check whether one number is divisible by the other.

**Step 2: **Declare two variables, ‘num1’ and ‘num2,’ as integers.

**Step 3: **Accept custom input from the user for both ‘num1’ and ‘num2’ using the InputBox() function.

**Step 4: **Initialize an If-Else statement to check whether using Mod on the numbers returns a remainder. If it returns a remainder, the first number is not divisible by the second one; else, it is divisible.

**Code:**

**Sub DivisibleBy() Dim num1 As Integer Dim num2 As Integer num1 = InputBox(“Enter the first number:”) num2 = InputBox(“Enter the second number:”)If num1 Mod num2 = 0 Then MsgBox num1 & ” is divisible by ” & num2Else MsgBox num1 & ” is not divisible by ” & num2 End IfEnd Sub**

**Step 5: **To run the code, click the green arrow button in the VBA toolbar.

**Step 6: **Input the desired numbers in the prompts and click “OK.” The output is as shown below:

#### Example #2

Suppose you have a list of dates and want to highlight every third date in the list in pink. In this case, the VBA Mod function can construct an If-Else statement highlighting every third cell.

**Step 1: **Create a new sub-procedure HighlightEveryThirdDate(), which checks the above condition.

**Step 2: **Declare three variables; here, ‘dateRange’ and ‘cell’ are of datatype Range, and ‘counter’ is of datatype Integer.

**Step 3: **Initialize the value of “dateRange” as the range of the above table (it can be changed based on the preferred range) and the counter as 0.

**Step 4: **Initialize a For loop to increment the counter variable to check every cell. Inside the For loop, initialize an If-Else statement to check whether the cell row index is divisible by 3 using the VBA Mod function.

If counter Mod 3 = 0 Then

cell.Interior.ColorIndex = 7

Here, if the counter is divisible by three, it is the third cell; hence, we color it pink (color index 7). Therefore, every third cell is shaded in pink.

**Code:**

**Sub HighlightEveryThirdDate()Dim dateRange As RangeDim cell As RangeDim counter As IntegerSet dateRange = Range(“A1:A10”)counter = 0For Each cell In dateRangecounter = counter + 1If counter Mod 3 = 0 Thencell.Interior.ColorIndex = 7End IfNext cellEnd Sub**

**Step 5: **To run the code, click the green arrow button in the VBA toolbar.

**Step 6: **The resulting output is as shown below:

As seen above, the cell indices 3, 6, and 9 are highlighted in pink because the index number is divisible by 3, which can be checked with the VBA MOD.

### Excel MOD function vs. VBA Mod operator

The MOD function in Excel and the VBA MOD operator performs the same operation of returning the remainder of a division operation. However, there are some differences between the two:

**Syntax:**The syntax of the MOD function in Excel is**MOD(number, divisor**), where the number is the value to be divided, and the divisor is the value to divide by. The syntax of the VBA MOD operator is**number Mod divisor**, where number and divisor have the same meanings as in the Excel function.**Range of values:**The Excel MOD function can perform modulo operations on positive and negative numbers, whereas the VBA MOD operator can only be used with positive numbers. If you use a negative number with the VBA MOD operator, it will return a negative remainder. To get the positive remainder, you need to add the divisor to the result of the modulo operation.**Data types:**The Excel MOD function can handle various data types, including numbers, dates, and times, whereas the VBA MOD operator can only be used with numerical data types, such as integers and doubles.**Error handling:**The Excel MOD function returns the #VALUE! error if either the number or divisor argument is non-numeric, whereas the VBA MOD operator raises a run-time error if either argument is non-numeric.

In general, if you work with numerical data types in VBA, you can use the MOD operator for modulo operations. However, if you need to perform modulo operations on non-numeric data types or if you need to handle negative numbers, you can use the MOD function in Excel.

#### Example for the Excel MOD function

Consider two numbers input in two columns, as shown below. Their remainder can be calculated by using =MOD(<cell_number1>,<cell_number2>).

Resulting in the output:

### Important Things to Note

- The Mod function can be used in conditional statements to test for even or odd numbers and perform other calculations based on the remainder of a division.
- The Mod function has a lower precedence than other arithmetic operators, such as multiplication and division. It means that if you use the Mod function with other operators in a single statement, you may need to use parentheses to control the order of operations.
- The Mod function can produce a division by zero error if the divisor is zero or a null value. To avoid this error, you can add a check to ensure that the divisor is not zero before using the Mod function.
- The Mod function can be used with both positive and negative numbers. However, the result will always be positive.
- When VBA Mod with decimals is done with double-precision numbers, these rounding errors can accumulate and result in unexpected results.

For example, consider the following code:

As mentioned above, if this code is run, it will print erroneous values. In this case, 0 is printed as the answer, which is not the correct answer.

### Frequently Asked Questions (**FAQs)**

**1. What is the use of VBA MOD in Excel?**

The VBA Mod operator can be used for finding the remainder of a division operation. Here are some common uses of the VBA Mod operator in Excel:**•** **Highlighting rows or columns:** Use the VBA Mod operator to highlight particular rows or columns in an Excel worksheet.**•** **Formatting data:** You can use the VBA Mod operator to format data based on its value.**•** **Generating sequences:** You can use the VBA Mod operator to generate sequences of numbers or characters. For example, to generate a sequence of letters A, B, C, A, B, C, A, B, C, and so on, use the following VBA code structure:

Dim letter As Integer: letter = (columnNumber – 1) Mod 3 + 1: cell.Value = Chr(64 + letter).

**2. Is VBA Mod a useful function?**

Yes, the VBA Mod operator is helpful in Excel VBA programming. It is used to perform modulo-arithmetic operations. Here are some reasons why the VBA Mod operator is helpful:**•** **Data validation:** The VBA Mod operator can be used to validate data in Excel**•** **Formatting data:** It can format data based on its value.**•** **Looping:** The VBA Mod operator is often used in looping structures, such as For loops and Do…While loops.

**3. Why is the VBA Mod function not working?**

There could be several reasons if the VBA Mod operator is not working as expected in Excel. Here are some common issues that can cause problems with the Mod operator in VBA:**•** **Data type mismatch:** The VBA Mod operator only works with numerical data types, such as integers, longs, and doubles. You will get a type mismatch error if you try to use the Mod operator with a non-numeric data type, such as a string or a date.**•** **Division by zero:** If you try to divide a number by zero using the Mod operator, you will get a division by zero error.**•** **Overflow:** If the result of the Mod operation exceeds the range of the data type you are using, you will get an overflow error.

### Recommended Articles

Guide to **VBA MOD**. Here we explain how to use VBA MOD Function to calculate the remainder when dividing a number by another. You may learn more from the following articles –

## Leave a Reply