Some General Issues Concerning Functions

 

Introduction.  In the last webpage we made use of user defined functions to compute some expectations for distributions.   We defined the function in the Visual Basic IDE and passed information to it so that it could return a value to the spreadsheet or another function.  Now we need to discuss some of the more general syntactic essentials for developing user defined functions.

As we have seen, a user can develop a function that can be used

  • in an expression in another procedure to return a value

  • in a formula in a spreadsheet

There is some syntax for declaring a function

[Public|Private][Static] Function name [(argumentlist)] [As type]

VisualBasic code

End Function

The parameters in the declaration are discussed in the table below.

Parameter   Description
Public|Private Optional Public = The function procedure can be accessed in any other procedure in all modules in all active Excel projects.  Functions are Public by default.

Private = The function procedure is accessible only to other procedures in the same module.
Static Optional Indicates that the values within the function remain the same between calls.
Function Required This is the keyword that says the procedure returns a value or other data.
name Required Represents any valid function name.  Valid function names are the same as valid variable names.  When the function is completed the result is assigned to this name.
argumentlist Optional This represents a list of one or more variables that are passed into the function.  Commas are used to separate the items in the list.  The argumentlist needs to be enclosed in parentheses.
type Optional This indicates the data type of the value returned by the function.
End Function Required A keyword that indicates the end of the function procedure.

It is also possible to have other conditions within the function so that you can use an Exit Function command to leave the function early as we will see in the next webpage.

Some Different Argument Lists.  There are a large variety of possibilities when developing argument lists.

  • Arguments can be variables, arrays, constants, literals or expressions.

  • Some functions do not have arguments.

  • Some functions have a fixed number of arguments.  It is possible to have up to 60 argument.

  • Some functions have a combination of required and optional arguments.

The following table contains a variety of possible argument lists.

 

Type Sample Function Declaration Discussion
None Function Spacer() This function doesn't require any information from elsewhere in order to work.
One Function CubeRoot(ANumber) as Double This function finds the cube root of ANumber and returns it as a double precision value.
Two Values Function NRoot(ANumber, ARoot) This function finds the n-th root of a number
Two Arrays Function MeanDist(Values, Probabilities) This function actually brings in two arrays in order to return the mean of a distribution.
Optional Function Spacer(Optional ANumber) This function produces a certain number of spaces if no number is passed.  If ANumber is passed then it determines the number of spaces.

 

Whenever you use an Optional argument you will almost surely use the IsMissing built-in function within the function to determine whether the the argument was omitted.  You can include many optional arguments in the argument list if you want, but each must be preceded by the keyword Optional.