Page 127 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

Running Function Procedures
If you want to run a Function procedure, you can do so using one of the following methods:

● Use the Function procedure in a formula.
● Call the Function procedure from within another procedure.
● Call the Function procedure from a cell on a worksheet.

Important Your Function procedures don’t appear in the Macros dialog box.

You’ve already seen the first technique several times in the preceding two chapters, and you’ve
no doubt used functions many times in your worksheets. One example of an existing func­
tion you might use in a worksheet would be =NOW(), which returns the current date and
time. The third way to run a Function procedure is to call it from a cell on a worksheet. To
do so, you can call it the same way you would call any other function (for example,
=Amortize(ActiveCell.Value)).

So, when might you want to use a function procedure instead of a Sub procedure to operate
on a value? There are two such times: when you want to use the result of the function in an
expression in your VBA code, or when you want to use the result in a formula in one of your
worksheets. For example, if The Garden Company repackaged potting soil from 25-pound
bags into 5-pound bags, you could create a function that multiplied the number of 25-pound
bags by five to generate the total number of small bags. Then you could create a function such
as this one:

Function SmallBags(intLargeBags as Integer) as Integer
SmallBags = intLargeBags * 5
End Function
Once created, you could call the function from within a cell using the formula
=SmallBags(C16) to convert the number of large bags of potting soil in an order, which
was stored in cell C16, into the number of small bags of potting soil that order will produce.

Passing Arguments to Procedures
So far in this chapter, you’ve seen procedures that operate on fixed values, such as the contents Chapter 5
of a cell, and procedures that don’t operate on any values at all, such as the NOW function.
When you write a procedure that operates on a value from a cell by calling the cell’s value
from inside the procedure using the ActiveCell.Value property or the Range(<cell>).Value
property, you don’t need to worry about passing values from variables. Unfortunately, the sit­
uation won’t always be so straightforward. There might be times where you want to operate

101
Part 2: Visual Basic for Applications