Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban

Alan -

I always do it this way:

Sub abtest4()
Dim x
x = 4
Select Case x
Case 2, 4
MsgBox x
End Select
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Alan Beban" wrote in message
...
Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban

Sub abtest4()
Dim x As Long
x = 4
Select Case x
Case 2, 4
MsgBox x
End Select
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Alan Beban"
wrote in message
Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban

Jon Peltier wrote:
Alan -

I always do it this way:

Sub abtest4()
Dim x
x = 4
Select Case x
Case 2, 4
MsgBox x
End Select
End Sub


Thanks.

Alan Beban

Jim Cone wrote:

Sub abtest4()
Dim x As Long
x = 4
Select Case x
Case 2, 4
MsgBox x
End Select
End Sub

Thanks.

Alan Beban

Did that work Alan?
I noticed that msgboxs really work best when they are strings or converted
to a string strconv(etc, etc)

Mark

"Alan Beban" wrote:

Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban

ML wrote:
Did that work Alan?
I noticed that msgboxs really work best when they are strings or converted
to a string strconv(etc, etc)

Mark

"Alan Beban" wrote:


Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban


Yes, it was not a message box problem. It worked when "Case Is 2 Or 4"
was replaced with "Case 2, 4". I'm still curious why while it didn't
throw a compile error (i.e., the syntax seems OK), it still didn't work
the way I posted it.

Thanks for your interest.

Alan Beban

Alan -

I would rely on my default answer: "Sometimes Excel is like that." I say it
thoughtfully while stroking my beard.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Alan Beban" wrote in message
...
ML wrote:
Did that work Alan? I noticed that msgboxs really work best when they are
strings or converted to a string strconv(etc, etc)

Mark

"Alan Beban" wrote:


Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban


Yes, it was not a message box problem. It worked when "Case Is 2 Or 4" was
replaced with "Case 2, 4". I'm still curious why while it didn't throw a
compile error (i.e., the syntax seems OK), it still didn't work the way I
posted it.

Thanks for your interest.

Alan Beban

By God, I think you've got it!

Alan

Jon Peltier wrote:
Alan -

I would rely on my default answer: "Sometimes Excel is like that." I say it
thoughtfully while stroking my beard.

- Jon
Alan Beban


Yes, it was not a message box problem. It worked when "Case Is 2 Or 4" was
replaced with "Case 2, 4". I'm still curious why while it didn't throw a
compile error (i.e., the syntax seems OK), it still didn't work the way I
posted it.

Thanks for your interest.

Alan Beban

This syntax also works in Excel 2000:

Sub abtest4()
x = 4
Select Case x
Case Is = 2, Is = 4
MsgBox x
End Select
End Sub

--
Regards,
Bill Renaud

Try something like

Select Case X
Case 1, 3
Debug.Print "Number is either 1 or 3"
Case 2, 4
Debug.Print "Number is either 2 or 4"
Case Else
Debug.Print "Other number"
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Alan Beban" wrote in message
...
Sub abtest4()
x = 4
Select Case x
Case Is = 2 Or 4
MsgBox x
End Select
End Sub

I get no message box when I run the above. What am I missing?

Thanks,
Alan Beban

Chip Pearson wrote:
Try something like

Select Case X
Case 1, 3
Debug.Print "Number is either 1 or 3"
Case 2, 4
Debug.Print "Number is either 2 or 4"
Case Else
Debug.Print "Other number"
End Select


Thanks. The Syntax "Case 2,4" is what works; also "Case Is = 2, Is = 4"

It's still a mystery why "Case Is = 2 Or 4" neither works nor throws a
compiler error. I guess, as Jon Peltier said, "Sometimes Excel is like
that" is all the explanation I can get :-)

Alan

The expression = 2 Or 4 evaluates to 6, by Or'ing the bits

Regards,
Peter T


Thanks. The Syntax "Case 2,4" is what works; also "Case Is = 2, Is = 4"

It's still a mystery why "Case Is = 2 Or 4" neither works nor throws a
compiler error. I guess, as Jon Peltier said, "Sometimes Excel is like
that" is all the explanation I can get :-)

Alan

The last paragraph at the bottom of the "Operator Precedence" topic in
Visual Basic Help states:

"The Is operator is an object reference comparison operator. It does not
compare objects or their values; it checks only to determine if two object
references refer to the same object."

So, technically, I suppose it is not really correct programming to use the
Is operator when checking a variable that is a Variant, as in this case.

