Using MOD in Excel (and VBA)Learn what MOD is and what it does in Excel (and VBA)
There are tons of functions in Excel that can you can use to help make things easier.
Among these functions are those that perform mathematical equations.
One such function is the MOD function.
What this function does is return the remainder after dividing two numbers (the dividend and the divisor).
The MOD function can also be used in VBA.
But rather than a function, MOD is an operator in VBA.
In this article, we’ll be taking a look at how the MOD function works in Excel.
What is the MOD Function and What Does it Do?
The MOD function returns the remainder after dividing two numbers (the dividend and the divisor).
To illustrate, here’s an example:
To use the MOD function, you will have to enter this formula:
=MOD(dividend,divisor)
Where:
dividend – this refers to the number that you’ll be dividing from. This is typically the larger number
divisor – this refers to the number that you’ll be dividing by. This is typically the smaller number
Things to note when using MOD
The result of the MOD function can either be negative or positive. The sign (-/+) of the result will follow the sign of the divisor.
Two error values may appear depending on the circumstances. If the divisor is 0, the MOD function will return a #DIV/0! error value. This is because dividing by 0 is impossible. You’ll need to use a divisor that is not 0.
If any of the parameters (dividend, divisor) is a text value, the MOD function will return a #VALUE! error value. This is because you need number values for the MOD function to work properly.
MOD in VBA
You can also use MOD in VBA. But rather than a function, MOD works as an operator in VBA.
That said, it still works the same as the MOD function.
It divides two numbers, the dividend and the divisor. It then returns the remainder as the result. The code for using MOD in VBA is as follows
Sub name()
Range(range) = dividend Mod divisor
End Sub
Where:
name – description of the code
range – refers to the cell where the result of MOD will be displayed
dividend – refers to the number that you’ll be dividing from. This is typically the larger number
divisor – refers to the number that you’ll be dividing by. This is typically the smaller number
For example, suppose I want to show the remainder when dividing 17 by 3 in cell A1, the code I’ll be using in VBA will be this:
As you can, the remainder (which is 2) now appears in cell A1.
Appear in a message box
You can also make the result appear in a message box rather than a cell. To do so, you’ll have to use this code:
Sub name()
MsgBox dividend Mod divisor
End Sub
Where:
name – description of the code
dividend – refers to the number that you’ll be dividing from. This is typically the larger number
divisor – refers to the number that you’ll be dividing by. This is typically the smaller number
Let’s try it. Suppose I want to show the remainder when dividing 21 by 6 in a message box. This is the code that I’ll use:
Now when I run this code, a message box should appear in Excel which will show the result:
Things to note
If you try to use MOD in VBA with a divisor that is zero, a run-time error ‘11’ will occur. This is because dividing by zero is impossible.
If you try to use MOD in VBA where either the divisor or dividend isn’t a number, a run-time error ‘13’ will occur.
This is because there is a type mismatch. For MOD to work properly, the dividend and divisor must be numbers.
Conclusion
In this article, we have discussed the MOD function and what it does in Excel.
We also discussed how it operates in VBA.
Do you now have a deeper understanding of how MOD works?
Let me know in the comments.