Page 33 - Excel for Scientists and Engineers: Numerical Methods

10 EXCEL: NUMERICAL METHODS

lSub Initialize(): For J = 1 To N: P(J) = 0: Next J: End Sub J
Figure 1-12. A Sub procedure in one line.

Creating a Simple Custom Function
As a simple first example of a Function procedure, we'll create a custom
function to convert temperatures in degrees Fahrenheit to degrees Celsius.
Function procedures can't be recorded; you must type them on a module
sheet. You can have several macros on the same module sheet, so if you
recorded the ScientificForrnat macro earlier in this chapter, you can type this
custom function procedure on the same module sheet. If you do not have a
module sheet available, insert one by choosing Module from the Insert menu.
Type the macro as shown in Figure 1-13. DegF is the argument passed by the
function from the worksheet to the module (the Fahrenheit temperature); the
single line of VBA code evaluates the Celsius temperature and returns the result
to the caller (in this case, the worksheet cell in which the function is entered).

Function FtoC(DegF)
FtoC = (DegF - 32) * 5 / 9
I End Function
Figure 1-13. Fahrenheit to Celsius custom function.

A note about naming functions and arguments: function names should be
short, since you will be typing them in Excel formulas (that's why Excel's square-
root worksheet function is SQRT) but long enough to convey information about
what the function does. In contrast, command macro names can be long, since
command macros are run by choosing the name of the macro from the list of
macros in the Macro Run dialog box, for example.
Argument names can be long, since you don't type them. Longer names can
convey more information, and thus provide a bit of self-documentation. (If you
look at the arguments used in Excel's worksheet functions, you'll see that single
letters are usually not used as argument names.)

Using a Function Macro

A custom function is used in a worksheet formula in exactly the same way as
any of Excells built-in functions. The workbook containing the custom function
must be open.
Figure 1-14 shows how the FtoC custom function is used. Cell A2 contains
212, the argument that the custom function will use. Cell 82 contains the
formula with the custom function. You can enter the function in cell B2 by