Vba - if vs select case

[Minkowski]

Hello everyone i want to simplify my code if possible here

Case "1"
    If Range("A1") = "bbb" And Range("B1") = "aaa" And 
    Range("I1") = "ccc" Or Range("I1") = "ddd" Or Range("I1") = "eee" Then
        Range("N1").Value = 25
    ElseIf Range("A1") = "aaa" And Range("B1") = "bbb" And 
    Range("I1") = "ccc" Or Range("I1") = "ddd" Or Range("I1") = "eee" Then
        Range("N" & I).Value = 35
ElseIf Range("A1") = "bbb" And Range("B1") = "aaa" Then
           Range("N1").Value = 30

can i make it shorter? something like:

Case "1"
If Range("A1"&"B1"&"I1")= "bbbaaaccc" Or "bbbaaaddd" 
Or "bbbaaaeee":Range("N1)=25
ElseIf Range("A1"&"B1"&"I1")= "aaabbbccc" Or "aaabbbddd" 
Or "aaabbbeee":Range("N1)=35
ElseIf Range("A1"&"B1")= "bbbaaa": Range("N1")=30

i know i can do it with select case

Case "1"
Select Case Range("A1") & Range("B1")
Case "bbbaaa": Range("N1") = 30 
End Select
Select Case Range("A1") & Range("B1") & Range("I1")
Case "bbbaaaccc","bbbaaaddd",bbbaaaeee": Range("N1") = 25
Case "aaabbbccc","aaabbbddd","aaabbbeee": Range("N1") = 35
End Select
.....

but i don't like going into 2 select cases, i was hoping for even more compact code,thanks in advance

 

[Ruddles]

can i make it shorter? something like:

Case "1"
If Range("A1"&"B1"&"I1")= "bbbaaaccc" Or "bbbaaaddd" 
Or "bbbaaaeee":Range("N1)=25
ElseIf Range("A1"&"B1"&"I1")= "aaabbbccc" Or "aaabbbddd" 
Or "aaabbbeee":Range("N1)=35
ElseIf Range("A1"&"B1")= "bbbaaa": Range("N1")=30

No, you can't do that: each condition - the bits in red - must be complete. "bbbaaaddd" is not a condition. You have to repeat the whole thing - both of the bits you're comparing.

i know i can do it with select case...
but i don't like going into 2 select cases

If you indent your code correctly it becomes much more easy to read and understand.

If you want to compact your code, you can remove some of it to separate functions or procedures. For example, your main Select Case - does it contain a lot of Case clauses? If so, the code in each of those could be made separate procedures or the values you're assigning to the various ranges could be returned by a function.

But don't get too hung up on producing the smallest possible code. Quite often it's more important for it to be easily understood, especially when you come back to it several weeks later and can't remember why you coded something the way you did.

 

[Ruddles]

For example, for Case "1" you'd code:-

[FONT=Fixedsys]Case "1"[/FONT]
[FONT=Fixedsys]   Range("N1").Value = N1_Value(Range("A1").Value, Range("B1").Value, Range("I1").Value)[/FONT]

Then you'd have a function like this:-

[FONT=Fixedsys]Function N1_Value(R1 As String, R2 As String, R3 As String) As Integer[/FONT]
 
[FONT=Fixedsys] If R1 = "bbb" And R2 = "aaa" And R3 = "ccc" Or R3 = "ddd" Or R3 = "eee" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 25[/FONT]
[FONT=Fixedsys] ElseIf R1 = "aaa" And R2 = "bbb" And R3 = "ccc" Or R3 = "ddd" Or 3 = "eee" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 35[/FONT]
[FONT=Fixedsys] ElseIf R1 = "bbb" And R2 = "aaa" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 30[/FONT]
[FONT=Fixedsys] End If[/FONT]
 
[FONT=Fixedsys]End Function[/FONT]

You could stick any functions away at the end of the module or even in a separate module. That way you wouldn't need to see them every time you looked at your main code - you'd just regard them as 'black boxes' into which you inject some data and which then return the required values.

 

[Minkowski]

that is very interesting and helpful
thank you very much!!!