Is there any function in VBA as "IsEven"

[Excelboo2]

as title. Thanks

 

[GTO]

Hi Mike

Say, would this be a decent way to replicate the worksheet function?

Function IsEven_Maybe(val As Double) As Boolean
    IsEven_Maybe = Fix(val) Mod 2 = 0
End Function

Mark

 

[xld]

Debug.print Application.IsEven(val)

 

[GTO]

Debug.print Application.IsEven(val)

Hi Bob,

Am I missining something? That goes Kaboom(!) for me. (Object doesn't support...)

Mark

 

[xld]

Hi Bob,

Am I missining something? That goes Kaboom(!) for me. (Object doesn't support...)

Mark

Hi Mark,

How odd, it doesn't for me.

I admit, I had expected it to, and I know why. I am on Office 2007 over here and IsEven is part of ATP which has been integrated into main Excel, so it workls on 2007. I bet you are on some old antiquated version of Excel, not the nice new shiny version like me

 

[GTO]

...it workls on 2007. I bet you are on some old antiquated version of Excel, not the nice new shiny version like me

OUCH! Here I was all happy just to have 2003 at work and now I'll need counseling and a box of tissues!

Oh well, probably for the best I don't have gadzooks more rows/columns to goober up anyways

A great weekend to you and yours,

Mark

PS - a blonde moment here... ATP=???

 

[xld]

ATP - Analysis Toolpak

Have a great weekend yourself, I am travelling most of Saturday, so I won't be doing much.

 

[ZVI]

Hi,

There are some known limitations of the highlighted above methods:

1. Mod 2 is the fastest method because it interacts only with VBA object model.
But inputVal Mod 2 fails with numbers above the Long range (-2147483648 .. 2147483647) and returns the overflow error.

2. Method Evaluate("ISEVEN(" & Fix(inputVal) & ")") requires the ATP Analysis ToolPack Add-In in Excel versions prior to 2007 version. This fact can be the disadvantage for the clients because Analysis ToolPack Add-In is not installed as default.
It is slow method for VBA usage because it interacts with Excel objects using slow call of Application.Evaluate method.

3. Method Application,IsEven(inputVal) works only in Excel2007
Like Evaluate method it is slow one because it also interacts with Excel objects using slow Application call.

Here are VBA functions without such limitations:

<font face=Courier New>
Function IsEven(Num As Double) As Boolean
  IsEven = Fix(Num) / 2 - Fix(Fix(Num) / 2) = 0
End Function

Function IsOdd(Num As Double) As Boolean
  IsOdd = Fix(Num) / 2 - Fix(Fix(Num) / 2) <> 0
End Function</FONT>

Speed ratio results in Excel 2003 (PC depended):

<font face=Courier New>
1) ATP Excel - Evaluate("ISEVEN(Fix(2.5))"): 
3.484 sec      28 700 op/sec              ret = True    

2) ATP Excel2007-Application.IsEven(2.5): Not available

3) VBA - IsEven(2.5): 
0.016 sec     6 400 000 op/sec            ret = True    

------------------------------------------------------------
Speed ratios of the methods:
1) Evaluate("IsEven()")                   ratio = 1
2) Excel 2007 not found
3) VBA IsEven()                           ratio = 223
------------------------------------------------------------
</FONT>

Speed ratio results in Excel 2007 (PC depended):

<font face=Courier New>
1) ATP Excel - Evaluate("ISEVEN(Fix(2.5))"): 
7.266 sec      13 763 op/sec              ret = True    

2) ATP Excel2007 - Application.IsEven(2.5):
1.563 sec      64 000 op/sec              ret = True    

3) VBA - IsEven(2.5): 
0.016 sec     6 400 000 op/sec            ret = True    

------------------------------------------------------------
Speed ratios of the methods:
1) Evaluate("IsEven()")                   ratio = 1
2) Application.IsEven()                   ratio = 5
3) VBA IsEven()                           ratio = 465
------------------------------------------------------------
</FONT>

The testing code:

