Module 4 - Variables, Parameters and Functions
Lesson 4.3 - Parameters
Topic 4.3.2 - Multiple Parameters

In the previous part of the lesson you were introduced to the concept of parameters.  This part of the lesson expands on this concept by explaining how to define multiple parameters in the same procedure.

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 version of the workbook used in the previous part of this lesson which allows you to add details of films to different worksheets:

Click button

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:

Add to list

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:

Separate code

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:

New sub

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 Title

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:

Second parameter

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:

Third parameter

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:

Cut code

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

 

Paste the cut code into the Write_Details subroutine:

Paste code

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 variable names

Change the variable name highlighted in this diagram to Title.

 

Change DateWatched to WatchDate and MovieScore to Score:

Changed variables

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:

Call sub

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:

Pass value

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:

Next parameter

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:

Pass all values

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 button

Click the button on Sheet1 to test the code.

 

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

Result

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:

Name parameters

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

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

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

Sub CalculateBMIValues()

 

End Sub

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

Sub CalculateBMIValues(Weight As Double, Height As Double)

 

End Sub

  1. Return to the ProcessBMIList subroutine and cut the BMI and BMIBand variable declarations:
Cut variables

Cut these two variables from the ProcessBMIList subroutine.

 
  1. Paste the two variables into the CalculateBMIValues subroutine:

Sub CalculateBMIValues(Weight As Double, Height As Double)

 

Dim BMI As Double

Dim BMIBand As String

 

End Sub

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

Cut the section of code shown highlighted here.

 
  1. Paste the cut code into the CalculateBMIValues procedure:

Sub CalculateBMIValues(Weight As Double, Height As Double)

 

Dim BMI As Double

Dim BMIBand As String

 

BMI = WeightKg / (HeightM * HeightM)

 

BMIBand = Switch( _

BMI < 18.5, "Underweight", _

BMI < 25, "Healthy weight", _

BMI < 30, "Overweight", _

BMI >= 30, "Obese")

 

'write values into worksheet

ActiveCell.Offset(0, 3).Value = BMI

ActiveCell.Offset(0, 4).Value = BMIBand

 

End Sub

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

BMI = Weight / (Height * Height)

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

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

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

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

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

'calculate BMI and BMI band

CalculateBMIValues _

Weight:=WeightKg, _

Height:=HeightM

  1. Check that the code still works then save and close the workbook.  You can click here to download a file containing the completed code.
This page has 0 threads Add post