CODEWITHSUNDEEP
excelvbaexcel-2007modulo
Arlen Beiler
Arlen Beiler

Reputation: 15906

VBA equivalent to Excel's mod function

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

Upvotes: 41

Views: 231038

Answers (9)

Carlo
Carlo

Reputation: 91

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

Upvotes: 9

LeasMaps
LeasMaps

Reputation: 302

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

Upvotes: 0

Riccardo La Marca
Riccardo La Marca

Reputation: 895

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.

Upvotes: 2

egrunin
egrunin

Reputation: 25083

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).

Upvotes: 37

Rhys
Rhys

Reputation: 603

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

Upvotes: 0

Stu
Stu

Reputation: 77

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))

Upvotes: -2

Michael Wilson
Michael Wilson

Reputation: 109

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

Upvotes: 10

LukeH
LukeH

Reputation: 269578

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

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

Upvotes: 3

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103145

In vba the function is MOD. e.g

 5 MOD 2

Here is a useful link.

Upvotes: 60

Related Questions