Group of conditions in IF THEN statements in VBA

Question

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

Please share what the complete relevant code looks like at the moment and what you would like it to look like. You could use a boolean variable, e.g., Dim Exclusion1 As Boolean: Exclusion1 = (type_reception = "NEW" And Techno = "XYZ" And Critical_Layer = "YES" And Layer = "123") which you could utilize with If Exclusion1 Then. Use a more meaningful variable name though. — VBasic2008
– VBasic2008, Commented Jan 6 at 10:08
Uh, I'll see if I can post more of it, but there's a lot of sensitive info in it, so I'll have to trim it thoroughly first. — Florent
– Florent, Commented Jan 6 at 10:57
Are you really going to maintain tens of thousands of exclusions in your VBA code? Even with some streamlining, that is going to be hard. I would suggest building a separate sheet with exclusion criteria, reading that table into memory, and iterating over it to check if an item needs to be excluded. — Paul
– Paul, Commented Jan 6 at 13:14
No no, there's thousands of items, but they can be grouped into easy to sort out categories. It's the additions of criteria that build up the exclusions. It can be a NEW item, with TECHNO 1Y6 and BACKUP yes or no ; or the same, with TECHNO 1U7 - the permutations are technically endless, but there's only ten categories. It can overlap with more "generic" items though, and so if I could just reference groups of exclusions easily, it'd work out. — Florent
– Florent, Commented Jan 7 at 8:30

FunThomas · Accepted Answer · 2025-01-06 10:07:45Z

1

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

answered Jan 6 at 10:07

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Florent Jan 6 at 11:36

It doesn't work =/ I've edited my message above with a more complete edit of my code. @VBasic2008

Florent Jan 7 at 8:55

And it works ! I don't know why it didn't yesterday, but now it's okay. Maybe I was already too tired from searching, who knows. Thanks a lot @FunThomas It's simple and easily reproducible - I've taken note to dig deeper into boolean functions. (and by the way, sorry for the need of edits on my post...)

Collectives™ on Stack Overflow

Group of conditions in IF THEN statements in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related