How to Use the Mod Operator in VBA
Contents
Introduction
Mod stands for Modulo. It is a mathematical operator that returns the remainder, or modulus, of a number after performing division.
The Mod operator is not only helpful when you want to know the remainder in division, it is also powerful when you want to look for every nth item in a list, or when you need to generate a repeating pattern. In this article, you are going to learn how to use Mod in VBA.
Syntax
Result = Number1 Mod Number2
Where Number1
is the number you want the remainder of
Number2
is the number you want to divide by
Result
is the remainder.
e.g. 17 Mod 7 = 3
where Number1
= 17 and Number2
= 7 and Result
= 3
Using Mod in VBA Excel
In the following lines, you are going to use VBA to derive the Modulo. The result can be displayed in a message box or the immediate window of the VBE. Feel free to use any of them, but in the example, I will use the message box.
Example 1: Calculate 13 Mod 5
Step 1: Open the Visual Basic Editor in Excel (ALT + F11 or Developer tab > Click on Visual Basic icon)
Step 2: Create a new Module.
Step 3: write the following code in the Module and run it.
Sub Mod_Example1() Dim Result As Integer Result = 13 Mod 5 MsgBox "13 Mod 5 is " & Result End Sub
You should have a screenshot similar to the one below
Example 2: Working with decimals
In VBA, Mod always returns whole numbers. Even if the number involved in the operation is a decimal. Let’s see an illustration below.
Write and run the following code:
Sub Mod_Example2() Dim Result As Integer Result = 18.65 Mod 4 MsgBox Result End Sub
From the result, you must have noticed that the decimal number 18.65 has been rounded up because its decimal part is greater than 0.5. So the operation becomes 19 Mod 4 = 3. Had it been that the decimal amount was less than 0.5, the value would have been rounded down to the nearest integer.
Let’s verify whether you have understand: Guess the result of the following operation: 23.32 Mod 10. Write the appropriate code to verify your result. You can copy and paste the above code and modify the numbers.
If your code and your answer looks like the screenshot below, then congratulations!!!
Let’s try a last one.
Calculate 15.55 Mod 4.45.
Remember that numbers are rounded up to the nearest integer when the decimal part is greater or equal to 0.5 and they are rounded down to the nearest integer when the decimal part is less than 0.5.
If your answer is zero, then you’ve got it! 15.55 has been rounded up to 16 and 4.45 has been rounded down to 4 and 4 goes into 16 4 times without a remainder. That is why the modulus is 0.
The Excel Mod Function
Before we end this article it is important for you to know that there is a difference between the VBA Mod operator and the Excel Mod Function. While the VBA Mod operator rounds up/down decimal numbers before doing the operation, the Excel Mod Function does not. That is why in Excel, the result can be a decimal number unlike with VBA. Let’s look at an example to illustrate.
We shall use VBA and Excel separately to calculate 25.45 Mod 10.
Using VBA
Write and run the following procedure:
Sub Mod_Example3() Dim Result As Integer Result = 25.45 Mod 10 MsgBox Result End Sub
You should have the following screenshot:
Using Excel
In Excel, the syntax is MOD(number, divisor)
.
Don’t forget the equal sign “=” to inform Excel that it is a formula and not a string.
Conclusion
The intention of this article was to introduce you to the VBA Mod operator. I don’t have the pretention to claim that you have studied all its aspects. If you intend to use VBA Mod in more complex programming solutions, you will need to do more research to leverage on what you have studied here. Thanks for reading.