Create your own worksheet functions

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002

If you have a complex calculation that you reuse frequently in Excel, you don't have to repeatedly enter a long, complex worksheet formula. Instead, you can create your own worksheet function to perform the calculation. You can then use the function to create formulas that are easier to enter and maintain.

To create your own custom functions, you work in Microsoft Visual Basic® for Applications (VBA). VBA is a programming language that's built into Excel. VBA is very flexible and can do everything that Excel formulas can do, and more.

For example, say you have a complex formula for figuring sales commissions, where the commission percentage changes based on several factors such as what products and mix of products a representative sells, whether the sales rep is under quota, and the total sales for the quarter. Instead of entering the lengthy formula that takes all of these factors into account every time you want to calculate the commission for a sale, you can create a custom commission function. Then all you have to type in your commission formulas is the name of your function.

Furthermore, the next time your company changes the rules for how commissions are calculated, you don't have to find and make tedious changes to all of your complex formulas. You only have to make changes in one place, the custom function, to update all the formulas in your workbook.

The following steps show how to create and use a custom function. To keep the example simple, this function calculates a sales commission at a flat 6% rate. To take full advantage of a custom function, you would substitute more complex VB code for this simple percentage calculation.

  1. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  2. On the Insert menu in the Microsoft Visual Basic window, click Module.
  3. In the Modulen window, type the code for your function. For example, a function to calculate a 6% sales commission might look like this:

Example of VBA code for a custom function

The first line in the example above creates a function named Commission, which performs its calculations on a number or cell reference. The function uses a variable, MyNum, to store the number or value from the cell. The second line calculates the value of the Commission function by multiplying the number or cell reference (MyNum) by 0.06 (a more sophisticated function could have several lines of code to perform its calculations). The third line ends the function code.

  1. On the File menu, click Close and Return to Microsoft Excel.
  2. On the worksheet, use your function in formulas as you would any worksheet function. For example, you could use the Commission function to calculate the commissions on sales:

Using a custom function in a formula on a worksheet

A function that you create in this manner is available for use only in the workbook where you create it. If you want to use the function elsewhere, you can copy the code for the function to VBA modules in other workbooks; or, if you're a developer, you can copy the function into a workbook used as a function library and compile that workbook as an add in program.

 Note   The example in this article is highly simplified to show you the basics. To create sophisticated functions in VBA, you'll need some knowledge of the Excel 2000 VBA object model, VB language structures, and the VBA environment. For example, you'd want to declare argument and return data types in any functions you create. To start learning about programming in VBA, see the references below.

More information

For more information about creating formulas that include functions, type functions in formulas in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

For information about creating custom functions in VBA, type function procedure in the Office Assistant or on the Answer Wizard tab in the Excel Visual Basic Help window, click Search, and then click topics "Writing a Function Procedure" and "Function Statement."

If you are new to VBA programming, the Microsoft Office 2000/Visual Basic Programmer's Guide has information to help you get started. For information about how to obtain this guide, type programmer's guide in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

 
 
Applies to:
Excel 2003