Databases Reference
In-Depth Information
4
Utilizing Functions in Excel
4.1
INTRODUCTION
Functions are the cornerstones of all data analysis. Data analysis requires that calculations take
place. Calculations require functions to execute the computations. In Excel there are two kinds of
functions. The first type of function is that which resides in the cell of a Worksheet. These types
of functions typically carry out mathematical operations utilizing parameters contained within other
Worksheet cells. However, Worksheet cell functions are capable of doing far more than just ordinary
mathematical operations on Worksheet data, and this chapter will introduce the reader to some
techniques for enhancing Worksheet cell functions to accomplish far more than they were ever
intended to. The second type of function is a VBA function, which is simply a function written in
VBA code. What is not known to many users is that VBA functions can in fact be utilized from
within Worksheet cells. The methods for accomplishing this will be covered in this chapter as well.
Functions are wonderful tools for computation, but often not enough thought is given as to what
should be done if something goes wrong when a function tries to calculate a value. Trying to calculate
values utilizing cells that are empty can wreak havoc with even the best intended functions. For nearly
every calculation, there are conditions that will cause it to become unstable and return an invalid
result or crash. Sometimes, determining when such conditions will occur proves to be impossible.
Because such an eventuality can occur, it is best to prepare for it up front whenever possible. The
reader will be shown how to handle errors that can occur when utilizing functions.
4.2
CREATING AND UTILIZING VBA FUNCTIONS IN CODE
Unlike a subroutine, a function is capable of returning a value that can be of any variable type. Most
functions return strings, integers, single or double precision values, or Boolean values (True/False).
Because functions return a value, they are often called by assigning a variable of the same type equal
to them, or substituting a function where a value would normally go. A simple example function:
Function DivideBy(ByVal Numerator As Double, ByVal Denominator
As Double) As Double
'Simple Function to divide two numbers
DivideBy=Numerator/Denominator
End Function
This function will divide two numbers where the numerator and denominator are passed parameters.
The function can be executed from the immediate (or debug) window by typing the following:
Debug.Print DivideBy(3,4)
which will return this result:
0.75
Here the function is placed at the end of the Debug.Print statement where some kind of value is
expected. Functions can also be called by being assigned to a variable. Typing the following in the
immediate window will illustrate this concept:
result = DivideBy(3,4): Debug.Print "result = ";result
113
