Case Statement

[Question Boy]

Hello,

Can it be done? I need to construct a multi criteria case statement but can
seem to get it to work. Something like

Select Case blVal And buVal
Case True And False

Case True And True

Case False And True

Else Case

where blVal and buVal are 2 bolean input variable to my function. Can this
be done or must I use if logic?

Thank you,

QB

 

[Marshall Barton]

Can it be done? I need to construct a multi criteria case statement but can
seem to get it to work. Something like

Select Case blVal And buVal
Case True And False

Case True And True

Case False And True

Else Case

where blVal and buVal are 2 bolean input variable to my function. Can this
be done or must I use if logic?

Well it could be done, but the code would be terribly
cryptic. Using If would be much clearer.

If blVal And buVal Then
...
ElseIf blVal And Not buVal Then
...
ElseIf Not blVal And buVal Then
...
Else
...
End If

 

[Question Boy]

Thank you! I would not have thought of that permutation.

One little precision for anyone who ever reviews this post I had to put the
value in quote for it to work as shown below.

Select Case blVal & buVal
Case "TrueFalse"
...
Case "TrueTrue"
...
etc..

QB

 

[Marshall Barton]

Select Case blVal & buVal
Case TrueFalse
...
Case TrueTrue
...

Very clever Rick, carefully placed in tricks bag ;-)

 

[Rick Brandt]

Very clever Rick, carefully placed in tricks bag ;-)

As the OP pointed out though I forgot the quotes around the "TrueFalse" values.

I have only used something like this when there were several boolean values.
For only two I would just use If-Else-Then blocks.

 

[Robert Morley]

I like Rick's suggestion, though the conversion to string and then
comparison of strings would probably slow it down significantly if it were
in a very tight loop...probably not a problem unless it's something running
hundreds of thousands of times, though.

Another common, albeit somewhat cryptic, method of doing it is as follows:

Select Case -blVal - 2 * buVal '- 4 * Next1 - 8 * Next2 - 16 * Next3, etc.
Case 0 'blVal = False, buVal = False
Case 1 'blVal = True, buVal = False
Case 2 'blVal = False, buVal = True
Case 3 'blVal = True, buVal = True
End Select

If you don't understand the logic, essentially what you're doing is creating
bit flags out of your boolean values. Since a boolean value evaluated as a
number will either be -1 if True or 0 if False, you negate the value to make
it a 1 or 0, then multiply it by a power of two to stuff it into that bit
position. Add everything together and you've got your value. Since the
number are all negative to begin with, however, you combine the negation and
the addition by simply subtracting everything.

So in this case, you've got buVal going into the 2's column and blVal going
into the 1's column (thinking in binary here, of course).

00 = 0 = buVal False, blVal False
01 = 1 = buVal False, blVal True
10 = 2 = buVal True, blVal False
11 = 3 = buVal True, blVal True


Rob

 

[Marshall Barton]

I like Rick's suggestion, though the conversion to string and then
comparison of strings would probably slow it down significantly if it were
in a very tight loop...probably not a problem unless it's something running
hundreds of thousands of times, though.

Another common, albeit somewhat cryptic, method of doing it is as follows:

Select Case -blVal - 2 * buVal '- 4 * Next1 - 8 * Next2 - 16 * Next3, etc.
Case 0 'blVal = False, buVal = False
Case 1 'blVal = True, buVal = False
Case 2 'blVal = False, buVal = True
Case 3 'blVal = True, buVal = True
End Select

Exactly the approach I thought was too cryptic to be useful,
especially to someone that had to ask how

I would also worry about the value of True being different
in various possible data sources.

 

[Robert Morley]

Yes, True can be 1 for things like SQL Server and in some other languages,
but by the time you've assigned them to boolean values in VB/VBA, they're
necessarily going to have been converted to -1.


Rob

 

[Guest]

Select case True
Case blVal and Not buVal

Case blVal and buVal

Case Not blVal and buVal

case Not blVal and Not buVal

(david)

 

[Marshall Barton]

I knew that. Now I have to wonder what that brain cell was
doing when I posted a reply.

Thanks for reminding me David, and don't be such a stranger.

 

[Robert Morley]

I'll admit it can be useful on occasion, but there's just something so WRONG
about "Select Case True".


Rob

 

[Guest]

I'll admit it can be useful on occasion, but there's just something so WRONG
about "Select Case True".

Ahhh -- at heart you are a Pascal programmer....

Remember, that right from the first BASIC, each LINE is compiled and
executed.

You can't have a multi-line construct because this is a line language.

Of course this means that you can't optimise across lines like FORTRAN does,
but evaluation of the lines allows the flexible use of CASE that just
doesn't
exit in other, inferior implementations that only have jump tables, not real
case
statements at all....

(david)

 

[Robert Morley]

Ahhh -- at heart you are a Pascal programmer....

Yup. Structured-programming = good!

Remember, that right from the first BASIC, each LINE is compiled and
executed.

Compiled? The first BASICs I remember were interpreted! (Vic 20, C64,
Apple II, and TRS-80 were my first exposures to BASIC.)

Of course this means that you can't optimise across lines like FORTRAN does,

I wouldn't have thought that would still be the approach in VB6, but I don't
pretend to have ever looked behind the scenes of a modern compiler.



Rob

 

[Guest]

I wouldn't have thought that would still be the approach in VB6

VB6 is compiled by the Visual Studio 6 compiler, so internally
it's not a line compiler or a line interpreter. Still, the effect of the
language definition and history sometimes has interesting effects
on the executable.

Case statements in C, although designed to be like a jump table,
often must be implemented as a series of if/else statements, but
the code order is randomised by optimisation, because a jump
table has no defined order.

In BASIC after compilation of a case statement the code order
remains unchanged, because the definition of the case statement as
short circuit evaluation, which is implied from the expectation that
code will be evaluated line-by-line, means you have to evaluate the
case conditions in order.

And Microsoft BASIC, which was a compiled language, internally
compiled to a series of function calls, almost as if it was interpreted:
Expressions were evaluated by calls to the expression evaluation
routine. A case statement was evaluated as a series of calls to
the expression evaluation routine. Dunno what VBA looks like
inside.

And C has always had problems with register optimisation because
the use of untied pointers instead of arrays and byref variables means
it is often impossible to know if a value can be left in register, or
must be saved to memory. When I'm looking inside a compiled C
program, I'm constantly saying WTF? Often even the most obvious
optimisations aren't made, because in C they would require assumptions
about the rest of the code.

Oh well. I've wandered off-topic I guess.

(david)