Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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: