Module 4 - Variables, Parameters and Functions
Lesson 4.3 - Parameters
Topic 4.3.1 - Basic Parameters

In previous lessons we've used module-level and project-level variables to make values and objects available to multiple procedures.  As your projects get larger and more complex you may find it becomes difficult to manage lots of public and module-level variables.  An alternative way to allow procedures to share information is to use parameters.  A parameter allows you to pass information into a subroutine when it is called and this part of the lesson explains how.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

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

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a system that allows you to add a new film to a different list based on the score assigned to it:

System

Click the button to assign the film to one of the Great, OK and Rubbish worksheets based on its score.

 

Open the VBE and find the Add_To_List subroutine in Module1:

Add to list

The complete Add_To_List subroutine.

The subroutine contains three variables which are used to capture the values entered into cells B2, B3 and B4.  The MovieScore variable is used in an If statement to check which worksheet the details should be copied into.  We'd like to extract this If statement into a separate subroutine:

If statement

We want to move this part of the procedure into a separate subroutine.

 

The challenge here is to ensure that the separate subroutine has access to the value of the MovieScore variable without using a module-level or project-level variable!

Creating a Separate Subroutine

We'll start by creating a separate subroutine called Go_To_Sheet in the same module:

New sub

Create a new subroutine in the usual way.

 

The new subroutine does not need to be in the same module as the original procedure.  You can call a subroutine from any module in the project as long as it has not been declared as Private.

Now we can cut the relevant code from the Add_To_List subroutine:

Cut code

Cut this section of code from the Add_To_List procedure.

 

Paste the cut code into the Go_To_Sheet subroutine:

Paste code

The Go_To_Sheet subroutine should now look like this.

 

The problem at this point is that the code in the Go_To_Sheet subroutine refers to a variable to which it has no access.  We'll solve this problem by providing the subroutine with a parameter.

Defining a Parameter

Defining a parameter is a lot like declaring a variable with two noticeable differences:

  • You don't use the Dim, Private or Public keywords;
  • You define a parameter in the parentheses immediately after the procedure's name.

We'll define a parameter called ScoreToTest in the definition of the Go_To_Sheet subroutine.  Start by positioning the cursor inside the parentheses after the subroutine's name and then enter the name of the parameter you want to create:

Parameter name

Enter the parameter name inside the parentheses.

 

After the parameter name you can define what type of data it should be capable of storing by writing As followed by a data type.  We want the ScoreToTest parameter to have the same data type as the original MovieScore variable so we'll use Integer:

Define type

Define the type of parameter you want, just as you would define the type when you declare a variable.

 

The last thing we need to do in the Go_To_Sheet subroutine is change any reference to the original MovieScore variable to refer to our new ScoreToTest parameter:

Replace names

The Go_To_Sheet subroutine contains two references to the MovieScore variable. Make sure you change them both to refer to the ScoreToTest parameter.

 

We could have called the parameter MovieScore instead of ScoreToTest to avoid having to change the code within the Go_To_Sheet subroutine.  While it's acceptable to use the same name for variables and parameters, you may find that it becomes confusing when you do so!  In this course we'll try to make sure that variable names and parameter names are different.

Calling a Procedure with Parameters

Now that we've created the Go_To_Sheet subroutine, we need to call it from within the Add_To_List subroutine.  Find the appropriate place in the Add_To_List subroutine as shown below:

Call sub

Call the Go_To_Sheet subroutine by entering its name in the appropriate place in the Add_To_List subroutine. Remember that you can press Ctrl + Spacebar to find your subroutine names in the IntelliSense list.

 

Next, we must pass a value into the parameter that we have defined.  Start by typing a space after the name of the procedure that you are calling:

Tooltip

The tooltip shows you information about the parameter that you have defined.

 

For our example, we'll pass the value of the MovieScore variable into the ScoreToTest parameter:

Pass value

Enter the name of the MovieScore variable to pass its value into the ScoreToTest parameter.

 

You could pass any Integer value into the ScoreToTest subroutine - the value you pass into a parameter does not need to be stored in a variable.

