Case statement with multiple ANDed conditions

[JenniferMurphy]

I need a Case statement that will be true if a value is >=0 AND <=1. Here's what I have tried, none of which work:

Case Is >= 0, Is <= 1          'True for all values
Case Is >= 0 and Is <= 1       'Syntax error
Case >= 0 and <= 1             'Syntax error
Case is >= 0 andalso is <= 1   'Syntax error
Case is >= 0, andalso is <= 1  'Syntax error

Is there a way to code multiple tests that must all be true (ANDed) on a single Case statement in VBA?

If not, what's the best workaround?

 

[MrIfOnly]

You can use:

Case 0 To 1

Regards,

CJ

 

[JenniferMurphy]

You can use:

Case 0 To 1

Regards,

CJ

Doh!

As usual, I made it a lot harder than was necessary. I got locked into the comparison syntax and couldn't see anything else.

Thanks

 

[MrIfOnly]

Been there, done that

 

[JenniferMurphy]

Your solution (Case 0 to 1) handles the case where 0 <= x <= 1 [0,1]. What if one or both sides do not include the "=" condition?

[LIST=1]
[*]0 < x <= 1  '(0,1] 
[*]0 <= x <1   '[0,1) 
[*]0 < x < 1   '(0,1)<x <="" 1="" (0,1)<="" li=""> </x> 
[/LIST]

Is there an expression for those situations?

 

[Peter_SSs]

It depends what your other conditions are and what your possible values for x are. For example, if x can not be negative and you wanted to deal with the ranges
0<=x<1
1<=x<5
5<=x<7
then something like

Select Case x
  Case Is < 1
  Case Is < 5
  Case Is < 7
  Case Else
End Select

If x could be any number and the only ranges you want to work with are
1<x<=5
12<=x<20
then you can use a structure like this

Select Case True
  Case x > 1 And x <= 5
  Case x >= 12 And x < 20
End Select

 

[mikerickson]

If x could be any number and the only ranges you want to work with are
1 < x <=5
12<=x<20

Another approach would be

Select Case x
    Case Is <=1
        ' blank line, do nothing
    Case is <=5
        MsgBox "Between 1 and 5"
    Case is <12
        ' blank line, do nothing
    Case is <20
        MsgBox "between 12 and 20"
    Case Else
End Select

 

[Peter_SSs]

Another approach would be

Select Case x
    Case Is <=1
        ' blank line, do nothing
    Case is <=5
        MsgBox "Between 1 and 5"
    Case is <12
        ' blank line, do nothing
    Case is <20
        MsgBox "between 12 and 20"
    Case Else
End Select

I agree Mike. My reasoning for using what I did in the second example was that I had already shown that sequential idea in my first example & I wanted to demonstrate the "Select Case True" concept - which many users are unaware of.

 

[JenniferMurphy]

So, bottom line: there is no Case statement equivalent to 3 of these 4 If statements:

If Statement	Equivalent Case Statement
If X > A And X < B	none
If X > A And X <= B	none
If X >= A And X < B	none
If X >= A And X <= B	Case A to B

<tbody>
</tbody>

And there is a similar deficiency for even simple Or expressions, let along more complex boolean expressions.

Correct?

It seems pretty clear that Visual Basic was "designed" (I am being extremely charitable) by a robot using a random number generator or, at best, a wannabe software engineer who transferred over from marketing.

The only other possible explanation is that it is a creation of Beelzebub himself as the ultimate eternal punishment reserved for those who spent their lives writing endless loops, incomprehensible error messages, unnormalized data structures. (sigh)
[h=4][/h]

 

[JenniferMurphy]

Been there, done that

I like your rate schedule. It reminds me of a sign I once saw in a service station garage:

 Rates:         $50/hour
 If you wait:   $60/hour
 If you watch: $100/hour
 If you help:  $500/hour