As Peter T mentioned, "Case Is = 2 Or 4" DOES WORK if x = 6! Your point
about it not throwing a compiler error is interesting, though. I noticed
that you had not declared the variable x in your sample code, so you must
not have used "Option Explicit" at the top of your code, which I assume you
normally do. (I had to add a declaration when testing the version that I
tried.)

--
Regards,
Bill Renaud

Bill Renaud wrote:
The last paragraph at the bottom of the "Operator Precedence" topic in
Visual Basic Help states:

"The Is operator is an object reference comparison operator. It does not
compare objects or their values; it checks only to determine if two object
references refer to the same object."

So, technically, I suppose it is not really correct programming to use the
Is operator when checking a variable that is a Variant, as in this case.

What is the correct programming instead of Case Is < 2?

. . . I noticed
that you had not declared the variable x in your sample code, so you must
not have used "Option Explicit" at the top of your code, which I assume you
normally do. . . .

Horror of horrors, I normally don't! Much of what I do is testing
snippets, and I can't be bothered declaring all the i's and j's and k's
and x's and y's and arr's and rng's. The time lost on the few occasions
when it bites me is nothing compared to the time "wasted" with the
declarations. So beat me :-)

Alan Beban

<<What is the correct programming instead of Case Is < 2?

I notice that if you enter "Case Is < 2


--
Regards,
Bill Renaud

(Disregard my previous post; a wrong editing keystroke posted it before I
was ready!)

<<What is the correct programming instead of "Case Is < 2"?

In this case, I guess you have to use the Is operator (the VBA editor will
add it automatically for you if you leave it out). Or, if this is a simple
case, maybe an If statement would work (i.e. If x < 2 Then ... ).

<<The time lost on the few occasions when it bites me is nothing compared
to the time "wasted" with the declarations.

I guess I have to disagree with this one. I was "bitten" early on in my VBA
learning experience by typos and spelling errors, so have been declaring
all variables ever since. Declarations also give me a place to put a
comment to further explain what the variable stands for. As you can see
from the replies that I post in this newsgroup, I tend to use long variable
names (i.e. ilngNumRows, instead of just i or j) even for short demo
routines, as I just can't figure out the algorithms when single-letter
names are used. Auto List Members fills in the variable names for me fairly
quickly most of the time. I spend relatively little time in the debugger
trying to figure out errors. I do this even for short demo routines in this
newsgroup because I hope that there are a few people that want to see the
proper way to write VBA code.

I do enjoy reading your posts, however, and like learning faster ways of
programming from the people here who are much more advanced than I.
--
Regards,
Bill Renaud

"The Is operator is an object reference comparison operator.

In a Select Case statement, the clause "Case Is" is not using the "Is"
operator. Think of "Case Is" as an single operation that just happens to use
the text "Is". If you do want to use the Is operator, you can do something
like

Dim R1 As Range
Dim R2 As Range
Dim X As Integer
Set R1 = Range("A1")
Set R2 = R1
X = 123
Select Case True
Case R1 Is R2 ' or (R1 Is R2) = True
Debug.Print "IS true"
Case Else
Debug.Print "Not IS true"
End Select



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Bill Renaud" wrote in message
. ..
The last paragraph at the bottom of the "Operator Precedence" topic in
Visual Basic Help states:

"The Is operator is an object reference comparison operator. It does not
compare objects or their values; it checks only to determine if two object
references refer to the same object."

So, technically, I suppose it is not really correct programming to use the
Is operator when checking a variable that is a Variant, as in this case.

As Peter T mentioned, "Case Is = 2 Or 4" DOES WORK if x = 6! Your point
about it not throwing a compiler error is interesting, though. I noticed
that you had not declared the variable x in your sample code, so you must
not have used "Option Explicit" at the top of your code, which I assume
you
normally do. (I had to add a declaration when testing the version that I
tried.)

--
Regards,
Bill Renaud

<<In a Select Case statement, the clause "Case Is" is not using the "Is"
operator. Think of "Case Is" as an single operation that just happens to
use the text "Is".

Thanks for the clarification, Chip. "Is" is used as a keyword in the Select
Case statement, as documented in the Select Case Statement in Visual Basic
Help.

From Visual Basic Help:

"Use the Is keyword with comparison operators (except Is and Like) to
specify a range of values. If not supplied, the Is keyword is automatically
inserted."

"Note The Is comparison operator is not the same as the Is keyword used
in the Select Case statement."

--
Regards,
Bill Renaud