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?

 

[Rick Rothstein]

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?

For your specific question, list the end point to be exclude in a "do nothing" Case statement before the range one you want. So, to exclude both 0 and 1 from the range 0 to 1 inclusive...

Select Case X
  Case 0, 1
  Case 0 To 1
    MsgBox "between 0 and 1"
End Select

If, for example, you only wanted to exclude 0 from the range, then remove the 1 from the first case statement.

 

[JenniferMurphy]

For your specific question, list the end point to be exclude in a "do nothing" Case statement before the range one you want. So, to exclude both 0 and 1 from the range 0 to 1 inclusive...

Select Case X
  Case 0, 1
  Case 0 To 1
    MsgBox "between 0 and 1"
End Select

If, for example, you only wanted to exclude 0 from the range, then remove the 1 from the first case statement.

This solution is clever, but I put it in the category of a workaround -- that is, a solution that compensates for a glaring deficiency in the language syntax. From the standpoint of clarity, simplicity, consistency, maintainability, and error-proneness,

Select Case X
  Case 0, 1
  Case 0 To 1
    MsgBox "between 0 and 1"
End Select

is not in the same league with

Select Case X
  If X > A And X < B
    MsgBox "between 0 and 1"
End Select

And worse, the language already has the simpler, more effective syntax. The code to parse it is already working for the If statement. All they had to do was port it over to the Case statement. Inexcusable. Boolean expressions anywhere should behave like boolean expressions everywhere.

 

[Peter_SSs]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]If Statement
[/TD]
[TD="align: center"]Equivalent Case Statement
[/TD]
[/TR]
[TR]
[TD]If X > A And X < B
[/TD]
[TD]none
[/TD]
[/TR]
[TR]
[TD]If X > A And X <= B
[/TD]
[TD]none
[/TD]
[/TR]
[TR]
[TD]If X >= A And X < B
[/TD]
[TD]none
[/TD]
[/TR]
[TR]
[TD]If X >= A And X <= B
[/TD]
[TD]Case A to B
[/TD]
[/TR]
</tbody>[/TABLE]

No, I showed the Case statement equivalent in my second code example in post 6.

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

Not only is there no deficiency, since I showed you how to do it already, but there is a further advantage in that if you use Select Case x then you can only use the value of variable x in your Case comparisons but if you use Select Case True you can use whatever variables you want and build a boolean expression as complex as you like.
For example

Select Case True
  Case 2 * x + 5 >= 4 And y < 6
  
  Case (x > y And y > z) Or ((IsEmpty(Range("A1").Value) Or z = 5) And Sqr(x) < 5)
  
End Select

 

[JenniferMurphy]

No, I showed the Case statement equivalent in my second code example in post 6.

If you are referring to the solution that involves null Case statements to eliminate the boundary conditions (endpoints) before testing for the rest of the range, those are "equivalent", IMHO, only in the results. That is, they produce the same result as would be possible if the Case statement accepted normal Boolean expressions. There are not even close to equivalent in any other way. I contend that they are obtuse workarounds that are far more difficult to read and error prone in that the two Case statements must stay together for the code to work, something that is completely unnatural.

Not only is there no deficiency, since I showed you how to do it already, but there is a further advantage in that if you use Select Case x then you can only use the value of variable x in your Case comparisons but if you use Select Case True you can use whatever variables you want and build a boolean expression as complex as you like.
For example

Select Case True
  Case 2 * x + 5 >= 4 And y < 6
  
  Case (x > y And y > z) Or ((IsEmpty(Range("A1").Value) Or z = 5) And Sqr(x) < 5)
  
End Select

Your Select Case True solution is a far better solution and one which I misread initially. My apologies. It does indeed provide most of the functionality of the If statement, but, as you point out, lacks the feature of restricting the test expressions to the target variable. I consider that a fairly significant deficiency.

But the main point here is that all of this is completely unnecessary. The failure of the Case statement to accept all normal Boolean expressions is stupid, incompetent, and probably arrogant on the part of the M$FT.

And the fact that Select Case True opens up the door to general Boolean expressions makes the deficiency even more ridiculous, IMHO.

