Vba - if vs select case

Minkowski

Board Regular
Joined
Sep 16, 2009
Messages
157
Hello everyone i want to simplify my code if possible here


Code:
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:
Code:
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
Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
can i make it shorter? something like:
Rich (BB code):
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
For example, for Case "1" you'd code:-
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:-
Code:
[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.
 

Forum statistics

Threads
1,141,220
Messages
5,705,091
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top