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:

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:

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:

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:

Create a new subroutine in the usual way.

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

Cut this section of code from the Add_To_List procedure.

Paste the cut code into the Go_To_Sheet subroutine:

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:

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 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:

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

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 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:

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:

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

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:

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:

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

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:

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:

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:

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

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:

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

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

Cut the line shown highlighted here.

4. Paste the cut code into the Add_Formatted_Date procedure:

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

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

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

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

8. Pass the DateWatched variable to the DateToFormat parameter:

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

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

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

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

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

12. Save and close the workbook.