Conditional Statements in Excel VBA
Related Links:
1. Excel IF Function and IF Statements.
2. Select…Case Statement (VBA).
There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation.
If…Then…Else Statements (VBA)
Select…Case Statement (VBA)
—————————————————————————
Contents:
Multiple-line statements – Syntax
Single-line If…Then…Else Statements
Select…Case compared to If…Then…Else Statements
—————————————————————————
Executes a block of code or statements, if the specified condition is met.
Multiple-line statements – Syntax
If condition Then
statements
ElseIf elseif_condition_1 Then
elseif_statements_1
ElseIf elseif_condition_n Then
elseif_statements_n
Else
else_statements
End If
If statement -> In case of a multiple-line syntax (as above), the first line should have only the If statement. See below for single-line syntax.
condition -> an expression (could be numeric or string) which evaluates to True or False (note: a Null condition is equated to False). It is necessary to specify a condition.
statements -> one or more statements (block of code) get executed if the condition evaluates to True. If statements are not specified, then no code will be executed if the condition evaluates to True.
ElseIf -> this clause can be used (optionally) if you want to test for multiple conditions. It is necessary to specify elseif_condition if ElseIf is present. Any number of ElseIf and elseif_conditions can be present.
elseif_condition [elseif_condition_1 … elseif_condition_n] -> an expression (could be numeric or string) which evaluates to True or False (note: a Null elseif_condition is equated to False). It is necessary to specify this if ElseIf is present.
elseif_statements [elseif_statements_1 … elseif_statements_n] -> one or more statements (block of code) get executed if the elseif_condition evaluates to True. If elseif_statements are not specified, then no code will be executed if the elseif_condition evaluates to True.
Else -> condition and elseif_conditions are tested in the order they are mentioned and if any one evaluates to True, its respective statements get executed and no subsequent condition is tested thereafter. If no previous condition or elseif_condition evaluates to True, Else clause comes into play and the else_statements get executed. It is Optional to include Else in the If…Then…Else statement.
else_statements -> one or more statements (block of code) get executed if no previous condition or elseif_condition evaluates to True. If else_statements are not specified, then no code will be executed if it was applicable based on the conditions.
End If -> terminates the If…Then…Else block of statements and it is necessary to mention these keywords at the end.
Nesting:
If…Then…Else block of statements can be nested within each other and also with Select…Case statement and VBA Loops (as inner or outer loop), without any limit. It may be noted that in re. of spreadsheet functions, Excel 2003 only allows 7 levels of nesting of functions, while Excel 2007 allows up to 64.
Example 1 (ElseIf Structure):
Sub ElseIfStructure()
‘this procedure returns the message “Good”, if marks are equal to 60.
Dim sngMarks As Single
sngMarks = 60
If sngMarks >= 80 Then
MsgBox “Excellent”
ElseIf sngMarks >= 60 And sngMarks < 80 Then
MsgBox “Good”
ElseIf sngMarks >= 40 And sngMarks < 60 Then
MsgBox “Average”
Else
MsgBox “Poor”
End If
End Sub
Example 2 (Multiple If…Then Statements):
Multiple If…Then Statements (in this Example 2) can be used alternatively to the earlier example (Example 1) of ElseIf Structure, but it is not a very efficient method. Multiple If…Then Statements compared to ElseIf structure: In Multiple If…Then statements, VBA runs through each of the If…Then blocks even after encountering a True condition (and executing its associated statements), whereas in an ElseIf structure all subsequent conditions are skipped after encountering a True condition. In this sense, the ElseIf structure is faster. Thus, Multiple If…Then statements might not a very efficient method if the same can be done with the ElseIf structure.
Sub multipleIfThenStmnts()
‘this procedure returns the message “Good”, if marks = 60.
Dim sngMarks As Single
sngMarks = 60
If sngMarks >= 80 Then
MsgBox “Excellent”
End If
If sngMarks >= 60 And sngMarks < 80 Then
MsgBox “Good”
End If
If sngMarks >= 40 And sngMarks < 60 Then
MsgBox “Average”
End If
If sngMarks < 40 Then
MsgBox “Poor”
End If
End Sub
Example 3 (Nesting If…Then…Else Statements within a For…Next Loop):
Sub IfThenNesting()
‘accept five integers from user, add the even numbers and odd numbers separately
Dim i As Integer, n As Integer, iEvenSum As Integer, iOddSum As Integer
For n = 1 To 5
i = InputBox(“enter number”)
If i Mod 2 = 0 Then
iEvenSum = iEvenSum + i
Else
iOddSum = iOddSum + i
End If
Next n
MsgBox “sum of even numbers is ” & iEvenSum
MsgBox “sum of odd numbers is ” & iOddSum
End Sub
Example 4 (Test multiple variables):
To test multiple variables with the If…Then statements, below are 3 alternative methods:
Option 1 (ElseIf Structure):
Sub IfThen1()
‘this procedure returns the message “Pass in maths and Fail in science”
Dim sngMaths As Single, sngScience As Single
sngMaths = 50
sngScience = 30
If sngMaths >= 40 And sngScience >= 40 Then
MsgBox “Pass in both maths and science”
ElseIf sngMaths >= 40 And sngScience < 40 Then
MsgBox “Pass in maths and Fail in science”
ElseIf sngMaths < 40 And sngScience >= 40 Then
MsgBox “Fail in maths and Pass in science”
Else
MsgBox “Fail in both maths and science”
End If
End Sub
Option 2 (If…Then…Else Nesting):
Sub IfThen2()
‘this procedure returns the message “Pass in maths and Fail in science”
Dim sngMaths As Single, sngScience As Single
sngMaths = 50
sngScience = 30
If sngMaths >= 40 Then
If sngScience >= 40 Then
MsgBox “Pass in both maths and science”
Else
MsgBox “Pass in maths and Fail in science”
End If
Else
If sngScience >= 40 Then
MsgBox “Fail in maths and Pass in science”
Else
MsgBox “Fail in both maths and science”
End If
End If
End Sub
Option 3 (Multiple If…Then Statements):
This may not be the most efficient method, as explained in Example 2 above.
Sub IfThen3()
‘this procedure returns the message “Pass in maths and Fail in science”
Dim sngMaths As Single, sngScience As Single
sngMaths = 50
sngScience = 30
If sngMaths >= 40 And sngScience >= 40 Then
MsgBox “Pass in both maths and science”
End If
If sngMaths >= 40 And sngScience < 40 Then
MsgBox “Pass in maths and Fail in science”
End If
If sngMaths < 40 And sngScience >= 40 Then
MsgBox “Fail in maths and Pass in science”
End If
If sngMaths < 40 And sngScience < 40 Then
MsgBox “Fail in both maths and science”
End If
End Sub
Example 5:
Using If IsEmpty, If Not and If IsNumeric (in If…Then…Else statements) in the Worksheet_Change event.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Using If IsEmpty, If Not and If IsNumeric (in If…Then statements) in the Worksheet_Change event.
‘auto run a VBA code, when content of a worksheet cell changes, with the Worksheet_Change event.
On Error GoTo ErrHandler
Application.EnableEvents = False
‘if target cell is empty post change, nothing will happen
If IsEmpty(Target) Then
Application.EnableEvents = True
Exit Sub
End If
‘using If Not statement with the Intersect Method to determine if Target cell(s) is within specified range of “B1:B20”
If Not Intersect(Target, Range(“B1:B20”)) Is Nothing Then
‘if target cell is changed to a numeric value
If IsNumeric(Target) Then
‘changes the target cell color to yellow
Target.Interior.Color = RGB(255, 255, 0)
End If
End If
Application.EnableEvents = True
ErrHandler:
Application.EnableEvents = True
Exit Sub
End Sub
Using NOT Operator with IF statement viz. “If Not …”
Using the NOT Operator on Boolean expressions, reverses the True value to False and vice-versa. Using the Not Operator reverses the logic of the If Statement – from True to False or from False to True. Ref to the above example, If Not Intersect(Target, Range(“B1:B20”)) Is Nothing Then actually means:- If Intersect(Target, Range(“B1:B20”)) Is Something Then or If Intersect(Target, Range(“B1:B20”)) Is Not Nothing Then. In simpler words it means: If the changed Range intersects or falls within the specified range (“B1:B20”) Then.
Single-line If…Then…Else Statements
You can use the single-line syntax for short and simple constructs. To distinguish between a multiple-line syntax and single-line syntax: if in the first line If statement, nothing follows the Then keyword on the same line, it is multiple-line, otherwise single-line.
Syntax (single-line):
If condition Then statements Else else_statements
If…Then…Else block of statements can be nested within each other in a single-line syntax also.
A clause similar to ElseIf (in multiple-line syntax) can be inserted by using the keywords Else If (in single-line syntax).
End If keywords are not required to end the procedure, in the single-line syntax.
To include multiple statements or multiple else_statements, separate each by a colon.
Examples of using single-line syntax for If…Then…Else Statements:
If sngMarks > 80 Then MsgBox “Excellent Marks”
If sngMarks > 80 Then MsgBox “Excellent Marks” Else MsgBox “Not Excellent”
‘add MsgBox title “Grading”:
If sngMarks > 80 Then MsgBox “Excellent Marks”, , “Grading”
‘using logical operator And in the condition:
If sngMarks > 80 And sngAvg > 80 Then MsgBox “Both Marks & Average are Excellent” Else MsgBox “Not Excellent”
‘nesting another If…Then statement:
If sngMarks > 80 Then If sngAvg > 80 Then MsgBox “Both Marks & Average are Excellent”
Sub IfThenSingleLine1()
Dim sngMarks As Single
sngMarks = 85
‘Execute multiple statements / codes after Then keyword. Code will return 3 messages: “Excellent Marks – 85 on 90”; “Keep it up!” and “94.44% marks”.
If sngMarks = 85 Then MsgBox “Excellent Marks – 85 on 90”: MsgBox “Keep it up!”: MsgBox Format(85 / 90 * 100, “0.00”) & “% marks”
End Sub
Sub IfThenSingleLine2()
Dim sngMarks As Single, sngAvg As Single
sngMarks = 85
sngAvg = 75
‘nesting If…Then statements. Code will return the message: “Marks are Excellent, but Average is not”
If sngMarks > 80 Then If sngAvg > 80 Then MsgBox “Both Marks & Average are Excellent” Else MsgBox “Marks are Excellent, but Average is not” Else MsgBox “Marks are not Excellent”
End Sub
Sub IfThenSingleLine3()
Dim sngMarks As Single
sngMarks = 65
‘using the keywords Else If (in single-line syntax), similar to ElseIf (in multiple-line syntax). Procedure will return the message: “Marks are Good”.
If sngMarks > 80 Then MsgBox “Marks are Excellent” Else If sngMarks >= 60 Then MsgBox “Marks are Good” Else If sngMarks >= 40 Then MsgBox “Marks are Average” Else MsgBox “Marks are Poor”
End Sub
For live codes of running If…Then…Else Statements, click to download excel file.
Select…Case compared to If…Then…Else Statements
Both are Conditional Statements, wherein one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation.
The difference lies in that in a Select…Case statement, a single expression (or variable) is considered and evaluated at a time. The variable to be evaluated is determined in the first line of “Select Case expression”, and then multiple Case statements specify the possible values. Whereas in If…Then…Else statements, multiple expressions (or variables) can be considered and evaluated simultaneously. Select…Case statement tests a single item for several possible values, whereas If…Then…Else statements test multiple items for several possible values. In this sense, If…Then…Else statements are more flexible in testing multiple variables for multiple conditions.
In case of a large number of conditions, If…Then…Else statements might get and appear confusing and the code tends to become unreadable.