In this article, I wanted to share some information about the Select Case Decision Structure which is one of the Decision (Condition) Structures in Excel & VBA, a.k.a Excel macros. You can find other articles about Excel & VBA on our blog.
WHAT IS THE SELECT CASE DECISION STRUCTURE?
The Select Case Decision Structure
can be used to control comparisons about a data, just like the IF structure.
The values it returns are going to be True or False. When it is True, the code lines under it work. When it is False, if there are other Case conditions, they are controlled.
If there are other Case conditions and they are True, the codes below them are applied.
If there is no other Case conditions or all the Case conditions have returned False, if there are other codes in the procedure, they are applied by getting out the block with End Select.
Even though it is similar to IF (IF Else ElseIf Then) its type is a bit different and if you have multiple conditions, it is way easier to use compared to IF.
Here is the Syntax..
Blue
parts cannot be changed.
Red parts can be changed.
Green parts are comment lines.
Select Case Data to be controlled
‘Your Codes
End Select
If we are going to have multiple conditions, than Case Is or Case statements come into the play. We also have the Case Else statement that allows us to say “do this if the controlled data is outside indicated conditions.”
Select Case Data to be controlled
Case Is Condition
‘Your codes
Case Is Condition
‘Your codes
Case Is Condition
‘Your codes
Case Else
‘Your codes
End Select
If you want, you can write to codes in the same row after the condition part with : (colon). Case Is Condition: Your Codes
If we use a comparison operator (<; <=; =; >; >=) while indicating a condition, Case Is statement is written for the condition. If not, Case statement will be enough. You will see this situation in the examples below.
We used this statement for the Select Case structure above: “If you have multiple conditions, it is way easier to use compared to IF.”
Let’s give an example of this. We will apply the same conditions with both the IF structure and the Select Case structure and see the difference.
Our conditions will be like this: we have a value and we’ll have it checked. If this value is smaller than 1, we will return the outcome with MsgBox. Likewise, if it is between 1 and 5 or 6 and 10 or 11 and 15, we’ll return the outcome with MsgBox. We will prepare this separately with both of the structures.
Let’s start. First, the codes that we will need if we write our codes depending on the conditions above with the IF structure are:
Sub IF_Yapisi_ile_1()
For i = 1 To 10
If Cells(i, 1).Value < 1 Then
MsgBox "Değer 1'den küçüktür."
ElseIf Cells(i, 1).Value >= 1 And Cells(i, 1).Value <= 5 Then
MsgBox "Değer 1 ile 5 arasındadır."
ElseIf Cells(i, 1).Value >= 6 And Cells(i, 1).Value <= 10 Then
MsgBox "Değer 6 ile 10 arasındadır."
ElseIf Cells(i, 1).Value >= 11 And Cells(i, 1).Value <= 15 Then
MsgBox "Değer 11 ile 15 arasındadır."
End If
Next i
End Sub
If we want to write our codes in different IF rows for each condition and simplify it, then we can write it like this:
Sub IF_Yapisi_ile_2()
For i = 1 To 10
If Cells(i, 1).Value < 1 Then MsgBox "Değer 1'den küçüktür."
If Cells(i, 1).Value >= 1 And Cells(i, 1).Value <= 5 Then MsgBox "Değer 1 ile 5 arasındadır."
If Cells(i, 1).Value >= 6 And Cells(i, 1).Value <= 10 Then MsgBox "Değer 6 ile 10 arasındadır."
If Cells(i, 1).Value >= 11 And Cells(i, 1).Value <= 15 Then MsgBox "Değer 11 ile 15 arasındadır."
Next i
End Sub
Now let’s see how we will write the same conditions with the Select Case statement.
Sub Select_Case_Yapisi_ile()
For i = 1 To 10
Select Case Cells(i, 1).Value
Case Is < 1: MsgBox "Değer 1'den küçüktür."
Case 1 To 5: MsgBox "Değer 1 ile 5 arasındadır."
Case 6 To 10: MsgBox "Değer 6 ile 10 arasındadır."
Case 11 To 15: MsgBox "Değer 11 ile 15 arasındadır."
Case Else: MsgBox "Değer 15'den daha büyüktür."
End Select
Next i
End Sub
We saw that we can execute the action we want with three different codes and we can increase this number, but I hope that you can see the difference between them.
With the IF structure, we had to indicate Cells(i, 1).Value –the cell address that we were going to have controlled and we wrote it many times to indicate the condition between two numbers. But with Select Case, it was enough for us to write the cell address that we were going to have controlled and it was very easy to indicate the condition between two numbers as 1 To 5.
Let’s see the outcome with the Select Case structure in a GIF.
CODE SAMPLES
You can apply the Select Case application samples below and test how it works and comprehend its logic better.
Sub Makro1()
Select Case "Murat" = "Emre"
Case True: MsgBox "İfade Doğrudur."
Case False: MsgBox "İfade Yanlıştır."
End Select
End Sub
Sub Makro2()
Dim Değer As Integer
Değer = 2
Select Case Değer
Case 1: MsgBox "Değer 1'dir."
Case 2: MsgBox "Değer 2'dir."
Case 3: MsgBox "Değer 3'tür."
Case Else: MsgBox "Değer Mevcut Değil."
End Select
End Sub
Sub Makro3()
Dim Değer As Integer
Değer = 1
Select Case Değer
Case Is < 1: MsgBox "Değer Küçüktür."
Case Is = 1: MsgBox "Değer Eşittir."
Case Is > 1: MsgBox "Değer Büyüktür."
End Select
End Sub
Sub Makro4()
Dim Metin As String
Metin = "PEAKUP"
Select Case Metin
Case "Excel": MsgBox "Metin Excel'dir"
Case "PEAKUP": MsgBox "Metin PEAKUP'tır."
Case "VBA": MsgBox "Metin VBA'dır."
End Select
End Sub
Sub Makro5()
Dim Değer As Integer
Değer = 14
Select Case Değer
Case Is < 1: MsgBox "Değer 1'den küçüktür."
Case 1, 2, 3, 4, 5: MsgBox "Değer 1 ile 5 arasındadır."
Case 6, 7, 8, 9, 10: MsgBox "Değer 6 ile 10 arasındadır."
Case 11, 12, 13, 14, 15: MsgBox "Değer 11 ile 15 arasındadır."
Case Else: MsgBox "Değer 15'den daha büyüktür."
End Select
End Sub
Sub Makro6()
Dim Değer As Integer
Değer = 20
Select Case Değer
Case Is < 1: MsgBox "Değer 1'den küçüktür."
Case 1 To 9: MsgBox "Değer 1 ile 9 arasındadır."
Case 10 To 19: MsgBox "Değer 10 ile 19 arasındadır."
Case 20 To 29: MsgBox "Değer 20 ile 29 arasındadır."
Case Else: MsgBox "Değer 29'dan büyüktür."
End Select
End Sub
Sub Makro7()
Dim Değer As Long
Değer = 15
Select Case Değer
Case Is < 1: MsgBox "Değer 1'den küçüktür."
Case 1 To 4, 5 To 9: MsgBox "Değer 1 ile 9 arasındadır."
Case 10 To 14, 15 To 19: MsgBox "Değer 10 ile 19 arasındadır."
Case 20 To 24, 25 To 29: MsgBox "Değer 20 ile 29 arasındadır."
Case Else: MsgBox "Değer 29'dan büyüktür."
End Select
End Sub
Sub Makro8()
Dim Tanım As String
Tanım = "C"
Select Case Tanım
Case Is < "E": MsgBox Tanım & " harfi E harfinden önce gelir."
Case Is > "E": MsgBox Tanım & " harfi E harfinden sonra gelir."
End Select
End Sub
Sub Makro9()
Dim Tanım As String
Tanım = "E"
Select Case Tanım
Case "A" To "M": MsgBox Tanım & " harfi A ile M harfleri arasındadır."
Case "N" To "Z": MsgBox Tanım & " harfi N ile Z harfleri arasındadır"
Case Else: MsgBox "Case Else"
End Select
End Sub
You can cat more detailed information about the Select Case statement on Microsoft Docs.
See you in other articles, bye. 🙋🏻♂️
You can share this post with your friends and help them get informed as well.👍🏻
feature of Excel.
Outlook
Excel
PowerPoint
Word
Project
Visio
OneNote
Access
