Lesson 4: Function Procedures

Lesson Objectives

Upon completion of this lesson, the participant will be able to:

• Explain why Function procedures differ from Sub procedures.
• Define a function that accepts a variable-length argument list or has optional arguments.
• Write a function that accepts an array of values and returns an array of values to a worksheet.

Some Topics to be introduced in this lesson include:

• How Function procedures differ from Sub procedures
• Returning data
• Arrays

Function Procedures

Function procedures differ from sub procedures in that functions may return a value while subs may not. Further, function procedures must be called from a Macro, they will not appear on the Tools-Macros menu.

Function procedures are declared using the following structure:

Function Name (arguments)

{Function code}

Name = {The return value}

End Function

For example, this function macro accepts a temperature entered as Fahrenheit and returns the equivalent Celsius temperature:

Function FtoC (Temperature)

FtoC = (Temperature - 32) / 1.8

End Function

The accessibility of the functions depends on how they are declared when they are defined. By default, the functions have Public access, and can be used by any module within the library that contains the function. If the keyword Private is placed before Function, that function may only be called from within the same module.

Passing Data to a Function Procedure

When an argument is specified without a data type, VB will assume that the argument is a reference of the Variant data type. VB will perform the necessary conversion based on what is passed but will cause VB to allocate more memory than is probably needed. To explicitly specify the data type, include it in the argument list:

Function FtoC (Temperature as Integer)

Optional Arguments

Function arguments can be made optional by using the Optional keyword in front of the argument name when declared in the Function statement.

Function MyFunction(arg1, arg2, Optional arg3, Optional arg4)

All arguments that follow this argument in the list must also be optional and must be declared using the Optional keyword. Optional arguments must be of the variant data type. The IsMissing function can be used to test whether an optional argument was included in the argument list when a function is called.

The following is an example of using Option and IsMissing, which sets a default of "10" for the second number if IsMissing returns a true:

Function AddEm(Number1, Optional Number2)

If IsMissing(Number2) Then Number2 = 10

AddEm = (Number1 + Number2)

End Function

Lesson 4 Exercises

1. Explain the difference between Sub procedures and Function procedures.

Only one difference: function will return a value.

2. How can a programmer check if an optional argument was passed to a function?

He/she can use the IsMissing function within an If statement to check to see if the argument has been passed to the function. IsMissing will return True if no argument was passed and will return False if it was.

3. Create a function that returns the square root of a number multiplied by 6.

Function MyPoser(pNumber)
MyPower = pNumber ^ .05 * 6
End Function