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
548 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 an earlier lesson you saw how to define parameters in a subroutine. You can use the same technique to define parameters for a function and this part of the lesson shows you how.
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 list of characters for whom we want to calculate some statistics related to their health:
You can click the button to calculate the BMI and Category for each character.
In the VBE you can find a subroutine called ProcessBMIList which performs the calculations for each character in the list:
The code loops over the list of characters and performs two calculations for each person.
We'd like to create functions to perform the calculations for the BMI and BMI category. Each function will need access to values stored in variables in the ProcessBMIList procedure. We'll create parameters to allow the functions to accept those values.
We'll begin by creating a function to calculate the BMI of a character. This function will return a value of the Double data type. Create a function called BodyMassIndex in the Module1:
Create the function and define its return type as shown here.
The BodyMassIndex function needs to know the weight and height of the character in order to calculate a result. You can define parameters for a function in the parentheses immediately after the function's name. Define a parameter called Weight with a data type of Double:
Define a parameter in the parentheses after the name of the function.
You can define multiple parameters in the parentheses after a function's name as long as you separate each parameter using a comma. Define a parameter called Height which uses the Double data type:
Enter a comma after the first parameter and then define a new parameter as shown here.
The next job is to write code to instruct the function which value to return. The code for the BodyMassIndex function is relatively simple:
Enter the function name followed by an = operator then the expression which calculates the result.
You can use the Immediate window to test the function before you call it in another procedure. If you can't see the Immediate window, choose View | Immediate Window from the menu:
Enter a question mark followed by the function name and open a set of parentheses. The tooltip shows you the parameters of the function.
You can pass values to the parameters then close the parentheses and press Enter to see the result:
The result of the function will be returned to the Immediate window.
Now we can call the BodyMassIndex function in the ProcessBMIList subroutine. Find the code shown highlighted in the diagram below:
Find the code shown highlighted here in the ProcessBMIList subroutine.
Replace the highlighted code with a call to the BodyMassIndex function:
Enter the function name followed by an open parenthesis.
You can then pass the values held in the WeightKg and HeightM variables to the Weight and Height parameters of the function:
Pass the variables into the function parameters then close the parentheses.
You can now return to Excel and test that the code still works:
Click the first button on the worksheet to test that the code works.
To practise using functions with parameters:
Function BMICategory() As String
End Function
Function BMICategory(BMIValue As Double) As String
End Function
Function BMICategory(BMIValue As Double) As String
BMICategory =
End Function
Function BMICategory(BMIValue As Double) As String
BMICategory = Switch( _
BMIValue < 18.5, "Underweight", _
BMIValue < 25, "Healthy weight", _
BMIValue < 30, "Overweight", _
BMIValue >= 30, "Obese")
End Function
Select the code shown highlighted here.
Enter the function name followed by an open parenthesis.
Refer to the BMI variable then close the parentheses.
Click the first button on the worksheet to test that the code works.
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.