# Vba - if vs select case

#### Minkowski

##### Board Regular
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"
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 "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

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
can i make it shorter? something like:
Rich (BB code):
``````Case "1"
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
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.

#### Minkowski

##### Board Regular
that is very interesting and helpful
thank you very much!!! Replies
7
Views
74
Replies
5
Views
83
Replies
0
Views
62
Replies
3
Views
58
Replies
3
Views
288