Excel VBA

Conditional Statements in Excel VBA - If...Then...Else Statements (VBA)

User Rating:  / 5
PoorBest 
Details

 

Conditional Statements in Excel 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)

 

 

Related Links:

1. Excel IF Function and IF Statements.

2. Select...Case Statement (VBA).

 


 

 

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 marks As Single

marks = 60


If marks >= 80 Then

MsgBox "Excellent"

ElseIf marks >= 60 And marks < 80 Then

MsgBox "Good"

ElseIf marks >= 40 And marks < 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 marks As Single

marks = 60


If marks >= 80 Then

MsgBox "Excellent"

End If

If marks >= 60 And marks < 80 Then

MsgBox "Good"

End If

If marks >= 40 And marks < 60 Then

MsgBox "Average"

End If

If marks < 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, evenSum As Integer, oddSum As Integer


For n = 1 To 5

i = InputBox("enter number")

If i Mod 2 = 0 Then

evenSum = evenSum + i

Else

oddSum = oddSum + i

End If

Next n


MsgBox "sum of even numbers is " & evenSum

MsgBox "sum of odd numbers is " & oddSum


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 maths As Single, science As Single

maths = 50
science = 30


If maths >= 40 And science >= 40 Then

MsgBox "Pass in both maths and science"

ElseIf maths >= 40 And science < 40 Then

MsgBox "Pass in maths and Fail in science"

ElseIf maths < 40 And science >= 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 maths As Single, science As Single

maths = 50
science = 30


If maths >= 40 Then

If science >= 40 Then

MsgBox "Pass in both maths and science"

Else

MsgBox "Pass in maths and Fail in science"

End If

Else

If science >= 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 maths As Single, science As Single

maths = 50
science = 30


If maths >= 40 And science >= 40 Then

MsgBox "Pass in both maths and science"

End If


If maths >= 40 And science < 40 Then

MsgBox "Pass in maths and Fail in science"

End If


If maths < 40 And science >= 40 Then

MsgBox "Fail in maths and Pass in science"

End If


If maths < 40 And science < 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.

 

'if target cell is empty post change, nothing will happen
If IsEmpty(Target) Then Exit Sub


'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


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 marks > 80 Then MsgBox "Excellent Marks"

 

If marks > 80 Then MsgBox "Excellent Marks" Else MsgBox "Not Excellent"

 

'add MsgBox title "Grading":

If marks > 80 Then MsgBox "Excellent Marks", , "Grading"


'using logical operator And in the condition:

If marks > 80 And avg > 80 Then MsgBox "Both Marks & Average are Excellent" Else MsgBox "Not Excellent"

 

'nesting another If...Then statement:
If marks > 80 Then If avg > 80 Then MsgBox "Both Marks & Average are Excellent"

 

 

Sub IfThenSingleLine1()


Dim marks As Single

marks = 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 marks = 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 marks As Single, avg As Single


marks = 85
avg = 75

 

'nesting If...Then statements. Code will return the message: "Marks are Excellent, but Average is not"
If marks > 80 Then If avg > 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 marks As Single

marks = 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 marks > 80 Then MsgBox "Marks are Excellent" Else If marks >= 60 Then MsgBox "Marks are Good" Else If marks >= 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. 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.