Conditions in VBA

Conditions in VBA

(Conditional statements in VBA)

Decision-making statements are those statements that control the flow of execution. Generally Program execution flow is from Top to Bottom. When we want to execute some of the statements or groups of the statement on the base of the condition at that time Decision-making statements are used. Simple fundamental in Decision-making statements is that execute the statement only if the condition becomes true.

In VBA there is mainly five type of conditional statements

  1. Simple If statement
  2. If…Else statement
  3. If…ElseIf…else statement
  4. nested if statements
  5. switch statement

1. Simple If statement in VBA

It is one of the simplest conditional statement.

If we want to execute a programming statement only when the condition becomes true at that time, we can use a simple “if” statement.

Flow chart of simple if condition

The flowchart is the pictorial presentation of program.

Flow chart of simple if condition in MS Excel VBA

Syntax of simple if condition in VBA

If condition Then
   Statement 1
   .....
   .....
   Statement n
End If

Where…

condition: create a condition on that basis, Statements are executed. it may contain a condition with comparison operator.
For Example,
X > Y, X=0

Simple if condition in VBA video in Hindi


Example of simple if: Display Greater value using simple If

Example of simple if in MS Excel VBA

Write below code in click event of button.

Private Sub cmdBttnGetValue_Click()
    Dim x As Integer
    Dim y As Integer
    x = 33
    y = 73
    If x > y Then
        MsgBox ("X is Greater then Y")
    End If
    
    If y > x Then
        MsgBox ("Y is Greater then X")
    End If
End Sub

Output:
Click on button or click on run button to run program

Example of simple if in MS Excel VBA

2. If…Else statement in VBA

This type of conditional statement has two different parts that are “True Part” and “False Part”. If condition will be true then statements of the true part will be executing and If condition will be false then statements of the false part will be executed.

If…Else condition in VBA video in Hindi


Flow chart of If…Else statement

Flow chart of If-Else condition in MS Excel VBA

Syntax of If…Else statement

If condition Then
   Statement 1
   .....
   .....
   Statement n
Else
   Statement 1
   .....
   .....
   Statement n
End If

Example of If…Else:

Get Value from Cell and display Grater variable Message in Cell

Example of If Else condition in MS Excel VBA

Write below code in click event of button

Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim y As Integer
    
    x = Range("C3").Value
    y = Range("C4").Value
    
    If x > y Then
        Range("C5").Value = "X is Grater"
    Else
        Range("C5").Value = "Y is Grater"
    End If
End Sub

3. If…ElseIf…else statement in VBA

This type of conditional statement has a sequence of conditions. If one condition became false then its control goes to another condition and so on. At any point in time one condition becomes true at that time, the statements below that condition will be executed and whole if condition will end.

If…ElseIf…else condition in VBA video in Hindi


Flow chart of If…ElseIf…else statement

Flow chart of If-ElseIf-else condition in MS Excel VBA

Syntax of If…ElseIf…else statement

If condition1 Then
   Statement 1
   .....
   .....
   Statement n
ElseIf condition2 Then
   Statement 1
   .....
   .....
   Statement n
ElseIf condition3 Then
   Statement 1
   .....
   .....
   Statement n 
ElseIf condition4 Then
   Statement 1
   .....
   .....
   Statement n
Else
   Statement 1
   .....
   .....
   Statement n
End If

Example of If…ElseIf…else statement

Enter Day Number and Display Day name.
1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday

Example of If-ElseIf-else condition in MS Excel VBA

Write below code in click event of button

Private Sub CommandButton1_Click()
    Dim day As Integer
    
    day = Range("B3").Value
    
    If day = 1 Then
        Range("B4").Value = "Monday"
    ElseIf day = 2 Then
        Range("B4").Value = "Tuesday"
    ElseIf day = 3 Then
         Range("B4").Value = "Wednesday"
    ElseIf day = 4 Then
         Range("B4").Value = "Thursday"
    ElseIf day = 5 Then
         Range("B4").Value = "Friday"
    ElseIf day = 6 Then
         Range("B4").Value = "Saturday"
    ElseIf day = 7 Then
         Range("B4").Value = "Sunday"
    Else
        MsgBox ("Please Enter Day Number Between 1 to 7")
    End If
End Sub

4. Nested if statements

Nested if means If or ElseIf conditions are within another If condition.

Nested if condition in VBA video in Hindi


Syntax of nested if statement

If condition Then
   If condition Then
   Statement 1
   .....
   .....
   Statement n
Else
   If condition Then
   Statement 1
   .....
   .....
   Statement n
 End If
End If
Else
   If condition Then
   Statement 1
   .....
   .....
   Statement n
 End If
End If

Example of nested if:

Enter any Value and display Message which stated that enter value is “Negative”, “Positive” or “Zero”.

Example of Nested if condition in MS Excel VBA

Write below code in click event of button

Private Sub CommandButton1_Click()
    Dim x As Integer
    x = Range("B3").Value
    
    If x >= 0 Then
        If x = 0 Then
            Range("B4").Value = "Enter No. is Zero"
        Else
            Range("B4").Value = "Enter No. is Positive"
        End If
    Else
        Range("B4").Value = "Enter No. is Negative"
    End If
    
End Sub

5. Switch Case or Select Case statement

A select case statement is the best alternate of If condition when we want to test the equality of variables’ value against a list of values.

The compression operator will never be used in the select case.

Switch Case or Select Case statement in VBA video in Hindi


Syntax of Switch Case or Select Case statement

Select Case expression
   Case Value1
      statement1
      statement2
      ....
      statement1n
   Case Value2
      statement1
      ....
      ....
   Case ValueN
      statement1
      ....
      ....   
   Case Else
      statement1
      ....
      ....
End Select

Example Switch Case or Select Case statement

Enter Day Number and Display Day name.
1 = January, 2 = February, 3 = March, 4 = April, 5 = May, 6 = June, 7 = July, 8 = August, 9 = September, 10 = October, 11 = November, 12 = December

Example of Select Case statement in MS Excel VBA

Write below code in click event of button

Private Sub CommandButton1_Click()
    Dim monthNo As Integer
    Dim msg As String
    
    monthNo = Range("B3").Value
    
    Select Case monthNo
        Case 1
            Range("B4").Value = "January"
        Case 2
            Range("B4").Value = "February"
        Case 3
            Range("B4").Value = "March"
        Case 4
            Range("B4").Value = "April"
        Case 5
            Range("B4").Value = "May"
        Case 6
            Range("B4").Value = "June"
        Case 7
            Range("B4").Value = "July"
        Case 8
            Range("B4").Value = "August"
        Case 9
            Range("B4").Value = "September"
        Case 10
            Range("B4").Value = "October"
        Case 11
            Range("B4").Value = "November"
        Case 12
            Range("B4").Value = "December"
        Case Else
            msg = MsgBox("Plase Enter Month No. between 1 to 12", vbInformation)
            Range("B4").Value = ""
    End Select
End Sub