lmÉpï c£Á°npï ¸À®ÆµÀ£ïì · if statement -> in case of a multiple-line syntax (as...

iTech Analytic Solutions

LmÉPï C£Á°nPï ¸À®ÆµÀ£ïì

No. 9, 1st Floor, 8th Main, 9th Cross,

SBM Colony, Brindavan Nagar, Mathikere, Bangalore – 560 054

Email: [email protected] Website: www.itechanalytcisolutions.com

Mobile: 9902058793

No. 9, 1st Floor, 8

th Main, 9

th Cross, SBM Colony, Brindavan Nagar, Mathikere, Bangalore – 560 054 Page: 2

VBA Codes

Conditional Statements

No. 9, 1st Floor, 8

th Main, 9


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)

No. 9, 1st Floor, 8

th Main, 9


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 theelseif_condition evaluates to True. If elseif_statements are not specified, then no code

will be executed if the elseif_condition evaluates to True.

No. 9, 1st Floor, 8

th Main, 9


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. Ifelse_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

No. 9, 1st Floor, 8

th Main, 9


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

No. 9, 1st Floor, 8

th Main, 9


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"

No. 9, 1st Floor, 8

th Main, 9


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



maths = 50

science = 30

If maths >= 40 Then

If science >= 40 Then


Else


End If

Else

If science >= 40 Then


Else


End If

End If

End Sub

No. 9, 1st Floor, 8

th Main, 9


Option 3 (Multiple If…Then Statements):

This may not be the most efficient method, as explained in Example 2 above.

Sub IfThen3()



maths = 50

science = 30

If maths >= 40 And science >= 40 Then


End If

If maths >= 40 And science < 40 Then


End If

If maths < 40 And science >= 40 Then


End If

If maths < 40 And science < 40 Then


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)

'auto run a VBA code, when content of a worksheet cell changes, with the Worksheet_Change event

If IsEmpty(Target) Then Exit Sub

'if target cell is empty post change, nothing will happen

If Not Intersect(Target, Range("B1:B20")) Is Nothing Then

No. 9, 1st Floor, 8

th Main, 9


'using If Not statement with the Intersect Method to determine if Target cell(s) is within specified range of

"B1:B20"

If IsNumeric(Target) Then

'if target cell is changed to a numeric value

Application.EnableEvents = False

'turn off all events to prevent triggering a recursive loop due to the target change

Target.Interior.Color = RGB(255, 255, 0)

'changes the target cell color to yellow

Application.EnableEvents = True

'enable all events after code is executed

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.

No. 9, 1st Floor, 8

th Main, 9


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"

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

'add MsgBox title "Grading"

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

'using logical operator And in the condition

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

'nesting another If...Then statement

Sub IfThenSingleLine1()

Dim marks As Single

marks = 85

If marks = 85 Then MsgBox "Excellent Marks - 85 on 90": MsgBox "Keep it up!": MsgBox Format(85 / 90 *

100, "0.00") & "% marks"

'Execute multiple statements / codes after Then keyword. Code will return 3 messages: "Excellent Marks - 85

on 90"; "Keep it up!" and "94.44% marks".

End Sub

No. 9, 1st Floor, 8

th Main, 9



Dim marks As Single, avg As Single

marks = 85

avg = 75

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"

'nesting If...Then statements. Code will return the message: "Marks are Excellent, but Average is not"

End Sub


Dim marks As Single

marks = 65

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"

'using the keywords Else If (in single-line syntax), similar to ElseIf (in multiple-line syntax). Procedure will

return the message: "Marks are Good".

End Sub

No. 9, 1st Floor, 8

th Main, 9


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...Casestatement

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.

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)

No. 9, 1st Floor, 8

th Main, 9



Executes different blocks of code or statements, depending on the respective condition(s) being met. It

evaluates an expression and based on its result executes one of the many set of statements. It is very similar

to the If…Then…Else statements.

Syntax

Select Case expression

Case expression_value_1

statements_1

Case expression_value_n

statements_n

Case Else

else_statements

End Select

expression can be a variable, a field or a range. It can be expresesed using a vba function -> as

"IsNumeric(rng)" or "rng.HasFormula", where 'rng' is a Range variable. It should evaluate to a Boolean value,

String value, Numeric value or Date type ie. to any of the basic data types of Boolean, Byte, Integer, Long,

Single, Double, Decimal, Date, Object, String, Variant, ... It is necessary to specify an expression. It is the

value of this expression which is tested and compared (with expression_value) in each Case and when it

matches, the statements specified in the matching Case are executed.

