Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 3 - Conditions and Loops

Lesson 3.1 - If Statements

Topic 3.1.2 - Block If Statements

In the previous part of this lesson you saw how to use an If statement to perform a single action when a condition is met. In practice, you'll often want to perform a series of instructions based on the result of a condition. This part of the lesson shows you how to use a Block If statement to do exactly that.

Files Needed

You can click here to download the file for this page.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the file linked to in the Files Needed section above. The workbook contains the same example as you used in the previous part of this lesson:

You can click the button to add the details you have entered in column B to the table starting in column D.

Our aim, as in the previous example, is to prevent the user from adding incomplete records to the table.

Creating a Block If Statement

Open the VBE and locate the subroutine called Add_To_List. You'll find that it contains a simple If statement to check if cell B2 is empty:

If cell B2 is empty, the code will execute the Exit Sub statement.

If we want to do more than just exit the subroutine when cell B2 is empty we'll find it difficult to add more instructions to the same line of code. Instead, we'll convert the existing simple If statement into a block If:

To begin, move the Exit Sub statement to a new line below the If statement. It's conventional to indent this line one extra tab space.

When you separate the If statement from the instruction you want it to perform, you must also add an End If statement. Try running your code without the End If statement:

You'll see this fairly descriptive error message. Click OK and then stop running the procedure.

Add the End If statement as shown below:

Write the End If statement on a new line. It's conventional to write this at the same indent level as the original If statement.

Make sure that cell B2 is empty and then use the F8 key to step through the code:

Press F8 until you reach this line.

If B2 is empty, pressing F8 again will cause the code to move to the instruction below the If statement:

You can press F8 again to end the subroutine.

Now try adding a value to cell B2 (and optionally, B4):

Enter any value into cell B2.

Return to the VBE and press F8 to begin stepping through the subroutine:

Press F8 until you reach this line.

If B2 isn't empty, pressing F8 again will jump to the End If statement:

The code will skip any instructions between the If and End If lines when the condition is not met.

You can press F5 to continue running the code to the end of the procedure:

Return to Excel to see that the details have been added to the list.

Adding More Instructions

You can add as many instructions as you like between the If and End If statements. Let's make it more obvious to the user that they can't proceed without adding a film name to cell B2:

Add a line which changes the colour of cell B2. It's important that this line appears above the Exit Sub statement.

We can also help the user by selecting the cell into which they need to write a film name:

Automatically selecting cell B2 means the user can simply begin typing.

Finally, we can display an error message in another cell - we'll use cell A6 for this example:

You can enter a different message if you prefer.

In Excel, remove the value from cell B2 and then click the button on the worksheet:

After clicking the button, your worksheet should look like this. Cell A6 has already been formatted with a red font colour.

Tidying Up

It's worth adding some code to remove your problem indicators when the user has correctly filled in a film name. Add two instructions below the End If statement:

The two new instructions will only be executed when the Exit Sub statement is not executed, i.e. when cell B2 is not empty.

Add a new film name to cell B2 and click the button on the worksheet:

When you have entered a value in cell B2 your code will automatically tidy up the worksheet.

To practise writing block If statements:

Using the same subroutine, create a new block If statement below the existing End If statement. Use this new If statement to check if cell B3 is empty:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

Add instructions to select and change the colour of cell B3:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

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

Range("B3").Select

Add an instruction to enter a message in cell A6:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

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

Range("B3").Select

Range("A6").Value = "Enter a date!"

Enter an instruction to exit from the subroutine and then end the If statement:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

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

Range("B3").Select

Range("A6").Value = "Enter a date!"

Exit Sub

End If

Below the End If statement, in the section of code which tidies up the worksheet, change the instruction which resets the colour of cell B2 to include cell B3 as well:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

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

Range("B3").Select

Range("A6").Value = "Enter a date!"

Exit Sub

End If

Range("B2:B3").Interior.ColorIndex = xlNone

Range("A6").ClearContents

In Excel, clear the contents of cell B3 then click the button on the worksheet to check that your code works:

This is what you should see if you haven't entered a value in cell B3.

Enter a value in cell B3 and check that your code works when you click the button:

The worksheet should be reset when you have entered a value in cell B3.

Add two more block If statements to enter default values if the user doesn't enter a review in cell B4 or a reviewer in cell B5. The entire procedure is shown below:

Sub Add_To_List()

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

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

Range("B2").Select

Range("A6").Value = "Enter a film name!"

Exit Sub

End If

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

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

Range("B3").Select

Range("A6").Value = "Enter a date!"

Exit Sub

End If

Range("B2:B3").Interior.ColorIndex = xlNone

Range("A6").ClearContents

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

Range("B4").Value = "None"

End If

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

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

End If

'Select the next blank cell in column D

Range("D1048576").End(xlUp).Offset(1, 0).Select

'Copy the entered values into the table

ActiveCell.Value = Range("B2").Value

ActiveCell.Offset(0, 1).Value = Range("B3").Value

ActiveCell.Offset(0, 2).Value = Range("B4").Value

ActiveCell.Offset(0, 3).Value = Range("B5").Value

End Sub

Test that the procedure works then save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Creating a Block If Statement

Adding More Instructions

Tidying Up

Head office

London

Manchester