Using Worksheet Function MOD in VBA

[Wookiee]

I have a bit of code I'm working on that loops through a defined number of columns and should change the value in every cell in Column A which is stored in an odd-numbered row. But when I tried to use the MOD function in VBA, I get a run-time error (object doesn't support this property or method). I could not find a VBA equivalent of the worksheet formula MOD, so I'm at a loss for how to proceed.

Here's my code:

For lngLastRow = 2 To 29150
    If WorksheetFunction.Mod(lngLastRow, 2) = 1 Then Range("A" & lngLastRow) = "Hello, world"
Next lngLastRow

Can anyone offer a suggestion or workaround?

Many thanks!

 

[Firefly2012]

Try:

If lngLastRow Mod 2 = 1 Then Range("A" & lngLastRow) = "Hello, world"

 

[Wookiee]

Many thanks, Firefly! That did the trick. I also realized that there was an IsOdd worksheet function that I could use as well, but my problem was I was looking for it as a VBA function rather than a worksheet function. When it comes to functions I don't regularly use, I still have issues remembering whether (or in what manner) a function can be used in VBA.

 

[Firefly2012]

Mod is an operator in VBA rather than a function which may explain why you couldn't locate it

 

[Rick Rothstein]

ADVERTISEMENT

Many thanks, Firefly! That did the trick. I also realized that there was an IsOdd worksheet function that I could use as well, but my problem was I was looking for it as a VBA function rather than a worksheet function. When it comes to functions I don't regularly use, I still have issues remembering whether (or in what manner) a function can be used in VBA.

As Firefly showed you, Mod in VBA is not a function... it is an operator (like +, -, etc.). While it does not matter to your intended use (to see if a number is odd or not), you should be aware for future projects that VBA's Mod operator only works the same as the worksheet's MOD function when the number and the modulus are whole numbers. The VBA Mod operator rounds fractions (using Banker's Rounding) before calculating the remainder after dividing the number by the modulus (that is what Mod and MOD do... return remainders after dividing) whereas the worksheet MOD function does not round the numbers at all (which is why you can retrieve decimal part of a floating point number by using 1 as the modulus; for example, =MOD(1.234,1) returns 0.234 on the worksheet where as (1.234 Mod 1) returns 1 in VBA. If you need to use the worksheet's MOD functionality inside VBA code, you can use the Evaluate function to do so. So, to retrieve the fractional part of 1.234 in VB, you could do this...

Number = 1.234
Modulus = 1
DecimalPart = Evaluate("MOD(" & Number & "," & Modulus & ")")

Although most VB's would use (Number - Int(Number)) to do that. However, if you need the other worksheet MOD function's ability to with floating point numbers, just use the Evaluate function structure I show above.

By the way, here is another VBA method of determing if a number is odd or not...

Number = 123
If Number Like "*[13579]" Then
  MsgBox "The number is odd" 
Else
  MsgBox "The number is even"
End If

 

[ads4go]

Be careful! In VBA, the Mod function rounds decimals: "3.4 Mod 2" => 1 whereas the Worksheet Function does not round "=MOD(3.4, 2)" => 1.4

 

[Fluff]

Hi & welcome to MrExcel.

Be careful! In VBA, the Mod function rounds decimals:

Which exactly what Rick explained back in 2013.