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 ScientificFormat 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).

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.)

Was this article helpful?

0 0

Post a comment