Module 3 - Conditions and Loops

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

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

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

This page has 0 threads Add post