Determine if Integer is Odd or Even

Excel version: 11 (2003 SP2)
OS: WXP SP2

What's the VBA code equivalent of the Excel IsOdd built-in formula?

It would great if one could use Application.WorksheetFunction.IsOdd(range), but the IsOdd Excel built-in is not available in VBA.

Many thanks,

- Al

Could you use something related the MOD function?

"GoFigure" <[email protected]> wrote in
message news:[email protected]...
>
> Excel version: 11 (2003 SP2)
> OS: WXP SP2
>
> What's the VBA code equivalent of the Excel IsOdd built-in formula?
>
> It would great if one could use
> Application.WorksheetFunction.IsOdd(range), but the IsOdd Excel
> built-in is not available in VBA.
>
> Many thanks,
>
> - Al
>
>
> --
> GoFigure
> ------------------------------------------------------------------------
> GoFigure's Profile:
> http://www.excelforum.com/member.php...fo&userid=4274
> View this thread: http://www.excelforum.com/showthread...hreadid=492937
>

Hello GoFigure,

This is a simple test. AND a number with 1 and test the result. It will be TRUE if odd and FLASE if even.

Here is the UDF:

Function IsOdd(ByRef Rng As Range) As Boolean
IsOdd = Rng.Value AND 1
End Function

Sincerely,
Leith ross

The IsOdd () function is in the Analysis Toolpack
- see http://snipurl.com/kqja for a recent thread on
how to access these functions in VBA.

As others have said, though, it's very easy to do
it in plain VBA. My personal preference would be
to use the Mod operator:

Function myIsOdd (i as integer) as Boolean
myIsOdd = (i mod 2 = 1)
End function

Andrew Taylor


GoFigure wrote:
> Excel version: 11 (2003 SP2)
> OS: WXP SP2
>
> What's the VBA code equivalent of the Excel IsOdd built-in formula?
>
> It would great if one could use
> Application.WorksheetFunction.IsOdd(range), but the IsOdd Excel
> built-in is not available in VBA.
>
> Many thanks,
>
> - Al
>
>
> --
> GoFigure
> ------------------------------------------------------------------------
> GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274
> View this thread: http://www.excelforum.com/showthread...hreadid=492937

Thanks everyone. Using the MOD function works great for what I need. I'll make a note of the AND test, too.

If you will encounter negative numbers, you might want to modify it to

Function myIsOdd(i As Integer) As Boolean
myIsOdd = (Abs(i Mod 2) = 1)
End Function

or use Leith Ross's "AND" approach.

--
Regards,
Tom Ogilvy


"GoFigure" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks everyone. Using the MOD function works great.
>
>
> --
> GoFigure
> ------------------------------------------------------------------------
> GoFigure's Profile:
http://www.excelforum.com/member.php...fo&userid=4274
> View this thread: http://www.excelforum.com/showthread...hreadid=492937
>

Leith Ross wrote:
> Hello GoFigure,
>
> This is a simple test. AND a number with 1 and test the result. It will
> be TRUE if odd and FLASE if even.
>
> Here is the UDF:
>
> Function IsOdd(ByRef Rng As Range) As Boolean
> IsOdd = Rng.Value AND 1
> End Function
>
> Sincerely,
> Leith ross
>
>
---------------------------

That's kind of a risky thing to do. Excel is not defined to work that way,
though you've plainly found that it does. The risk is that the next upgrade of
Excel may change its internal behavior and make all your old spreadsheets die in
difficult ways to find.

Computer science is full of such "gottchas" over the years.

Good luck...

Bill

VBA is certainly designed to work that way. Where do you get your
information?


From Excel VBA help on AND
=========================
The And operator also performs a bitwise comparison of identically
positioned bits in two numeric expressions and sets the corresponding bit in
result according to the following table:

=========================



So AND, OR, XOR, EQV, IMP, NOT are all bitwise operators.

--

Regards,

Tom Ogilvy





"Bill Martin" <[email protected]> wrote in message
news:[email protected]...
> Leith Ross wrote:
> > Hello GoFigure,
> >
> > This is a simple test. AND a number with 1 and test the result. It will
> > be TRUE if odd and FLASE if even.
> >
> > Here is the UDF:
> >
> > Function IsOdd(ByRef Rng As Range) As Boolean
> > IsOdd = Rng.Value AND 1
> > End Function
> >
> > Sincerely,
> > Leith ross
> >
> >
> ---------------------------
>
> That's kind of a risky thing to do. Excel is not defined to work that
way,
> though you've plainly found that it does. The risk is that the next
upgrade of
> Excel may change its internal behavior and make all your old spreadsheets
die in
> difficult ways to find.
>
> Computer science is full of such "gottchas" over the years.
>
> Good luck...
>
> Bill

Good point - I always forget Mod's ("odd" IMHO) behaviour with
negative numbers. For ultra-terseness (which I don't necessarily
recommend...), you can do

myIsOdd = CBool(i Mod 2)

(or even omit the CBool, as the function returns a Boolean)

ndrew


Tom Ogilvy wrote:
> If you will encounter negative numbers, you might want to modify it to
>
> Function myIsOdd(i As Integer) As Boolean
> myIsOdd = (Abs(i Mod 2) = 1)
> End Function
>
> or use Leith Ross's "AND" approach.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "GoFigure" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Thanks everyone. Using the MOD function works great.
> >
> >
> > --
> > GoFigure
> > ------------------------------------------------------------------------
> > GoFigure's Profile:
> http://www.excelforum.com/member.php...fo&userid=4274
> > View this thread: http://www.excelforum.com/showthread...hreadid=492937
> >