Module 3 - Conditions and Loops

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

3.1 - If Statements
3.2 - Logical Tests
3.3 - Select Case Statements
3.4 - Conditional Functions
3.5 - Conditional Loops

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

## 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

'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

'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

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

There is currently no test for this module.