The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a version of the workbook used in the previous part of this lesson which allows you to add details of films to different worksheets:

Clicking this button adds the film details to one of three different worksheets based on the score assigned to the film.

Open the VBE and find the Add_To_List procedure:

This is the main procedure which is executed when you click the button on Sheet1.

In this part of the lesson we'd like to replace the section of code at the bottom of the Add_To_List procedure with a separate subroutine:

We want to place this section of code in a separate subroutine.

The challenge in this example is that the separate subroutine needs to have access to the values of three variables without using module-level or public variables.

Defining Multiple Parameters

Start by creating a new subroutine called Write_Details in the same module:

Begin a new subroutine in the usual way.

Define a String parameter called Title which will accept the title of the film being added to the list:

Define a single String parameter as shown here.

To add a second parameter you can type a comma after the first and then specify a new name and data type.  Define a Date parameter called WatchDate:

Define the second parameter within the same set of parentheses.

You can continue defining parameters in this way, separating each with a comma.  Define a third parameter called Score using the Integer data type:

This is the full set of parameters that the subroutine will need.

Now we can move the code from the Add_To_List procedure into this new subroutine.  Start by cutting the relevant lines of code from the Add_To_List procedure:

Select and cut the four lines of code shown highlighted here.

Paste the cut code into the Write_Details subroutine:

The entire subroutine should look like this.

Now change the names of the variables in the pasted code to match the names of the parameters.  Start by changing MovieTitle to Title:

Change the variable name highlighted in this diagram to Title.

Change DateWatched to WatchDate and MovieScore to Score:

After changing all three variable names to match the parameter names your code should look like this.

Calling Procedures with Multiple Parameters

Return to the Add_To_List subroutine and replace the code that you cut earlier with a call to the Write_Details subroutine:

Enter the name of the Write_Details subroutine and type a space.

The tooltip which appears as shown in the diagram above indicates that there are three parameters which require a value.  The Title parameter is shown in bold text, indicating that this is the current or active parameter.  We want to pass the value of the MovieTitle variable to the Title parameter:

Enter the name of the MovieTitle variable.

After passing a value to the first parameter, you can type a comma to move to the next one:

Typing a comma moves to the next parameter and highlights its name in bold in the tooltip.

Pass the value of the DateWatched variable to the WatchDate parameter, then type another comma and pass the value of the MovieScore variable to the Score parameter:

Here we've passed a value to each of the three parameters.

You can now test that the code works by clicking the button on Sheet1:

Click the button on Sheet1 to test the code.

You'll find that the details of the film appear in the appropriate worksheet:

The details of the film will appear in the list on the correct worksheet.

Naming Parameters

When you call a subroutine with multiple parameters you may find your code is more readable if you name the parameters to which you are passing values.  You can edit the call to the Write_Details subroutine to provide the name of each parameter:

Write the name of the parameter followed by := before the value you are passing to it. Here we've also used continuation characters to write the instruction on multiple lines.

You can now save and close this workbook.

To practise using multiple parameters:

1. Click here to download the file needed for this section then extract and open the workbook.
2. Open the VBE and find the ProcessBMIList subroutine:

We want to extract the section of code shown highlighted here into a new subroutine.

3. Create a new subroutine called CalculateBMIvalues in the same module:

4. Define two parameters called Weight and Height, each using the Double data type:

5. Return to the ProcessBMIList subroutine and cut the BMI and BMIBand variable declarations:

Cut these two variables from the ProcessBMIList subroutine.

6. Paste the two variables into the CalculateBMIValues subroutine:

7. Return to the ProcessBMIList subroutine and cut the section of code shown highlighted below:

Cut the section of code shown highlighted here.

8. Paste the cut code into the CalculateBMIValues procedure:

9. In the CalculateBMIValues subroutine, change the instruction which calculates the BMI so that it refers to the Weight and Height parameters instead of the WeightKg and HeightM variables:

10. Return to the ProcessBMIList procedure and add a call to the CalculateBMIValues subroutine:

Pass the values of the WeightKg and HeightM variables to the Weight and Height parameters respectively.

11. Return to Excel and click the first button on the worksheet to check that the code works:

Click the first button and check that the code produces the expected results.

12. Return to the ProcessBMIList procedure and edit the call to the CalculateBMIValues subroutine to use named parameters:

13. Check that the code still works then save and close the workbook.  You can click here to download a file containing the completed code.