The VBA Mod Operator Explained

The VBA Mod Operator
VBA Mod vs Excel MOD
Modulo Operation Ideas
Final Thoughts

The VBA Mod operator is the VBA equivalent of the Excel MOD function. You use the VBA modulo operator to return the remainder after dividing two numbers.

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

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

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 10 and 3.

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.

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…

VBA Mod Operator Example

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.

Final Thoughts

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.

Now, please help me get the word out! Share this article with people you know on Facebook and Twitter, then subscribe using the form below for more VBA tips.

Code More, Distract Less: Support Our Ad-Free Site
You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Ultimate VBA Training Bundle. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.