VBA MOD

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.

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 If
End 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 ” & num2
Else
MsgBox num1 & ” is not divisible by ” & num2
End If
End 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 Range
Dim cell As Range
Dim counter As Integer
Set dateRange = Range(“A1:A10”)
counter = 0
For Each cell In dateRange
counter = counter + 1
If counter Mod 3 = 0 Then
cell.Interior.ColorIndex = 7
End If
Next cell
End 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)

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 –

• VBA Operators
• VBA Variable Types
• VBA 1004 Error