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.