[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