[VBA] - IF & AND statements together

[Eawyne]

I will try to make this as clear as possible ^^' I cannot share too much of the code, as it contains loads of sensitive data (even just in the Dim declarations : client names, product codes, etc. It's not inherent to the code, but to the the data affixed to the products we receive, no other way around it. I believe not much is needed for this regardless). I took over someone else's work on this macro, and basically learned how to code starting on that. My level is not newbie, but certainly not high at all either.

Also, sorry if there are topics to answer this already, but I don't *know* exactly what to look for, and just searching for "if and" doesn't yield any results…

-*-*-*-*-*-

I have items I need to receive ; there are several ID markers for each of those. There are specific instructions for some of them depending on items. I have a code to treat differents cases.

ID markers :
Model A or B
5 first digits of the item
6th and 7th digits

So if I have those two items :

141HA050025 Model A

645VA050047 Model A

...with an exclusion on 141HA and 05 that need to be dealed with in a different way, I would write something like this :

If 5_digit = "141HA" and Layer = "05" Then
Do X

Then, I have a generic instruction to receive all Model A items and do Y with them :

If Model = "A" Then
Do Y

With special instructions, I have :

If model = "A" and Not 5_digit = "141HA" and Not Layer = "05" Then
Do Y

But if I now run the item 645VA050047 through that macro, it will get excluded because it matches the Layer = "05" If.

And this is just one case, I have 4 exclusions like this, that revolve around 15 ID markers. There are myriad overlaps possible.

So I am trying to figure out a way around this. I tried using a workaround :

Dim Exclu141HA As Boolean
Exclu141HA_1 = (5_digit = "141HA" and Layer = "05" Then)

That I would then write like :

If model = "A" and Not Exclu141HA_1 Then
Do Y

But it doesn't seem to work, as it will still look at what is contained in that Boolean (I believe so, at least, because tests have been unconclusive so far).

So my problem is that I cannot make AND statements to correspond only to certain other conditions, using ( ). Is that correct ?

How could I tackle this problem ?

-*-*-*-*-*-

I tried doing something that would encapsulate all conditions, but it still means that it can trigger in certain other conditions later down, and show unnecessary - and contradicting - information (not to mention it is way too complicated to maintain and probably heretic :

  If Maskset = "141HA" And Layer = "05" Then
    If Backup = "NO" Then
            Statut_mask = "PRODUCTION"
            Else
            Statut_mask = "DISPENSATION"
            End If
        Reception_production5.Show
        Erreur_reception = False
    End If


If Maskset = "141HA" And Layer <> "05" Then

    If Backup = "NO" Then
        If type_reception = "NEW" Then
            Statut_mask = "Production Test 1"
            Reception_production.Show
            Erreur_reception = False
            Else
            If chrome = "BINARY" Then
                Statut_mask = "HOLD"
                Reception_repell_binaire.Show
                Erreur_reception = False
            Else
                Statut_mask = "Production Test 2"
                Reception_production.Show
                Erreur_reception = False
            End If
        End If
     
    Else
    If Layer_critique = "YES" Then
        Statut_mask = "Qual Lot Required Test 1"
        Reception_layer_critique.Show
        Erreur_reception = False
        Else
        If chrome = "BINARY" Then
            Statut_mask = "Hold Test 1"
            Reception_repell_binaire.Show
            Erreur_reception = False
            Else
                Statut_mask = "Production Test 3"
                Reception_production.Show
                Erreur_reception = False
        End If
        End If
    End If
End If

 

[Micron]

Consider a Select Case block instead of a whack of If's and And's. Your most flexible use of that might be to

Select Case True
   Case a = b and c = d
       do stuff
   Case w = x and y = z
      do other stuff
   Case 'and so on
End Select

 

[Eawyne]

Hey ! Sorry for the delay in replying. I had never used Select Case yet, and have finally been able to toy around it this morning. It looks promising indeed, the first batch of iterations works fine so far. Thank you for the input =)

 

[Eawyne]

Well, no, scratch that. It just selects the first Case of each different Select Case, period Oo

I have this entry : 141HA05A2060 that has Backup = "YES"

Select Case Renvoi_141HA

Case type_reception = "NEW" And Maskset = "141HA" And Layer = "05" And Backup = "NO"
    Statut_mask = "PRODUCTION 11"
    Reception_production5.Show
    Erreur_reception = False
    
Case type_reception = "NEW" And Maskset = "141HA" And Layer = "05" And Backup = "Yes"
    Statut_mask = "PRODUCTION 22"
    Reception_production5.Show
    Erreur_reception = False
    
    
