In some situations it is necessary to repeat several conditions to create an If statement with ElseIf.
Sub Grade() Dim Score As Integer Dim Grade As String Score = Inputbox("What was your test score?") If Score < 2 Then Grade = "E" ElseIf Score < 4 Then Grade = "D" ElseIf Score < 6 Then Grade = "C" ElseIf Score < 8 Then Grade = "B" ElseIf Score <= 10 Then Grade = "A" Else Msgbox "Please insert a value between 0 and 10" End if Msgbox "Your grade was " & Grade & "." End Sub
The variable Score was repeated several times in the code to create the conditions. One way to simplify repetitions is by using Select Case (notice that the variable you want to compare will be omitted and implicitly replicated for all statements).
Sub GradeSCase() Dim Score As Integer Dim Grade As String Score = Inputbox("What was your test score?") Select Case Score Case Is < 2: Grade = "E" 'Equivalent to ElseIf Score < 2 Then: Grade = "E" Case Is < 4: Grade = "D" Case Is < 6: Grade = "C" Case Is < 8: Grade = "B" Case Is <= 10: Grade = "A" Case Else Msgbox "Please insert a value between 0 and 10" End Select Msgbox "Your grade was " & Grade & "." End Sub
Note that it is possible that multiple Case conditions result in True (E.g. in the case of the example above, if Score is equal to 7, shall result True in <8 and <=10). In this case the behavior of Case will be the same as in True in ElseIf.
The Case Else statement will only be executed if there is no True value.
You can specify multiple cases for the same Case by separating them with a comma, or by using To to create a range.
Sub Bank_Line_Time() Dim LineN As Integer LineN = Inputbox("How many people are in front of you in the queue (1 a 10)?") Select Case LineN Case 1 'Equivalent to ElseIf LineN = 1 Then MsgBox "You are next!" Case 2 To 5 'Equivalent to ElseIf LineN >= 2 And ElseIf LineN <= 5 Then MsgBox "You will be attended soon" Case 6, 7, 8 'Equivalent to ElseIf LineN = 6 Or LineN = 7 Or LineN = 8 Then MsgBox "Moderate wait time" Case Else MsgBox "You will not be attended today" End Select End Sub
Note that when Case is used, an comparison operators is required. If the operator is omitted Case (alone), Excel will assume an equality comparison.
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2023 SuperExcelVBA | ABOUT