expression_value [expression_value_1 ... expression_value_n] -> the data type of expression_value

should match or be convertible to that of expression. In each Case, the value of the expression is compared

to the expression_value, and if a match is found, the specifiedstatements get executed. It is necessary to

specify atleast one expression_value. Expression_values are tested in the order they are mentioned.

These (expression_value) are like a list of conditions and when a condition is met, the relevant block of code

gets executed.

statements [statements_1 … statements_n] -> statements specified in a particular Case get executed if the

value of the expressionmatches the relevant expression_value of that Case.

No. 9, 1st Floor, 8

th Main, 9


Case Else -> expression_values are tested in the order they are mentioned and if a match is found, its

respective statements get executed and no subsequent expression_value is tested thereafter. If no match is

found for any expression_value, Case Else clause comes into play and the else_statements get executed.

It is Optional to have the Case Else clause.

else_statements -> these statements get executed if no match is found in any of the expression_values. It

is optional to specifyelse_statements. If a match has not been found in any expression_value and

else_statements are not specified also, in this case no code will be executed in the Select...Case

Statements block.

End Select -> terminates the Select...Case block of statements and it is necessary to mention these

keywords at the end.

Example:

Sub selectCase1()

'making age equivalent to "young" will return the message "Less than 40 years"

Dim age As String

age = "young"

Select Case age

Case "senior citizen"

MsgBox "Over 60 years"

Case "middle age"

MsgBox "Between 40 to 59 years"

Case "young"

MsgBox "Less than 40 years"

Case Else

MsgBox "Invalid"

End Select

End Sub

No. 9, 1st Floor, 8

th Main, 9


Using the To keyword to specify the upper and lower range of values

Use the To keyword in expression_value to specify the upper and lower range of matching values, as shown

below. The value to the left of Tokeyword should be less than or equal to the value to the right of the To

keyword. Range can also be specified for character strings.

Example:

Sub selectCaseTo()

'entering marks as 69 will return the message "Average"; entering marks as 101 will return the message "Out

of Range"

Dim marks As Integer

marks = InputBox("Enter marks")

Select Case marks

Case 70 To 100

MsgBox "Good"

Case 40 To 69

MsgBox "Average"

Case 0 To 39

MsgBox "Failed"

Case Else

MsgBox "Out of Range"

End Select

End Sub

No. 9, 1st Floor, 8

th Main, 9


Using the Is keyword (with a comparison operator) to compare values

To include a comparison operator (=, <>, <, >, <=, or >=) in expression_value, use the Is keyword. The Is

keyword is automatically inserted before a comparison operator, if not specifically included. See below

example.

Example:

Sub selectCaseIs()

'if temp equals 39.5, returned message is "Moderately Hot"

Dim temp As Single

temp = 39.5

Select Case temp

Case Is >= 40

MsgBox "Extremely Hot"

Case Is >= 25

MsgBox "Moderately Hot"

Case Is >= 0

MsgBox "Cool Weather"

Case Is < 0

MsgBox "Extremely Cold"

End Select

End Sub

No. 9, 1st Floor, 8

th Main, 9


Using a comma to separate multiple expressions or ranges in each Case

clause

Multiple expressions or ranges can be specified in each Case clause, by separating each expression with a

comma (which has the effect of the OR operator). Multiple expressions or ranges can also be specified for

character strings. See below examples.

Example:

Sub selectCaseMultiple_1()

'if alpha equates to "Hello", the returned message is "Odd Number or Hello"

Dim alpha As Variant

alpha = "Hello"

Select Case alpha

Case a, e, i, o, u

MsgBox "Vowels"

Case 2, 4, 6, 8

MsgBox "Even Number"

Case 1, 3, 5, 7, 9, "Hello"

MsgBox "Odd Number or Hello"

Case Else

MsgBox "Out of Range"

End Select

End Sub

No. 9, 1st Floor, 8

th Main, 9


Example:

In this example, the string comparison "apples" To "grapes" determines a value between "apples" and

"grapes" in alphabetical order, and uses the default text comparison method of Binary (which is case-

sensitive) because Option Compare Statement is not specified - see below.

Sub selectCaseMultiple_2()

'bananas will return the message "Text between apples and grapes, or specifically mangoes, or the numbers

98 or 99"; oranges will return the message "Out of Range"

Dim var As Variant, result As String

var = InputBox("Enter")

Select Case var

Case 1 To 10, 11 To 20: result = "Number is between 1 and 20"

Case "apples" To "grapes", "mangoes", 98, 99: result = "Text between apples and grapes, or specifically

