This tutorial explains what the VBA Mod operator is, how it differs from the Excel Mod function and how you can use modulo operations to improve your macros.
Throughout the tutorial, I’ll give examples to help explain what the Mod operator is. You’ll see arithmetic examples and VBA macro examples.
The VBA Mod Operator
Mod in the Math world
Before I forget to tell you, “Mod” is short for “Modulo” or “Modulus.”
Let’s get right to it. Modulo operators return the remainder after dividing two numbers. For example, if you were to divide 10 by 3, the Mod operator would return a value of 1. Why? Let’s break it down.
The integer 3 will go into 10 three complete times (3*3=9) with 1 left over (10-9=1). This “1 left over” is known as the remainder. The remainder is 1, so the Mod operator will return a value of 1. Are you following so far? Okay, great!
Before we go any further, let’s be clear that the VBA Mod operator is not a function. It’s an arithmetic operator, just like the plus sign (+), minus sign (-), multiplication sign (*) and division sign (/).
The simplified mathematical explanation prevented in this section doesn’t exactly translate perfectly into the VBA Mod world.
Mod in the VBA world
So how do you tell VBA you want to perform a modulo operation? Fortunately, VBA has this incredibly intuitive keyword called
Mod. Remember what I said earlier about
Mod being an arithmetic operation? The way you use Mod makes sense if you remember that fact.
Sub ModDemo() x = 10 Mod 3 'x equals 1 End Sub
The first number is always divided by the second number. The keyword
Mod goes between the two numbers you want to divide, just like a multiplication sign would if you wanted to multiply
The VBA Mod operator always returns an integer! You can put decimal points in your VBA code, but
Mod will ALWAYS return an integer. You can think of it like VBA rounding both numbers before it performs a modulo operation. Let’s look at a few examples:
Sub ModDemo2() Debug.Print 5.9 Mod 4 'returns a value of 2 End Sub
In this example, 5.9 is rounded up to 6. The number 4 will go into 6 one time with 2 leftover. The Mod operator returns a value of 2.
Sub ModDemo3() Debug.Print 5.2 Mod 4 'returns a value of 1 End Sub
In this example, 5.2 is rounded down to 5. The number 4 will go into 5 one time with a remainder of 1. Therefore, the Mod operator returns a value of 1.
This is the major difference between the VBA Mod operator and the Excel MOD function. We’ll explore that topic a little deeper in the next section.
There’s one more thing you need to remember. VBA rounding uses what’s often called Banker’s Rounding. A wonderful wellsr.com visitor, aMareis, reminded me of this in a comment on this article. Banker’s rounding rounds decimals ending in a 5 to the nearest even number. Let’s use an example to illustrate why this is important:
Sub ModDemo4() Debug.Print 10.2 Mod 3.5 'returns a value of 2 End Sub
In this example, 3.5 rounds up to 4, like you would normally expect. 10.2 rounds down to 10. The number 4 will go into 10 two times with 2 leftover. The result is a value of 2.
That all makes sense, right? Well, let’s take look at a more complicated example.
Sub ModDemo5() Debug.Print 10.2 Mod 4.5 'Also returns a value of 2 End Sub
Because of banker’s rounding, the number 4.5 actually rounds down to 4, which is the nearest even number. Again, 10.2 rounds down to 10. The number 4 will go into 10 two times with 2 leftover. The result this time is also a value of 2.
That’s a little crazy, isn’t it? You would think the answer would be 1 because 4.5 goes into 10.2 two times with 1.2 left over. However, because VBA rounds your numbers to integers BEFORE performing the modulo operation and it rounds using banker’s rounding, the answer is two.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
VBA Mod vs Excel MOD
I’ve already told you the VBA Mod operator only returns integers. The Excel MOD function, on the other hand, actually does return floating numbers.
Where the VBA statement
5.2 Mod 4 returns an integer of 1, the Excel MOD function,
=MOD(5.2,4), returns a floating point answer of 1.2.
If you’re used to the Excel MOD function, it’s vitally important that you remember this difference when you start using the VBA Mod operator!
Notice how I’m calling the Excel MOD function a “function,” but I’m calling the VBA Mod operator an “operator.” That’s simply based on the syntax of the two tools. A function in Excel starts with an equal sign (=), but an operator in VBA performs an operation on two numbers, usually in the form of
number1 operator number2
If you want to use a VBA function that mimics the results of the Excel Mod function, you’ll need to create your own function. Shockingly, the Application.WorksheetFunction property doesn’t have the Excel MOD function! Instead, you can paste a function like this in your VBA editor:
Function XLMod(a, b) ' This attempts to mimic the Excel MOD function XLMod = a - b * Int(a / b) End Function
When you include your own XLMod function, you can actually return a decimal number result, like this:
Sub ModDemo6() Debug.Print XLMod(5.2, 4) 'returns a value of 1.2 End Sub
Modulo Operation Ideas
So how can you actually use the Mod operator, or any modulo operation, in your own VBA programs? I mostly use them when I’m processing files or spreadsheet data in a fixed format.
To give a basic example, let’s say you want to multiply all values in a column by 100 except every 5th value. There are many ways you can do this, but I’m going to demonstrate how to do it with the VBA Mod operator.
Sub VBA_Mod_Example() Dim i As Long, lastrow As Long lastrow = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To lastrow If i Mod 5 <> 0 Then 'ignores every 5th row Range("A" & i) = Range("A" & i) * 100 End If Next i End Sub
Run this macro and every cell in column A will be multiplied by 100, except for 5, 10, 15, 20…
I use programs like this all the time when reading data from text files or CSV files. It’s a really convenient way to process a file when you know how the data is structured.
If headers are presented every 50th line, you can use the VBA Mod operator to ignore every 50th line instead of performing a string operation to check if it’s a header. The Mod operator may not be as robust, but it’s perfectly fine if you know how your data is formatted.
I hope you learned something today. Many years ago, I used to avoid the Mod operator because I couldn’t remember which number was supposed to go first. Use this page as a reference if you forget and soon you’ll get the hang of it, too! It really is an underrated operator.
If you want to support wellsr.com, please check out the products in my Excel VBA Store. There, you’ll find add-ins, hardware and office products to make you look even better at Excel.
Reach out to my VBA Consulting page if you’re working on a macro, but you can’t quite figure out how to finish it. I’ll be happy to help you out!