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.

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

### 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 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 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.

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

### 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 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:

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: Select the code shown highlighted here.

1. Replace the code you have selected with a call to the BMICategory 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: Refer to the BMI variable then close the parentheses. 