Case Maskset = "141HA" And Layer = "05" And Backup = "Yes"
    Statut_mask = "DISPENSATION Basique 1"
    Reception_production5.Show
    Erreur_reception = False
    
End Select

It will pick the first case with Backup = "NO", even though it should pick the second one.

And it's the same with all other Cases I added that have nothing to do with this entry's name, that should not work out, but it's like he doesn't care if it's Case X AND Case Y, he just sees one that matches, and goes with it...

 

[Tupe77]

Do you mean something like this?
This doesn't completely solve your problem, but it shows how AND NOT AND works.
I hope I understood your question correctly and this is the answer you wanted.

Sub TrueAndNotTrue()

Dim arr(1 To 2) As String
arr(1) = "141HA050025 Model A"
arr(2) = "645VA050047 Model A"

Dim i As Integer

Dim model As String, digit_5 As String, Layer As String
    For i = 1 To 2
        model = Right(arr(i), 1)
        digit_5 = Left(arr(i), 5)
        Layer = Mid(arr(i), 6, 2)

        If model = "A" And Not (digit_5 = "141HA" And Layer = "05") Then ' VBA ignores parentheses, but they are there for readability.
            Debug.Print arr(i) & " match1" ' 645VA050047 Model A match1
        End If
    Next i
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Micron]

Renvoi_141HA

You can't look for a Case for that and test other things. It's all about whatever is in the first line. To test other things like that your case would need to be
Select Case True

 

[Micron]

Playing with the Case block, maybe

Select Case True
'Renvoi_141HA I have no idea how this relates to anything but it doesn't seem relatie to the rest of this

Case type_reception = "NEW" And Maskset = "141HA" And Layer = "05" And Backup = "NO"
    Statut_mask = "PRODUCTION 11"
    
Case type_reception = "NEW" And Maskset = "141HA" And Layer = "05" And Backup = "Yes"
    Statut_mask = "PRODUCTION 22"
      
Case Maskset = "141HA" And Layer = "05" And Backup = "Yes"
    Statut_mask = "DISPENSATION Basique 1"
    
End Select
'I'm guessing next 2 lines happen regardless of the case so just need to write them once?
Reception_production5.Show
Erreur_reception = False

VBA ignores parentheses, but they are there for readability

I think not, for the first part. They definitely control arithmetic calculations, and as far as I know, logical operators as well.
Depending on the operators, this If model = "A" And Not digit_5 = "141HA" And Layer = "05" then
could be evaluated as
If model = ("A" And Not digit_5 = "141HA") And Layer = "05" or
If model = "A" And Not (digit_5 = "141HA" And Layer = "05")

Then there is the subject of passing a value byRef or ByVal, depending on which you use. Then there is the rule that to use the value returned by a function, parentheses are required. So no, they are not ignored.

 

[Tupe77]

VBA ignores parentheses, but they are there for readability

I think not, for the first part. They definitely control arithmetic calculations, and as far as I know, logical operators as well.
Depending on the operators, this If model = "A" And Not digit_5 = "141HA" And Layer = "05" then
could be evaluated as
If model = ("A" And Not digit_5 = "141HA") And Layer = "05" or
If model = "A" And Not (digit_5 = "141HA" And Layer = "05")

Of course I didn't mean arithmetic calculations, otherwise Excel wouldn't be used for calculations.
I primarily meant the line in my code where the parentheses have no effect, but indirectly I also meant logical operators, that's where I was wrong.
I should have known better, but for some reason when I tested Eawyne's example, I came to the same conclusion as Eawyne.

If you had read Eawyne's question carefully, you would have noticed that:

So my problem is that I cannot make AND statements to correspond only to certain other conditions, using ( ). Is that correct ?

Your answer to me:

If model = ("A" And Not digit_5 = "141HA") And Layer = "05" or
If model = "A" And Not (digit_5 = "141HA" And Layer = "05")

With a corrected parenthesis:

    If (model = "A" And Not digit_5 = "141HA") And Layer = "05" Then
    If model = "A" And Not (digit_5 = "141HA" And Layer = "05") Then

Your example is a direct answer to Eawyne's question:

So my problem is that I cannot make AND statements to correspond only to certain other conditions, using ( ). Is that correct ?

It is not correct because parentheses can restrict AND statements to correspond only to certain other conditions, using ( )

So thanks for answering me even though Eawyne's was the original questioner!