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.

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):

MultipleIf…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 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 = 50sngScience = 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 = 50sngScience = 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 = 50sngScience = 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 valueIf IsNumeric(Target) Then

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.

'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"

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.