Page 192 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 178

EXCEL DATA ANALYSIS

CREATE A CUSTOM FUNCTION

f you cannot find an Excel function to accomplish a You can also use functions with the code you create for a
desired data analysis task, you can create a custom macro within the Visual Basic Editor. You can create
I function within the Visual Basic Editor. You can also functions to return a value to the procedure that calls them.
create functions to use with your other VBA subroutines. Unlike subroutines, you cannot call functions directly from
the Macro dialog box. When working with macros, only a
When you create a public function in the Visual Basic Editor, subroutine can call a function. Like subroutines, functions
it appears in the Insert Function dialog box within Excel consist of blocks of VBA code grouped together to perform
under the User Defined category. You can use these VBA a common task or series of tasks.
functions directly in your worksheet to create formulas in
the same fashion that you use the built-in functions that At first glance, the value of a function may appear
come standard with Excel. Keep in mind that a VBA function somewhat limiting. But unlike subroutines, which do not
that you create is only available when the corresponding return a value, functions always return a value, making them
workbook containing the function is open in Excel. ideal for performing calculations. For example, you can
Therefore, if you create a specific function that you want to create a function that always calculates the sales tax for an
use with all your workbooks, you must add it to the item and returns that amount.
Personal Macro Workbook — Personal.xls, which
always opens with Excel — to ensure that it is always
available.
CREATE A CUSTOM FUNCTION

⁄ Click Insert ➪ Module to ¤ Type Function. › In parentheses, type the Á Type the data type to be
create a blank module in a names of the function returned by the function.
project. ‹ Type the name of your parameters.
function. ‡ Press Enter.
Note: For a worksheet function, ˇ Type As.
place the module in the
Personal.xls project.

178