Using the Call Keyword

In an earlier module of this course you saw that you can optionally use the Call keyword when you call another subroutine.  You may find a problem when you use the Call keyword and attempt to pass a value to a parameter:

Call error

You'll encounter a syntax error if you attempt to use the Call keyword as shown here.

 

The solution to this problem is to make sure that you enclose the value you are passing to the parameter in a set of parentheses:

Call parentheses

If you use the Call keyword, you must enclose values passed to the procedure in a set of parentheses.

 

You may find it helpful to revisit lessons 1.4.2 Parameters and Arguments and 1.4.3 Using Parentheses to remind yourself of the rules!

Running the Code

You can test that your code works by clicking the button on the worksheet and checking that the information appears on the correct worksheet according to the score:

Result

This film appears on the Great worksheet as its score was 8 or higher.

 

You can press F8 to step through the Add_To_List subroutine in the VBE and use debugging techniques to see how the value is passed into the Go_To_Sheet procedure.  Make sure that you select Sheet1 in Excel before you do this:

Step through

You can position the mouse cursor over the MovieScore variable to see its value. You can also see the value of each variable in the Locals window.

 

When you press F8 with the line shown above highlighted, your code will jump to the Go_To_Sheet subroutine:

Step through sub

You can use the same techniques to check the value of the ScoreToTest parameter.

 

Note that you can't step through the Go_To_Sheet subroutine alone as it requires a value to be passed to its parameter.  You can step through the code in the Go_To_Sheet subroutine when it is called from another procedure.

Summary

Using parameters requires a little more effort than using module-level or project-level variables but it provides several advantages:

  • A parameter makes a value available to only those procedures which require it, rather than all of the procedures in a module or project.
  • Parameters make a procedure more easily portable: you can copy a procedure into a new project without needing to check if you have also copied all the required module-level and project-level variables.
  • Parameters help to avoid cluttering the IntelliSense list with lots of module-level and project-level variable names.

To practise using parameters we'll add a separate subroutine which formats the date in the DateWatched variable before adding it to the worksheet:

  1. Create a new subroutine called Add_Formatted_Date in the same module:

Sub Add_Formatted_Date()

 

End Sub

  1. In this new subroutine, define a parameter called DateToFormat using the Date data type:

Sub Add_Formatted_Date(DateToFormat As Date)

 

End Sub

  1. Return to the Add_To_List procedure and cut the line of code shown highlighted in the diagram below:
Cut code

Cut the line shown highlighted here.

 
  1. Paste the cut code into the Add_Formatted_Date procedure:

Sub Add_Formatted_Date(DateToFormat As Date)

 

ActiveCell.Offset(0, 1).Value = DateWatched

 

End Sub

  1. In the line you have just pasted, change the reference to the DateWatched variable to refer to the DateToFormat parameter:

Sub Add_Formatted_Date(DateToFormat As Date)

 

ActiveCell.Offset(0, 1).Value = DateToFormat

 

End Sub

  1. Add an instruction to the Add_Formatted_Date procedure to change the NumberFormat property of the cell into which the date has been written:

Sub Add_Formatted_Date(DateToFormat As Date)

  

ActiveCell.Offset(0, 1).Value = DateToFormat

  

ActiveCell.Offset(0, 1).NumberFormat = "dd mmm yyyy"

  

End Sub

  1. In the Add_To_List subroutine, replace the line of code you cut earlier with a call to the Add_Formatted_Date subroutine:
Call

Write the name of the subroutine or press Ctrl + Spacebar and select it from the IntelliSense list.

 
  1. Pass the DateWatched variable to the DateToFormat parameter:
Pass value

Type a space after the subroutine you're calling and write the name of the DateWatched variable.

 
  1. Test that your code works by clicking the button on Sheet1:
Test code

Change the film details if you want to avoid adding a duplicate record.

 
  1. Check that the new film's date is written in a different format to the original record:
Different format

The format of the new date should be different to that of the previous rows.

 
  1. Save and close the workbook.
This page has 0 threads Add post