excelvbaif-statementconditional-statements

Group of conditions in IF THEN statements in VBA


I have a code that allows the user to know what to do with an item depending on several conditions - there's tens of thousands of items. There are specific exclusions in the code based on very specific details. Given the wide array of items, and critical instructions, there's quite a bit of exclusions, sometimes they overlap.

It looks like this :

 If type_reception = "NEW" And Techno = "XYZ" And Critical_Layer =
 "YES" And Layer = "123" Then

I would like to identify this specific set of conditions under a name that I can then reference, for example Exclusion 1, that I could then reference easily in other sets of conditions, instead of rewriting it all :

 If Lambda = "Value X" And type_reception = "NEW" And Set <> "VAlue Y"
 And **Exclusion <> "Exclusion 1"** Then

 If Lambda = "Value X" And type_reception = "NEW" And Set <> "VAlue Y"
 And **Exclusion <> "Exclusion 1" And Exclusion <> "Exclusion 2"** Then

How could I do ? I've tried looking up solutions, but I have actually no idea what to look for... I believe I could use Case, but I cannot relate it to my problem.

Below an edited code to show its structure more ; I left one line with many arguments, and beneath, one with what I'd like to see :

Sub Reception()
 
    Reception.Show
 
    If Quitter = True Then 
        Sheets("Accueil").Activate 
        Exit Sub 
    End If
 
    MasqueID = InputBox("Indiquez le nom du masque à renvoyer :", "Nom du masque")
    Sheets("Repell form").Range("C11").Value = MasqueID
 
    Worksheets("Database").Visible = True 
    Worksheets("Database").Activate
 
    Dim grade As String 
    Dim Maskshop As String 
    Dim pellicule As String 
    Dim Groupe As String 
    Dim State As String
 
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
 
    For Each cellule In Selection
 
        `Lots of info to check for in database
             
           Exit For
        End If
    Next
 
    CHOIXTECHNO = Left(Masque_ireticles, 2)
    Select Case CHOIXTECHNO
 
        'LIST OF CASES
        Case Else Techno = "" 
    End Select
 
    Dim Erreur_reception As Boolean 
    Erreur_reception = True
 
    If type_reception = "NEW" And Backup = "NO" And Techno = "123" And Layer_critique = "YES" And Layer = "XY" Then
        Statut_mask = "PRODUCTION"
        Erreur_reception = False
        Reception_production.Show
    End If
 
    If Lambda = "### NM" And type_reception = "NEW" And Techno = "114" And Layer = "YZ" And Techno <> "1234" And Layer_critique <> "YES" And Layer <> "#0#" Then
        Statut_mask = "TO_BE_QUALIFIED"
        Reception_XYZ_123.Show
        Erreur_reception = False
    End If
         
    ElseIf Lambda = "## NM" And Not Exclusion1 Then
        If Backup = "NO" Then
            Statut_mask = "PRODUCTION"
        Else
            Statut_mask = "DISPENSATION"
        End If
        Reception_production.Show
        Erreur_reception = False
    End If
    
End Sub

Solution

  • Just declare a boolean variable and assign the logical check to it:

    Dim Exclusion1 as Boolean
    Exclusion1 = (type_reception = "NEW" And Techno = "XYZ" And Critical_Layer = "YES" And Layer = "123")
    

    Then you can use it for example like this

    If Lambda = "Value X" And type_reception = "NEW" And Set <> "VAlue Y" And Not Exclusion1 Then