That said, thank you for the creative suggestion of Select Case True and I apologize again for overlooking it. It definitely solves several of the problems of Select Case X.

Good on ya, mate. G'day.

 

[Peter_SSs]

If you are referring to the solution that involves null Case statements to eliminate the boundary conditions ..

I think you subsequently discovered that I was not referring to that solution, but since you didn't remove the comment I'm formally confirming that I wasn't.

.. as you point out, lacks the feature of restricting the test expressions to the target variable.

I did not suggest such a lack and it is not the case. Once again, my second code example in post 6 specifically restricts the case statements to testing values of the variable x only. No deficiency there IMHO.

The failure of the Case statement to accept all normal Boolean expressions ..

I haven't come across any Boolean expression that I have been unable to put into a Case statement when I wanted to do that. No deficiency there IMHO.

.. thank you for the creative suggestion ..

You are very welcome.

 

[JenniferMurphy]

I guess we will have to agree to disagree on what is and is not a deficiency.

I have a very low tolerance for crappy design and, IMHO, VB is riddled with it. It is easily the worst programming language I have ever used. The only other language I have ever used that even comes close is a very early and very crude basic microprocessor assembler language that did not support labels, so we had to write branch instructions like

   B   *+166

Good luck debugging or maintaining that code.

I wish Woody Leonhard would write a Book of Bugs on VB like he did in his 1,000 page book about Word.

 

[MrIfOnly]

I agree with you Jennifer, that there are deficiencies with VBA that I wish would be addressed by Microsoft. I don't know that they ever will, however, because I think that the language was never intended to do half the things our little community here has done with it. However, I find myself running into as many limitations with my knowledge of it as I do limitations of the language itself. Case in point (no pun intended) is the Case Select True statement that Peter_SSs introduced to me in this thread. I have always used the Case - do nothing method to get around what I perceived as a limitation and, viola!, here's a better way!

Regards,

CJ

 

[JenniferMurphy]

I agree with you Jennifer, that there are deficiencies with VBA that I wish would be addressed by Microsoft. I don't know that they ever will, however, because I think that the language was never intended to do half the things our little community here has done with it.

They never will be addressed for several reasons. But the main one, IMHO, is that there are no $$$ in it for M$FT for making it any better. Excel macros don't sell many copies of Office and VBA programmers will put up with the VBA crap or find clever workarounds because we really have no choice.

However, I find myself running into as many limitations with my knowledge of it as I do limitations of the language itself.

This is almost certainly true for almost all of us. It's certainly true for me.

Case in point (no pun intended) is the Case Select True statement that Peter_SSs introduced to me in this thread. I have always used the Case - do nothing method to get around what I perceived as a limitation and, viola!, here's a better way!

Absolutely. One of the main reasons I post many of my questions is that, in addition to usually getting an answer that I can use, I get additional peripheral information that I would have never figured out on my own. And I almost missed the Select Case True solution and would have if Peter hadn't called my attention to it a second time. Thanks, Peter.

This place is a gold mine. And Peter is one of the best. We are truly lucky that he and others like him are willing to take the time to contribute.

And M$FT is also very lucky and should be paying these guys. They are doing a lot of the heavy lifting that M$FT is too stupid, too lazy, and/or too arrogant to do itself. And having worked for a large technology company, I am sure that these guys are better programmers than a lot of M$FT programmers, especially in the Office division.

 

[RoryA]

there are deficiencies with VBA that I wish would be addressed by Microsoft. I don't know that they ever will, however

They won't, because MS do not see VBA as the future for Office scripting. That is not to suggest that VBA will be going away any time soon, since that would be commercial suicide (IMO), but there will continue to be little to no investment in it beyond adding things to the Object Model as necessary.

 

[My Aswer Is This]

I love Excel and I know they have written 10 billion lines of code to get this program working.
And they have to be concerned about forward and backwards compatibility.
But I do wonder why we have to do things like this:

Rows(1).Hidden = True
Sheets(2).Visible = False

I would think it would be like this:

Rows(1).Hidden = True
Sheets(2).Hidden= True