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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
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,785
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top