mangoes, or the numbers 98 or 99"

Case Else: result = "Out of Range"

End Select

MsgBox result

End Sub

No. 9, 1st Floor, 8

th Main, 9


Nesting:

Select…Case block of statements can be nested within each other and also with If...Then...Else statements

and VBA loops (as inner or outer loop), without any limit. When Select…Case is nested within the other, it

must be a complete block and terminate with its own End Select, within a specific Case or Case Else clause

of the outer Select...Case block.

Example: (using nested Select...Case statements)

Sub selectCaseNested1()

'check if a range is empty; and if not empty, whether has a numeric value and if numeric then if also has a

formula; and if not numeric then what is the text length.

Dim rng As Range, lnth As Integer

Set rng = ActiveSheet.Range("A1")

Select Case IsEmpty(rng)

Case True

MsgBox rng.Address & " is empty"

Case Else

Select Case IsNumeric(rng)

Case True

MsgBox rng.Address & " has a numeric value"

Select Case rng.HasFormula

Case True

MsgBox rng.Address & " also has a formula"

End Select

Case Else

lnth = Len(rng)

MsgBox rng.Address & " has a Text length of " & lnth

End Select

End Select

End Sub

No. 9, 1st Floor, 8

th Main, 9


Example: (Text Manipulation with nested Conditional Statements and

VBA Loops)

Sub nestingStringManipulation()

'1. remove leading, trailing & inbetween spaces (leave single space between words)

'2. adding space (if not present) after each exclamation, comma, full stop and question mark

'3. capitalize the very first letter of the string and the first letter of a word after each exclamation, full stop and

question mark, while all other letters are lower case

Dim txtLen As Integer, strLen As Integer, n As Integer, i As Integer, ansiCode As Integer, str As String

str = ActiveSheet.Range("A1")

'enter the text string in this range, which is required to be manipulated

str = Application.Trim(str)

'remove leading, trailing & inbetween spaces (leave single space between words), with TRIM function; Note:

the TRIM function removes space character with ANSI code 32, does not remove the nonbreaking space

character with ANSI code 160

str = LCase(str)

'convert string to lower case

'adding space (if not present) after each exclamation, comma, full stop and question mark

txtLen = Len(str)

For n = 1 To txtLen

If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63)

Then

If Mid(str, n + 1, 1) <> Chr(32) Then

str = Mid(str, 1, n) & Chr(32) & Right(str, txtLen - n)

txtLen = Len(str)

End If

End If

Next n

'capitalize the very first letter of the string and the first letter of a word after each exclamation, full stop and

No. 9, 1st Floor, 8

th Main, 9


question mark, while all other letters are lower case

strLen = Len(str)

For i = 1 To strLen

ansiCode = Asc(Mid(str, i, 1))

'determine the ANSI code of each character in the string

Select Case ansiCode

Case 97 To 122

'97 to 122 are the ANSI codes equating to small cap letters "a" to "z"

If i > 2 Then

If Mid(str, i - 2, 1) = Chr(33) Or Mid(str, i - 2, 1) = Chr(46) Or Mid(str, i - 2, 1) = Chr(63) Then

'capitalizes a letter whose position is 2 characters after (1 character after, will be the space character added

earlier) an exclamation, full stop and question mark

Mid(str, i, 1) = UCase(Mid(str, i, 1))

End If

ElseIf i = 1 Then

Mid(str, i, 1) = UCase(Mid(str, i, 1))

End If

Case Else

GoTo skip

'refer below how to use the GoTo statement

End Select

skip:

Next i

ActiveSheet.Range("A2") = str

'the manipulated text string is entered in this range by the procedure

MsgBox str

'the manipulated text string is displayed as message

End Sub

No. 9, 1st Floor, 8

th Main, 9


GoTo statement

Use the GoTo statement to jump to a line within the procedure. The GoTo statement consists of 2 parts: (1)

The GoTo statement which is the GoTo keywords followed by a Label which is the identifier; and (2) The

Label - this consists of the Name of the Label followed by a colon, and then has a line of code. On satisfying a

condition, the GoTo statement transfers control to a separate line of code within the procedure, identified by

the Label. GoTo statement is usually avoidable if there is an alternate solution (usually there is - many times

you can useIf...Then...Else and Select...Case statements alternatively). It makes the code somewhat

unreadable and confusing. It is used best for error handling, viz. "On Error GoTo".

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...Casestatement

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.

lmÉpï c£Á°npï ¸À®ÆµÀ£ïì · if statement -> in case of a multiple-line syntax (as...

Documents