Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Many people around the world enjoy and benefit from our tutorials. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Module 3 - Conditions and Loops

This module introduces two important VBA concepts: testing conditions, and doing something repeatedly (looping).

3.1 - If Statements
3.1.1 - Simple If Statements
3.1.2 - Block If Statements
3.1.3 - The Else Clause
3.1.4 - ElseIf Conditions
3.1.5 - Nested If Statements

3.2 - Logical Tests
3.2.1 - Combining Conditions - Or
3.2.2 - Combining Conditions - And
3.2.3 - The Not Operator

3.3 - Select Case Statements
3.3.1 - The Select Case Statement

3.4 - Conditional Functions
3.4.1 - The IIf Function
3.4.2 - The Switch Function

3.5 - Conditional Loops
3.5.1 - Basic Do Loops
3.5.2 - Do Until Loops
3.5.3 - Do While Loops

Choose what you want to learn from the list of lessons above.

This page provides a brief summary of what you've learned in this module. You can click here to download the example code shown below.

If Statements

You can write a basic If statement to perform an action if a condition is met.

If Range("A1").Value = "" Then Exit Sub

You can write a block If statement to perform multiple actions if a condition is met.

If Range("A1").Value = "" Then

Range("A1").Interior.Color = rgbPink

Range("A1").Select

Exit Sub

End If

You can use the Else clause to perform actions if the condition is not met.

If Range("A1").Value = "" Then

Range("A1").Interior.Color = rgbPink

Range("A1").Select

Exit Sub

Else

Range("A1").ClearFormats

End If

You can use ElseIf to test multiple conditions in the same block If statement.

If Range("A1").Value > Range("B1").Value Then

Range("C1").Value = "Player 1 wins"

ElseIf Range("B1").Value > Range("A1").Value Then

Range("C1").Value = "Player 2 wins"

Else

Range("C1").Value = "Draw"

End If

You can nest If statements.

If IsNumeric(Range("A1").Value) Then

If Range("A1").Value < 0 Then

Range("A2").Value = "Negative number"

Exit Sub

End If

Else

Range("A2").Value = "Not a number"

Exit Sub

End If

Logical Tests

You can combine conditions using the Or operator.

If Range("A1").Value < 0 _

Or Range("A1").Value > 10 Then

Exit Sub

End If

You can combine conditions using the And operator.

If Month(Now) = 12 And Day(Now) = 25 Then

Range("A1").Value = "Tis the season"

End If

You can use the Not operator to change True to False and vice versa.

If Not IsDate(Range("A1").Value) Then

Range("A2").Value = "Not a date"

Exit Sub

End If

Select Case Statements

You can write a Select Case statement to assign a value to one of a set of discrete categories.

Select Case Month(Date)

Case 1

Range("A1").Value = "Jan"

Case 2

Range("A1").Value = "Feb"

Case 3

Range("A1").Value = "Mar"

Case 4

Range("A1").Value = "Apr"

Case 5

Range("A1").Value = "May"

Case 6

Range("A1").Value = "Jun"

Case 7

Range("A1").Value = "Jul"

Case 8

Range("A1").Value = "Aug"

Case 9

Range("A1").Value = "Sep"

Case 10

Range("A1").Value = "Oct"

Case 11

Range("A1").Value = "Nov"

Case 12

Range("A1").Value = "Dec"

Case Else

Range("A1").Value = "Unknown"

End Select

You can test multiple values in each case.

Select Case Month(Date)

Case 12, 1, 2

Range("A1").Value = "Winter"

Case 3, 4, 5

Range("A1").Value = "Spring"

Case 6, 7, 8

Range("A1").Value = "Summer"

Case 9, 10, 11

Range("A1").Value = "Autumn"

Case Else

Range("A1").Value = "Unknown"

End Select

You can test a range of values in each case.

Select Case Month(Date)

Case 1 To 3

Range("A1").Value = "Quarter 1"

Case 4 To 6

Range("A1").Value = "Quarter 2"

Case 7 To 9

Range("A1").Value = "Quarter 3"

Case 10 To 12

Range("A1").Value = "Quarter 4"

Case Else

Range("A1").Value = "Unknown"

End Select

Conditional Functions

You can use the IIf function to return a value based on a logical test.

Range("A2").Value = IIf( _

Expression:=Range("A1").Value < 0, _

TruePart:="Negative", _

FalsePart:="Positive")

You can use the Switch function to test multiple conditions in the same expression.

Range("A2").Value = Switch( _

Range("A1").Value < 3, "Rubbish", _

Range("A1").Value < 6, "Average", _

Range("A1").Value < 8, "Good", _

True, "Great")

Conditional Loops

You can use a Do - Loop statement to execute instructions repeatedly, with an Exit Do statement to stop the loop.

Range("A1").Select

'do something useful

ActiveCell.Offset(1, 0).Select

If ActiveCell.Value = "" Then Exit Do

Loop

You can use a Do Until - Loop statement to automatically exit the loop when the condition returns True.

Range("A1").Select

Do Until ActiveCell.Value = ""

'do something useful

ActiveCell.Offset(1, 0).Select

Loop

You can use a Do - Loop Until statement to test the condition at the end of the loop.

Range("A1").Select

'do something useful

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = ""

You can use a Do While - Loop statement to automatically exit the loop when the condition returns False.

Range("A1").Select

Do While ActiveCell.Value <> ""

'do something useful

ActiveCell.Offset(1, 0).Select

Loop

You can use a Do - Loop While statement to test the condition at the end of the loop.

Range("A1").Select

'do something useful

ActiveCell.Offset(1, 0).Select

Loop While ActiveCell.Value <> ""

This page contains reference material for the code used in this module.

Comparison Operators

The table below shows a list of comparison operators that you can use in logical tests:

Operator	Description
=	Is equal to
<>	Is not equal to
>	Is greater than
<	Is less than
>=	Is greater than or equal to
<=	Is less than or equal to

Logical Operators

The table below shows a list of logical operators you can use in VBA:

Operator	Description
Or	Combines two logical tests. Returns True when either or both logical tests returns True.
And	Combines two logical tests. Returns True when both logical tests return True.
Not	Changes True to False and vice versa.
Xor	Combines two logical tests. Returns True when only one of the logical tests returns True.
Eqv	Combines two logical tests. Returns True when both logical tests return the same result.

Try doing one or more of the following exercises for this module:

Exercise 3.01 Exercise 3.02 Exercise 3.03

There is currently no test for this module.

This page has 0 threads

Add a new post

MODULES

LESSONS

If Statements

Logical Tests

Select Case Statements

Conditional Functions

Conditional Loops

Comparison Operators

Logical Operators

Head office

London

Manchester