In this article
- Syntax and Basic Mechanics
- Real-World VBA Use Cases
- 1. Highlighting Alternating Rows (Zebra Striping Without Conditional Formatting)
- 2. Batch Processing Every Nth Row (e.g., Progress Logs)
- Frequently Asked Questions
- What happens when dividing by zero with Mod in VBA?
- Does VBA Mod work with decimal numbers?
- Is Mod faster than nested IF checks?
- Practical VBA workshop
- What you will build
- Complete VBA example
- How the solution works
- Run it step by step
- Common mistakes
- Make it production-ready
- Practice challenge
- Download the practice pack
- Continue learning
The Mod operator in Visual Basic for Applications (VBA) divides two numbers and returns only the remainder. While simple in concept, the Mod operator is one of the most versatile tools in macro development, powering alternating row highlighting (zebra striping), batching records into groups, and creating cyclic counters.
Syntax and Basic Mechanics
' Syntax: Result = number1 Mod number2
Dim remainder As Long
remainder = 10 Mod 3 ' Returns 1 because 10 = (3 * 3) + 1
remainder = 12 Mod 4 ' Returns 0 (12 is cleanly divisible by 4)Unlike standard division (/) which returns floating-point quotients or integer division () which truncates the remainder, Mod specifically tracks what is left over after full multiples are removed.
Real-World VBA Use Cases
1. Highlighting Alternating Rows (Zebra Striping Without Conditional Formatting)
Sub ApplyZebraStriping()
Dim ws As Worksheet
Dim r As Long, lastRow As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
If r Mod 2 = 0 Then
ws.Rows(r).Interior.Color = RGB(245, 247, 250)
Else
ws.Rows(r).Interior.Color = RGB(255, 255, 255)
End If
Next r
End Sub2. Batch Processing Every Nth Row (e.g., Progress Logs)
Sub ProcessLargeDataset()
Dim i As Long
For i = 1 To 50000
' Execute processing logic here...
' Update status bar every 1,000 iterations to avoid UI lag
If i Mod 1000 = 0 Then
Application.StatusBar = "Processing record " & i & " of 50,000..."
DoEvents
End If
Next i
Application.StatusBar = False
End SubFrequently Asked Questions
What happens when dividing by zero with Mod in VBA?
If the divisor (the second argument) is zero, VBA raises a runtime error 11: Division by zero. Always validate that your divisor is non-zero before applying Mod.
Does VBA Mod work with decimal numbers?
In VBA, floating-point arguments passed to Mod are automatically rounded to the nearest integer before the modulo operation. If you need precise floating-point remainder calculations, use d - Fix(d / divisor) * divisor.
Is Mod faster than nested IF checks?
Yes, integer modulo arithmetic executes at the hardware CPU level in a few clock cycles, making it orders of magnitude faster than evaluating string or lookup conditions inside loops.
📖 Continue your macro journey in our Complete Excel VBA Course or review Formula Combos.
Practical VBA workshop
Goal: use Mod for repeating patterns, grouping and periodic actions.
A report needs alternating row groups and a marker after every fifth transaction.
What you will build
- A complete procedure that works without Select or Activate.
- Test data and expected behaviour you can verify before using real files.
- Error cases and production checks that prevent silent damage.
Complete VBA example
Option Explicit
Public Sub MarkPatterns()
Dim ws As Worksheet, lastRow As Long, r As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
If (r - 2) Mod 2 = 0 Then ws.Rows(r).Interior.Color = RGB(242, 247, 252)
If (r - 1) Mod 5 = 0 Then ws.Cells(r, "D").Value = "5th item"
Next r
End SubHow the solution works
- a Mod b returns the remainder after integer division, so a result of zero marks an exact cycle boundary.
- Subtract the starting row before applying Mod when the pattern should begin at zero for the first data row.
- Mod is useful for batching API calls, page breaks, rotating assignments and weekday cycles—not only odd/even tests.
Run it step by step
- Download and unzip the practice pack below. Open the matching CSV or copy it into a new macro-enabled workbook.
- Press Alt + F11, choose File → Import File, and import this chapter’s
.basmodule. - Rename the sample worksheet to the name used in the code, or change the worksheet name in one clearly marked line.
- Run the procedure with F5. Compare the result with the expected values and boundary cases in the CSV.
- Undo is not available after a macro changes a workbook. Test on a copy, save only after checking totals, and keep the original source file.
Common mistakes
- Using the worksheet row number directly can shift the pattern when headers are inserted.
- A divisor of zero raises an error; validate user-supplied cycle lengths.
- Negative operands can produce results that surprise users; document the expected input range.
Make it production-ready
Add Option Explicit, qualify every workbook and worksheet, validate the last row and input types, and use a single cleanup section to restore ScreenUpdating, EnableEvents and calculation settings after an error. Avoid hard-coded regulatory rates or colour-only business rules. Record the source, effective date and assumptions in a visible Notes sheet.
Practice challenge
Assign rows round-robin to Team A, B and C using Mod, then add a page break after every 25 data rows.
Test normal data, blanks, text in numeric columns, duplicate values and the exact threshold boundaries. A useful macro should fail clearly rather than produce a plausible but wrong answer.
Download the practice pack
Download VBA Chapters 13–23 practice pack (.zip)
The pack contains an importable VBA module and UTF-8 CSV test data for every chapter. Review code before enabling macros.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
Stuck on a step? Ask a question and the AI answers using this article.