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 1 - Getting Started in VBA

Lesson 1.4 - How VBA Works

Topic 1.4.4 - Calling Procedures

As you start to work with larger and more complex programs, you'll find it helpful to organise your code into multiple, separate subroutines. You can make one subroutine "talk to" another by making a call to another routine. This part of the lesson explains how to do this.

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 completed code.

The Example Workbook

Download and open the file linked to in the Files Needed section above. You'll find a list of the 32 teams who played at the 2018 World Cup.

You can click the first button on the worksheet to colour in the four teams who made it to the semi-final stage of the tournament. Click the second button to clear the formatting from these cells.

Duplicating Code

Open the VBE and find the subroutine called HighlightSemiFinalists in Module1.

You'll find the procedure contains a group of three lines of code which is repeated multiple times.

Writing the same code multiple times in the same procedure is bad practice for several reasons:

It's time-consuming (even if you copy and paste).
It makes the procedure much longer than it needs to be.
It makes it difficult to make changes as you have multiple copies of the code to update.

Separating Code into Multiple Procedures

The solution to the problems mentioned above is to move the repeating section of code into a new subroutine. Start by adding a new subroutine at the bottom of the existing module:

Give the subroutine a name which describes what it will do.

Now copy the lines from the original procedure:

Copy the three lines of code shown here.

Paste the copied lines into the new subroutine you have created:

Paste the code into the new subroutine.

Calling a Procedure

Now that you have created a separate procedure, it can be called from any other procedure in the project. To make this work, you'll need to replace the original lines of code with a call to the new subroutine you have created. Start by removing the first set of code you want to replace from the original procedure:

Select and delete these three lines of code from the original procedure.

You can now call the new subroutine by simply writing its name in place of the code you have just deleted. You can press Ctrl + Spacebar to display the IntelliSense list to help you:

Enter the name of the procedure to call it.

Repeat the process with each of the other sections of code you want to replace. The diagram below shows what the finished set of procedures should look like:

The original subroutine calls the new subroutine four times.

The Call Keyword

VBA allows you to optionally add the Call keyword before the name of a procedure that you are calling, as shown below:

You can add the word Call before the name of the subroutine, but this is optional.

The Call keyword only makes a difference when you are passing values to parameters of a procedure that you are calling. You'll learn how to do this in a later module of this course.

Stepping Through a Procedure

You can use the F8 key to step through the HighlightSemiFinalists subroutine:

Press F8 repeatedly until you reach this line.

When your code reaches a line which calls another procedure, executing it will immediately jump to the start of that procedure:

You can now step through each line of this procedure by pressing F8 repeatedly.

When you reach the end of the subroutine that you have called, you can execute the End Sub statement:

Execute the End Sub line to stop execution of the called procedure.

When the called procedure ends, your code will return the calling procedure and continue from the line after the one which made the call:

The code returns to the line immediately after the one which originally called the separate procedure.

You can continue pressing F8 to step through the rest of the procedure, or press F5 to run the complete routine through to the end.

Public and Private Procedures

By default, procedures that you write are public. This means that you can call the procedure from any other module in the same project. You can optionally write the word Public before the word Sub in the definition of the procedure:

Public is the default, so you don't need to write this.

You can use the Private keyword to make a procedure available only to the module in which it is written. This can be helpful in a very large project to stop the IntelliSense list becoming cluttered with many new procedure names:

Private procedures only appear in the IntelliSense list in the module to which they belong.

To practise calling procedures:

Using the same workbook, open Module2 and find the procedure called ClearHighlighting:

Sub ClearHighlighting()

'select Belgium

Range("A4").Select

ActiveCell.Interior.ColorIndex = xlNone

ActiveCell.Font.Color = rgbBlack

ActiveCell.Font.Bold = False

'select Croatia

Range("A8").Select

ActiveCell.Interior.ColorIndex = xlNone

ActiveCell.Font.Color = rgbBlack

ActiveCell.Font.Bold = False

'select England

Range("A11").Select

ActiveCell.Interior.ColorIndex = xlNone

ActiveCell.Font.Color = rgbBlack

ActiveCell.Font.Bold = False

'select France

Range("A12").Select

ActiveCell.Interior.ColorIndex = xlNone

ActiveCell.Font.Color = rgbBlack

ActiveCell.Font.Bold = False

End Sub

Create a new subroutine in Module2 called ResetActiveCellFormat:

Sub ResetActiveCellFormat()

End Sub

Write (or copy and paste) three lines of code to change the formatting of the active cell:

Sub ResetActiveCellFormat()

ActiveCell.Interior.ColorIndex = xlNone

ActiveCell.Font.Color = rgbBlack

ActiveCell.Font.Bold = False

End Sub

Alter the ClearHighlighting subroutine so that it calls the ResetActiveCellFormat procedure four times in the appropriate place:

Sub ClearHighlighting()

'select Belgium

Range("A4").Select

ResetActiveCellFormat

'select Croatia

Range("A8").Select

ResetActiveCellFormat

'select England

Range("A11").Select

ResetActiveCellFormat

'select France

Range("A12").Select

ResetActiveCellFormat

End Sub

In the workbook, check that clicking the Clear Highlighting button still works:

Click this button to check that the procedure still works.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Duplicating Code

Separating Code into Multiple Procedures

Calling a Procedure

The Call Keyword

Stepping Through a Procedure

Public and Private Procedures

Head office

London

Manchester