<font face=Courier New>
' ZVI:2009-07-25 http://www.mrexcel.com/forum/showthread.php?t=404266
Sub Test_IsEven_Methods()
  
  Dim t1#, t2#, t3#, i&, ret As Boolean
  
  Const N = 100000      ' <-- Amount of the test cycles
  Const TestNum = 2.5   ' <-- The testing value
  
  On Error Resume Next  ' <-- Not available method(s) will be skipped
  
  ' Method 1 - Evaluate("ISEVEN(Fix(2.5))")
  ' Fix() or Str() should be used instead of CStr() for correct working in all localization
  t1 = Timer
  For i = 1 To N
    'ret = Evaluate("ISEVEN(" & Str(TestNum) & ")")
    ret = Evaluate("ISEVEN(" & Fix(TestNum) & ")")
  Next
  t1 = Timer - t1
  
  If t1 = 0 Then t1 = 0.001  ' exclude DIV/0 error for low N
  If Err <> 0 Then
    t1 = 0
    Debug.Print "1) ATP Excel - Evaluate(""ISEVEN(Fix(" & TestNum & "))""):" _
              , "Not available" & vbLf
  Else
    Debug.Print "1) ATP Excel - Evaluate(""ISEVEN(Fix(" & TestNum & "))""): " & vbLf _
               & Format(t1, "0.000") & " sec" _
               , Format(N / t1, "# ### ##0") & " op/sec" _
               , "ret = " & ret _
               , vbLf
  End If
  
  ' Method 2 - Application.IsEven(2)
  Err.Clear
  t2 = Timer
  For i = 1 To N
    ret = Application.IsEven(TestNum)
  Next
  t2 = Timer - t2
  
  If t2 = 0 Then t2 = 0.001  ' exclude DIV/0 error for low N
  If Err <> 0 Then
    t2 = 0
    Debug.Print "2) ATP Excel2007-Application.IsEven(" & TestNum & "):" _
              , "Not available" & vbLf
  Else
    Debug.Print "2) ATP Excel2007 - Application.IsEven(" & TestNum & "):" & vbLf _
              & Format(t2, "0.000") & " sec" _
              , Format(N / t2, "# ### ##0") & " op/sec" _
              , "ret = " & ret _
              , vbLf
  End If
  
  ' Suggested VBA method 3 - IsEven(2)
  t3 = Timer
  For i = 1 To N
    ret = IsEven(TestNum)
  Next
  t3 = Timer - t3
  
  If t3 = 0 Then t3 = 0.001 ' exclude DIV/0 error for low N
  Debug.Print "3) VBA - IsEven(" & TestNum & "): " & vbLf _
             & Format(t3, "0.000") & " sec" _
             , Format(N / t3, "# ### ##0") & " op/sec" _
             , "ret = " & ret _
             , vbLf
  
  On Error GoTo 0
  
  ' Ratio
  Debug.Print String(60, "-")
  Debug.Print "Speed ratios of the methods:"
  If t1 = 0 Then
    Debug.Print "1) Analysis ToolPack not found"
  Else
    Debug.Print "1) Evaluate(""IsEven()"")", , "ratio = 1"
  End If
  If t2 = 0 Then
    Debug.Print "2) Excel 2007 not found"
  Else
    Debug.Print "2) Application.IsEven()", , "ratio = " & Format(t1 / t2, "0")
  End If
  Debug.Print "3) VBA IsEven()", , "ratio = " & Format(t1 / t3, "0")
  Debug.Print String(60, "-")

End Sub

Function IsEven(Num As Double) As Boolean
  IsEven = Fix(Num) / 2 - Fix(Fix(Num) / 2) = 0
End Function</FONT>

Regards,
Vladimir

 

[ZVI]

Surely, the suggested functions can be modified as follows for excluding of substraction and for clearer understanding of how it works:

<font face=Courier New>
Function IsEven(Num As Double) As Boolean
  IsEven = Fix(Num) / 2 = Fix(Fix(Num) / 2)
End Function

Function IsOdd(Num As Double) As Boolean
  IsOdd = Fix(Num) / 2 <> Fix(Fix(Num) / 2)
End Function</FONT>

 

[James_B]

What about just

public function IsEven(nDbl as double) as boolean
   IsEven = (nDbl / 2 = nDbl \ 2)
exit function

 

[phxsportz]

So many solutions... heh.. here's a simple minded persons approach... did I miss something ?

Public Function iseven(arg As Variant)
    myiseven = arg / 2 = Int(arg / 2)
End Function