Multiple case <>

[Manuel Cavero]

Good morning or afteernooon to everyone:

I've this little piece of VBA code that it's not working:

Select Case PAc_ALA[INDENT]Case Is <> 5, Is <> 10, Is <> 15, Is <> 20, Is <> 25, Is <> 30, Is <> 35, Is <> 40[/INDENT]
[INDENT]       Do whatever[/INDENT]
End Select

Where PAc_ALA is an integer. Anyway, I tried in a different way:

Select Case True[INDENT]Case PAc_ALA <> 5, PAc_ALA <> 10, PAc_ALA <> 15, PAc_ALA <> 20, PAc_ALA <> 25, PAc_ALA <> 30, PAc_ALA <> 35, PAc_ALA <> 40[/INDENT]
[INDENT]      Do whatever[/INDENT]
End Select

But it's not working anyway. What it's happenning?

 

[MartyS]

The CASE statement is evaluating for ALL conditions..


Compare:

For Cntr = 1 To 20

     PAc_ALA = Cntr 


    Select Case PAc_ALA
    
        Case Is <> 5, Is <> 10
            Debug.Print PAc_ALA
    
    End Select


Next Cntr

with:

For Cntr = 1 To 20

     PAc_ALA = Cntr 


    Select Case PAc_ALA
    
        Case Is <> 5
            Debug.Print PAc_ALA
        Case Is <> 10
            Debug.Print PAc_ALA
    
    End Select


Next Cntr

In the first instance it's evaluating ALL criteria at once..

Thus, the number 5... when evaluated... is being asked the question... "Are you equal to BOTH: (NOT 5) AND (NOT 10)


Evaluating the expression 5 = NOT(5) and NOT(10) returns FALSE... because it doesn't meet both criteria.



The second code sample evaluates separately..


So, how do you get what you want?

For Cntr = 1 To 20


    PAc_ALA = Cntr


    Select Case PAc_ALA
    
        Case 5, 10, 15, 20, 25, 30
            
            'Do Nothing
            
        Case Else
        
            'Put your code here
        
            Debug.Print PAc_ALA
    
    End Select


Next Cntr

Hope that helps explain!

 

[Manuel Cavero]

Hi MartyS


Thanks a lot for your answer. So VBA translates the comma as AND operator, not as OR operator.


What I'm needing is an execution in case of the integer PAc_ALA would be different from 5, 10, etc...


What you propouse is a good solution, complex, but at the end of the day good solution.

Thanks a lot!!!

 

[Fluff]

So VBA translates the comma as AND operator, not as OR operator.

Nope. the comma is treated as OR.
There's a good article on the Select Case here https://www.exceltrick.com/formulas_macros/vba-select-case-statement/

 

[Fluff]

This is another way of doing it

   Select Case True
      Case PAc_ALA Mod 5
      'do something
   End Select

 

[MartyS]

Nope. the comma is treated as OR.
There's a good article on the Select Case here https://www.exceltrick.com/formulas_macros/vba-select-case-statement/

The AND in the explanation was referring to there being more than one evaluation, and not the actual BOOLEAN evaluation itself:


CASE Statement: I'm doing evaluation on this criteria AND this critera AND this criteria.. AND this criteria also...


Yes, the BOOLEAN logic is OR:

and it's also true that the last column is doing evaluations on <>5 and <>10


The only criteria that could be excluded in a case statement:

[COLOR=#333333][FONT=monospace]Case Is <> 5, Is <> 10[/FONT][/COLOR]

would be an impossible number that was simultaneously 5 and 10




Apologies if it wasn't clear enough...

 

[MartyS]

This is another way of doing it

   Select Case True
      Case PAc_ALA Mod 5
      'do something
   End Select

This is great unless the data presented to the SELECT statement goes beyond, say 45, yet the criteria doesn't.


E.G. If the criteria is specifically: <>5, <>10, <>15, <>20, <>25, <>30,<>35, <>40 (i.e. doesn't go beyond 40)

and the data is any value in the range of, say, 1 - 100



It also wouldn't work if, say, any of the criteria was a value other than an increment of 5

E.G. <>5, <>9, <>15, <>23, <>25, <>31,<>34, <>40



If it's always infinite increments of 5 (or the data doesn't go beyond 44)... PAc_ALA Mod 5 is so much more elegant!

 

[Manuel Cavero]

Hello Fluff:

Your solution is -by far- more elegant. Thanks a lot Well done

 

[Manuel Cavero]

Hello MartyS: Thanks a lot for your explanation. I see it clearer now.

You are right

 

[Fluff]

Glad we could help & thanks for the feedback