VBA equivalent to Excel's mod function

Question

Is there a vba equivalent to excel's mod function?

score 60 · Answer 1 · answered Dec 07 '10 at 15:02

60

In vba the function is MOD. e.g

 5 MOD 2

Here is a useful link.

answered Dec 07 '10 at 15:02

Vincent Ramdhanie

102,349
23
137
192

6

+1 the VBA language reference page for `Mod` is [here](http://msdn.microsoft.com/en-us/library/office/gg264708%28v=office.14%29.aspx) – barrowc Jul 25 '13 at 21:39
1

The mod operator is not equivalent because it cannot return floating precision, unlike the excel function. – johnzilla Sep 21 '16 at 15:35
3

Be aware that the Excel MOD function calculates differently than the VBA Mod operator when negative numbers are used. I just discovered this with simple integers. I expected to see more explanation of this issue on this page. In VBA `11 Mod -5 = 1` where as Excel's formula `MOD(11,-5) = -4` In my case I decided to use the XLMod() function shown on this page instead of the Mod VBA operator. – Ben Aug 20 '18 at 22:03

score 37 · Accepted Answer · edited Apr 06 '18 at 10:40

37

You want the mod operator.

The expression a Mod b is equivalent to the following formula:

a - (b * (a \ b))

Edited to add:

There are some special cases you may have to consider, because Excel is using floating point math (and returns a float), which the VBA function returns an integer. Because of this, using mod with floating-point numbers may require extra attention:

Excel's results may not correspond exactly with what you would predict; this is covered briefly here (see topmost answer) and at great length here.
As @André points out in the comments, negative numbers may round in the opposite direction from what you expect. The Fix() function he suggests is explained here (MSDN).

edited Apr 06 '18 at 10:40

Dave Pile

5,559
3
34
49

answered Dec 07 '10 at 15:02

egrunin

24,650
8
50
93

4

You'd better change `(a \ b)` for `Fix(a / b)`. Otherwise, you may have problems with decimal arguments. Try your formula with `a = 1.75` and `b = 1` and you'll see my point. – André Jul 25 '13 at 16:01
1

The formula suggested is wrong and will always produce 0 as answer – PSVSupporter Oct 07 '16 at 08:45
2

@PSVSupporter I think you're confusing the ` \ ` operator with `/`. They're different. – egrunin Oct 08 '16 at 15:56
1

\ and mod are both 32 bit only. / and int seem to work on double. – Tuntable Oct 07 '17 at 06:04

score 10 · Answer 3 · edited Mar 01 '12 at 19:53

10

My way to replicate Excel's MOD(a,b) in VBA is to use XLMod(a,b) in VBA where you include the function:

Function XLMod(a, b)
    ' This replicates the Excel MOD function
    XLMod = a - b * Int(a / b)
End Function

in your VBA Module

edited Mar 01 '12 at 19:53

Sergey Glotov

20,200
11
84
98

answered Mar 01 '12 at 19:41

Michael Wilson

109
1
2

score 9 · Answer 4 · answered Aug 05 '12 at 11:12

9

Be very careful with the Excel MOD(a,b) function and the VBA a Mod b operator. Excel returns a floating point result and VBA an integer.

In Excel =Mod(90.123,90) returns 0.123000000000005 instead of 0.123 In VBA 90.123 Mod 90 returns 0

They are certainly not equivalent!

Equivalent are: In Excel: =Round(Mod(90.123,90),3) returning 0.123 and In VBA: ((90.123 * 1000) Mod 90000)/1000 returning also 0.123

answered Aug 05 '12 at 11:12

Carlo

91
1
2

1

There are many warnings about floating point results. +1 for providing an example. – pjvleeuwen Jan 26 '19 at 19:43

LukeH · Answer 5 · 2010-12-07T15:07:40.763

3

The Mod operator, is roughly equivalent to the MOD function:

number Mod divisor is roughly equivalent to MOD(number, divisor).

edited Dec 07 '10 at 15:07

answered Dec 07 '10 at 15:02

LukeH

263,068
57
365
409

Riccardo La Marca · Answer 6 · 2018-05-20T17:30:31.000

2

Function Remainder(Dividend As Variant, Divisor As Variant) As Variant
    Remainder = Dividend - Divisor * Int(Dividend / Divisor)
End Function

This function always works and is the exact copy of the Excel function.

edited May 20 '18 at 17:30

answered May 17 '18 at 14:47

Riccardo La Marca

775
5
15

While this answer is probably correct and useful, it is preferred if you include some explanation along with it to explain how it helps to solve the problem. This becomes especially useful in the future, if there is a change (possibly unrelated) that causes it to stop working and users need to understand how it once worked. – Erty Seidohl May 17 '18 at 21:28

score 0 · Answer 7 · edited Jun 28 '17 at 14:48

0

But if you just want to tell the difference between an odd iteration and an even iteration, this works just fine:

If i Mod 2 > 0 then 'this is an odd 
   'Do Something
Else 'it is even
   'Do Something Else
End If

edited Jun 28 '17 at 14:48

M--

25,431
8
61
93

answered Feb 06 '17 at 21:35

Rhys

574
6
11

LeasMaps · Answer 8 · 2022-03-03T03:36:32.207

0

I've found this to be the most reliable if excel's mod function is also giving incorrect results:

Originally even XLMod = (a - (b * Int(a / b))) was giving me incorrect results so..

Function XLMod(a, b As Long)
' This replicates the Excel MOD function - the VBA mod function returns an integer

Dim templong As Long

  templong = (b * Int(a / b))
  XLMod = (a - templong)
End Function

edited Mar 03 '22 at 03:36

answered Mar 03 '22 at 03:27

LeasMaps

300
4
14

1

Var type has to be declared for each variable. Parameter `a` will be a variant in this case. Double might also be preferable. – Filcuk Mar 11 '22 at 09:44

Stu · Answer 9 · 2016-06-30T17:27:21.683

-1

The top answer is actually wrong.

The suggested equation: a - (b * (a \ b))

Will solve to: a - a

Which is of course 0 in all cases.

The correct equation is:

a - (b * INT(a \ b))

Or, if the number (a) can be negative, use this:

a - (b * FIX(a \ b))

edited Jun 30 '16 at 17:27

answered Jun 30 '16 at 17:22

Stu

61
1
2
6

4

\ operator is not the same as / operator \ The result is the integer quotient of expression1 divided by expression2, which discards any remainder and retains only the integer portion. This is known as truncation. The / Operator (Visual Basic) returns the full quotient, which retains the remainder in the fractional portion. https://msdn.microsoft.com/en-us/library/0e16fywh.aspx – Cheeky Charlie Jul 18 '16 at 11:42
-1 As explained by Cheeky Charlie this answer is misleading, based on an incorrect assumption; if you aren't convinced, try entering _Msgbox 3 - (2 * (3 \ 2))_ in the Immediate window in the VBA editor, which returns 1 as expected from the modulo function instead of 0 as posited by this poster. – pbeentje Feb 22 '19 at 14:36

VBA equivalent to Excel's mod function

9 Answers9

I've found this to be the most reliable if excel's mod function is also giving incorrect results:

Linked

Related