Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this page.
You can click here to download a file containing the completed code.
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.
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:
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.
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.
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.
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.
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:
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
Sub ResetActiveCellFormat()
End Sub
Sub ResetActiveCellFormat()
ActiveCell.Interior.ColorIndex = xlNone
ActiveCell.Font.Color = rgbBlack
ActiveCell.Font.Bold = False
End Sub
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
Click this button to check that the procedure still works.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.