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
- Simple If statement
- If…Else statement
- If…ElseIf…else statement
- nested if statements
- 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.
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
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
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
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
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
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
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”.
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
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