Page 128 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

on a value that’s stored in a variable in a procedure, not in a worksheet cell. That’s when you
need to tell the procedure the values on which you want it to operate; those values are called
arguments.
You probably noticed that the first line of the SmallBags function looked different from most
of the other procedures you’d seen earlier in the chapter. The following function, which rec­
ommends a retail price of an item that’s 180 percent of the item’s wholesale price, also takes
an argument:

Function MarkupPrice(curItemPrice as Currency) as Currency
MarkupPrice = curItemPrice * 1.8
End Function
Let’s take a moment to break down the first line in the function. The Function keyword is
familiar, as is the function name that follows it, but the next two elements are new. The element
in the parentheses, curItemPrice as Currency, is the name and data type of the variable
that’s being passed to the function. In other words, regardless of how the function gets its
variable, it knows to treat the value it receives as a variable of type Currency.

Caution As with other functions, if the data the function receives is of the incorrect type,
the function will return a #VALUE! error message in the worksheet cell where the function
is called.

The last element of the first Function procedure statement is the second occurrence of as
Currency. That element tells the function the data type of the value it returns to the formula
or procedure that called it. Most of the time the result of the procedure will be returned as the
same data type as the value passed to the procedure, but you might want to divide a single by
an integer and return an integer value. One situation where that would be the case would be
if you have 22.3 pounds of potting soil and want to see how many full 5-pound bags you
could make out of it.
So where is the value calculated by the procedure stored? It’s stored in a variable with the
same name as the Function procedure. In the code listed earlier, the second line executes the
arithmetic.

MarkupPrice = intLargeBags * 1.8
The MarkupPrice variable is created using the data type named at the end of the first state­
Chapter 5
ment in the procedure.
If you need to pass more than one argument to a procedure, you do so by separating the argu­
ments by commas, as in the following example:

Function ConvertMultiple (sngKrona as Single, sngEuro as Single) as Single

102
Part 2: Visual Basic for Applications