Module 4 - Variables, Parameters and Functions
Lesson 4.4 - Functions
Topic 4.4.2 - Function Parameters

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.

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 list of characters for whom we want to calculate some statistics related to their health:

List of data

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:

Original code

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.

Creating a Basic Function

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 function

Create the function and define its return type as shown here.

 

Defining Function Parameters

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

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:

Multiple parameters

Enter a comma after the first parameter and then define a new parameter as shown here.

Calculating a Value to Return

The next job is to write code to instruct the function which value to return.  The code for the BodyMassIndex function is relatively simple:

Return value

Enter the function name followed by an = operator then the expression which calculates the result.

Testing the Function

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:

Test function

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:

Result of test

The result of the function will be returned to the Immediate window.

 

Calling the Function in a Procedure

Now we can call the BodyMassIndex function in the ProcessBMIList subroutine.  Find the code shown highlighted in the diagram below:

Find code

Find the code shown highlighted here in the ProcessBMIList subroutine.

 

Replace the highlighted code with a call to the BodyMassIndex function:

Call 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 values

Pass the variables into the function parameters then close the parentheses.

 

You can now return to Excel and test that the code still works:

Test code

Click the first button on the worksheet to test that the code works.

To practise using functions with parameters:

  1. In Module1 create a new function called BMICategory which returns a String value:

Function BMICategory() As String

 

End Function

  1. Define a parameter called BMIValue which uses the Double data type:

Function BMICategory(BMIValue As Double) As String

 

End Function

  1. Begin writing an instruction which tells the function to return a value:

Function BMICategory(BMIValue As Double) As String

 

BMICategory =

 

End Function

  1. Complete the instruction using the Switch function to calculate the correct BMI category:

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

  1. Return to the ProcessBMIList subroutine and find the section of code shown highlighted in the diagram below:
Code to replace

Select the code shown highlighted here.

 
  1. Replace the code you have selected with a call to the BMICategory function:
Call function

Enter the function name followed by an open parenthesis.

 
  1. Pass the value of the BMI variable to the BMIValue parameter of the BMICategory function:
Pass value to parameter

Refer to the BMI variable then close the parentheses.

 
  1. Return to Excel and test that the code still works:
Test code

Click the first button on the worksheet to test that the code works.

  1. Save and close the workbook.
This page has 0 threads Add post