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 ...
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the completed code.
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.
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.
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.
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:
We want to extract the section of code shown highlighted here into a new subroutine.
Sub CalculateBMIValues()
End Sub
Sub CalculateBMIValues(Weight As Double, Height As Double)
End Sub
Cut these two variables from the ProcessBMIList subroutine.
Sub CalculateBMIValues(Weight As Double, Height As Double)
Dim BMI As Double
Dim BMIBand As String
End Sub
Cut the section of code shown highlighted here.
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
BMI = Weight / (Height * Height)
Pass the values of the WeightKg and HeightM variables to the Weight and Height parameters respectively.
Click the first button and check that the code produces the expected results.
'calculate BMI and BMI band
CalculateBMIValues _
Weight:=WeightKg, _
Height:=HeightM
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.