VBA – 2. If Conditions and Select Case

IF CONDITIONS
iF condition_1 THEN
  'Instructions inside First IF Block
ELSEIF condition_2 Then
  'Instructions inside ELSEIF Block
...
ELSEIF condition_n Then
  'Instructions inside nth ELSEIF Block
ELSE
  'Instructions inside Else Block
END IF

WE CAN ALSO USE IN SEPARATE IF BLOCKS
IF  condtition Then

end if

if condition then

end if

'create a variable to store value from range A3
Dim score_value As Integer

'store the value of range A3 in this variable
score_value = Range("A3").Value

'create if conditions based on the grade
If score_value < 33 Then
MsgBox "fail"

ElseIf score_value >= 33 And score_value < 56 Then 'This is AND condition
MsgBox "avg"

ElseIf score_value >= 56 And score_value < 70 Then
MsgBox "good"

ElseIf score_value >= 70 And score_value < 85 Then
MsgBox "good+"

ElseIf score_value >= 85 And score_value <= 100 Then
MsgBox "best"

Else
MsgBox "na"

End If ' This is the closing End if block

'This could also be written using separate if blocks as shown below

If score_value < 33 Then
MsgBox "fail"
End If


If score_value >= 33 And score_value < 56 Then 'This is AND condition
MsgBox "avg"

End If

'so on....
 Example: 
Dim count As Integer = 0
        Dim message As String

        If count = 0 Then
            message = "There are no items."
        ElseIf count = 1 Then
            message = "There is 1 item."
        Else
            message = "There are " & count & " items."
        End If
Sub IF_Test()
Dim num As Integer
num = WorksheetFunction.RandBetween(1, 10)
If num > 5 Then
  MsgBox num & " is greater than 5"
ElseIf num = 5 Then
  MsgBox num & " is equal to 5"
Else
  MsgBox num & " is less than 5"
End If
End Sub

USING SELECT CASES

SELECT CASE is like if else conditions. instead of different if conditions, we have different cases for difference condition. The expression is written after select case in first line, against which each case will be checked.

Select Case test_expression

   Case condition_1
      result_1

   Case condition_2
      result_2

   ...

   Case condition_n
      result_n

   Case Else
      result_else

End Select

 

EXAMPLE:

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet2”)

Select Case ws.Range(“I1”).Value

Case “Text”
MsgBox “string”

Case 3
MsgBox “number”

Case Else
MsgBox “unknown”

End Select
End Subs

=============================================================

str = Application.InputBox(“Enter Values”, “FO”, Type:=2)
Select Case str
Case “one”
MgBox “you entered one”
Case “two”
MsgBox “you entered two”
Case Else
MsgBox “none of above”

For numeric input :
v = Application.InputBox(“enter”, Type:=1)

Case 1 To 5
MsgBox “between 1 to 5”
Case 8
MsgBox “8”
End Select

=============================================================

Select Case LRegion
Case “N”
LRegionName = “North”
Case “S”
LRegionName = “South”
Case “E”
LRegionName = “East”
Case “W”
LRegionName = “West”
End Select

=============================================================
With the Excel CASE statement, you can also use the To keyword to specify a range of values. For example:
Select Case LNumber
Case 1 To 10
LRegionName = “North”
Case 11 To 20
LRegionName = “South”
Case 21 To 30
LRegionName = “East”
Case Else
LRegionName = “West”
End Select

=============================================================
With the Excel CASE statement, you can also comma delimit values. For example:
Select Case LNumber
Case 1, 2
LRegionName = “North”
Case 3, 4, 5
LRegionName = “South”
Case 6
LRegionName = “East”
Case 7, 11
LRegionName = “West”
End Select

=============================================================
And finally, with the Excel CASE statement, you can also use the Is keyword to compare values. For example:
Select Case LNumber
Case Is < 100
LRegionName = “North”
Case Is < 200
LRegionName = “South”
Case Is < 300
LRegionName = “East”
Case Else
LRegionName = “West”
End Select

Leave a Reply