🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Mod in Excel — The Remainder Operator, Negatives, and the Sign Trap

|

VBA Mod in Excel — The Remainder Operator, Negatives, and the Sign Trap

TL;DR — Mod gives you the remainder after integer division: 7 Mod 3 is 1. It is an operator, not a function, so you write a Mod b with no brackets. Two things surprise people: VBA rounds both operands to whole numbers first (6.7 Mod 3 behaves like 7 Mod 3), and the result takes the sign of the dividend — the left number — so -7 Mod 3 is -1, not the worksheet's 2.

Sub RemaindersAndTheSignTrap()
    Debug.Print 7 Mod 3      ' 1   - remainder of 7 / 3
    Debug.Print 10 Mod 2     ' 0   - divides evenly, so "is 10 even?" -> True
    Debug.Print -7 Mod 3     ' -1  - sign follows the DIVIDEND (-7), not the divisor
    Debug.Print 6.7 Mod 3    ' 1   - 6.7 is rounded to 7 first, then 7 Mod 3
End Sub

Mod, Round and Rnd are three math tools that look like their worksheet cousins but quietly behave differently. Mod is the one people port straight from a cell formula into a macro and then cannot understand why the negatives came out wrong.

What you'll learn

  • The mental model — Mod is the remainder after integer division, and it is an operator
  • Why VBA rounds the operands to whole numbers before it divides
  • The number-one bug — -7 Mod 3 is -1 in VBA but 2 on the worksheet, because the sign rule differs
  • The everyday idioms — is-even, every Nth row, cycling through a list
  • How to force an always-positive result, and why x Mod 0 raises a run-time error

The mental model: the remainder after integer division

Division gives you two things — how many times one number fits into another, and what is left over. Mod hands you the left over. 17 Mod 5 is 2, because 5 goes into 17 three times with 2 to spare. That is the entire idea, and it is why Mod is everywhere a pattern repeats: a remainder of 0 means "divides evenly," which is how you test is this even, is this the Nth row, have we filled a full group.

Two properties make VBA's Mod its own thing. First, it is an operator, like + or *, not a function — you write a Mod b, never Mod(a, b). Second, it works on whole numbers: if you hand it decimals, VBA rounds each operand to an integer before taking the remainder.

Debug.Print 6.7 Mod 3     ' 1   - 6.7 rounds to 7, then 7 Mod 3 = 1  (not 0.7)
Debug.Print 5.4 Mod 2     ' 1   - 5.4 rounds to 5, then 5 Mod 2 = 1

If you genuinely need a fractional remainder, Mod will not give it to you — compute it yourself with a - Int(a / b) * b. But nine times out of ten Mod is exactly what you want, precisely because it deals in whole counts.

The number-one bug: negatives change sign

This is the one that fills forums. You have a formula that works on the sheet, you paste the same arithmetic into VBA, and the negatives come out with the wrong sign:

Debug.Print -7 Mod 3      ' VBA:            -1
' =MOD(-7, 3) on the sheet gives:            2

Both are "correct" — they follow different, documented rules. VBA's Mod takes the sign of the dividend (the left operand): -7 Mod 3 is -1, 7 Mod -3 is 1. The worksheet's MOD takes the sign of the divisor: MOD(-7, 3) is 2. So a formula you trusted in a cell can silently produce a different number the instant you move it into a macro, and nothing warns you.

If what you actually want is a result that is never negative — the usual case when you are wrapping an index around a list — do not fight the sign rule, neutralise it:

' Always lands in 0 .. b-1, whatever the sign of a
result = ((a Mod b) + b) Mod b
Debug.Print ((-7 Mod 3) + 3) Mod 3     ' 2   - the worksheet's answer, on purpose

The lesson is not "VBA is wrong." It is: when negatives can appear, decide the sign you want and build it — do not assume VBA's Mod matches the cell you copied from.

The idioms worth memorising

Most real uses of Mod are one of a handful of patterns. Learn these and you will recognise them everywhere:

If n Mod 2 = 0 Then ...              ' is n even?  (odd = 1)
If i Mod 3 = 0 Then ...              ' every 3rd item (banding rows, batching)
colour = palette(i Mod paletteCount) ' cycle through a fixed list, wrapping around

The row-banding case is where Mod earns its keep inside a loop: shading every third row, inserting a separator every tenth record, alternating a colour with i Mod 2. Each is just "is the remainder zero?" — the same test wearing different clothes.

The edge that errors: dividing by zero

One case does not fail quietly — it stops the macro. x Mod 0 raises run-time error 11, "Division by zero," exactly as x / 0 would, because the remainder is undefined when there is nothing to divide by. If the divisor can be zero (a count that might be empty, a user-supplied step), guard it first:

If groupSize > 0 Then
    If i Mod groupSize = 0 Then InsertSeparator i
End If

A related edge is size: Mod works within VBA's integer types, so extremely large operands typed as Integer or Long can overflow. If you are working with values beyond about two billion, type them as LongLong (64-bit VBA) or Double and let VBA round to a whole number.

How ExcelMaster helps

Mod looks like the simplest operator in VBA, and it hides three traps that all fail without a warning: a negative dividend flips the sign away from the worksheet you copied the formula from, a decimal operand is silently rounded before the division, and a divisor that slips to zero stops the whole macro with error 11.

ExcelMaster lets you describe the pattern — "insert a subtotal after every 12 rows" or "flag the odd-numbered invoices" — and it writes the remainder test the right way: it wraps with ((a Mod b) + b) Mod b when you need a non-negative result, keeps the operands whole on purpose, and guards the divisor so an empty group never crashes the run. You keep the workbook and the code.

Frequently asked questions

Why does -7 Mod 3 give -1 in VBA but 2 on the worksheet?

Because the two use different sign rules. VBA's Mod operator takes the sign of the dividend (the left operand), so -7 Mod 3 is -1. The worksheet's MOD function takes the sign of the divisor, so MOD(-7, 3) is 2. To get the worksheet's always-positive answer in VBA, write ((a Mod b) + b) Mod b.

Is Mod a function or an operator in VBA?

It is an operator, like + or *. You write a Mod b, not Mod(a, b) — there are no brackets and no comma. It sits in an expression and returns the remainder of the integer division of a by b.

Does VBA Mod work with decimals?

Not directly — VBA rounds both operands to whole numbers before taking the remainder, so 6.7 Mod 3 behaves like 7 Mod 3 and returns 1. If you need a true fractional remainder, compute it yourself with a - Int(a / b) * b.

How do I check if a number is even or odd in VBA?

Use the remainder against 2: If n Mod 2 = 0 is true for even numbers and false (remainder 1) for odd ones. The same pattern, i Mod k = 0, tests "every kth" for any k — every third row, every tenth record, and so on.

What happens if I use Mod with zero?

x Mod 0 raises run-time error 11, "Division by zero," because the remainder is undefined with no divisor. If the divisor might be zero — an empty count or a user-supplied step — check it with If divisor > 0 Then before using Mod.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-18.

Related guides: VBA Round · VBA Rnd · VBA For Loop · VBA If Then Else · VBA WorksheetFunction