Creating custom functions

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Applies to
Microsoft Office Excel 2003

Book cover


This article is excerpted from Microsoft Office Excel 2003 Inside Out by Craig Stinson and Mark Dodge. Visit Microsoft Learning to buy this book.

In this article

Sample files    You can download sample files that relate to excerpts from Microsoft Office Excel 2003 Inside Out from Microsoft Office Online. This article uses the file TreeOrders.xls.

Although Microsoft Excel includes a multitude of built-in worksheet functions, chances are it doesn't have a function for every type of calculation you perform. Excel's designers couldn't possibly anticipate every calculation need of every user. But they did provide you with the ability to create your own custom functions. In the same way that a macro lets you encapsulate a sequence of actions execute that sequence with a single command, a custom function lets you encapsulate a sequence of calculations so that you can perform those calculations with a single formula.

Custom functions, like macros, use the Visual Basic for Applications (VBA) programming language. They differ from macros in two significant ways. First, they use function procedures instead of sub procedures. They start with a Function statement instead of a Sub statement and end with End Function instead of End Sub. Second, they perform calculations instead of taking actions. Certain kinds of statements (such as statements that select and format ranges) are excluded from custom functions. In this article, you learn how to create and use custom functions.

Getting started

Suppose your company offers a quantity discount of 10 percent on the sale of a product, provided the order is for more than 100 units. In the following paragraphs, you'll build a function to calculate this discount.

Figure 1 shows an order form that lists each item, the quantity, the price, the discount (if any), and the resulting extended price.

Orders template

Figure 1: In column F, we want to calculate the discount for each item ordered.

 Note   The workbook used in this example, TreeOrders.xls, is included in the sample files download.

To create a custom Discount function in this workbook, follow these steps:

  1. Press Alt+F11 to open the Visual Basic Editor (VBE), and then choose Insert, Module. A new module appears, as shown in Figure 2.
  2. In the new module, enter the following code. To make the code more readable, use the Tab key to indent lines. (The indentation is for your benefit only and is entirely optional. The code will run with or without indentation.) After you type an indented line, the VBE assumes that your next line will be similarly indented. To move back out (that is, to the left) one tab character, press Shift+Tab.
Function Discount(quantity, price)
    If quantity >= 100 Then
        Discount = quantity * price * 0.1
    Else
        Discount = 0
    End If
    Discount = Application.Round(Discount, 2)
End Function

Choosing Insert, Module adds a new module to the workbook.

Figure 2: Choosing Insert, Module adds a new module to the workbook.

Using custom functions

Now you're ready to use the new Discount function. Press Alt+F11 to switch back to the worksheet shown in Figure 2. Select cell F9 and enter =Discount(C9,D9). Excel calculates the 10 percent discount on 200 units at $47.50 per unit and returns $950.00.

In the first line of your VBA code, Function Discount(quantity, price), you indicated that the Discount function would require two arguments, quantity and price. When you call the function in a worksheet cell, you must include those two arguments. In the formula =Discount(C9,D9), C9 is the quantity argument, and D9 is the price argument. Now you can copy the discount formula into F10:F15 to get the worksheet shown in Figure 3.

This worksheet shows the result of the Discount custom function.

Figure 3: This worksheet shows the result of the Discount custom function.

What's happening

Let's consider how Excel interprets this function procedure. When you press Enter, Excel looks for the name Discount in the current workbook and finds that it is a procedure in Module1. The argument names enclosed in parentheses—quantity and price—are placeholders for the values on which the calculation of the discount is based.

The If statement in the following block of code examines the quantity argument and determines whether the number of items sold is greater than or equal to 100:

If quantity >= 100 Then
    Discount = quantity * price * 0.1
Else
    Discount = 0
End If

If the number of items sold is greater than or equal to 100, VBA executes the following statement, which multiplies the quantity value by the price value and then multiplies the result by 0.1:

Discount = quantity * price * 0.1

The result is stored as the variable Discount. A VBA statement that stores a value in a variable is called an assignment statement, because it evaluates the expression on the right side of the equal sign and assigns the result to the variable name on the left. Because the variable Discount has the same name as the function procedure, the value stored in the variable is returned to the worksheet formula called the Discount function.

If quantity is less than 100, VBA executes the statement

Discount = 0

Finally, the following statement rounds the value assigned to the Discount variable to two decimal places:

Discount = Application.Round(Discount, 2)

VBA has no Round function, but Excel does. Therefore, to use Round in this statement, you tell VBA to look for the Round method (function) in the Application object (Excel). You do that by adding the word Application before the word Round. Use this syntax whenever you need to access an Excel function from a VBA module.

Understanding custom function rules

A custom function must start with a Function statement and end with an End Function statement. In addition to the function name, the Function statement usually specifies one or more arguments. You may include as many as 29 arguments, using commas to separate them. You can also create a function with no arguments. Excel includes several built-in functions—RAND() and NOW(), for example—that don't use arguments. As you'll see later in this chapter, you can also create functions with optional arguments—arguments that you can either include or omit when you call the function.

Following the Function statement, a function procedure includes one or more VBA statements that make decisions and perform calculations using the arguments passed to the function. Finally, somewhere in the function procedure, you must include a statement that assigns a value to a variable with the same name as the function. This value is returned to the formula that calls the function.

Using VBA keywords in custom functions

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

The one action a function procedure can take (apart from performing calculations) is to display a dialog box. You can use an InputBox statement in a custom function as a means of getting input from the user executing the function. You can use a MsgBox statement as a means of conveying information to the user. You also can use custom dialog boxes, or UserForms—a subject that is beyond the scope of this introduction.

Documenting macros and custom functions

Even simple macros and custom functions can be difficult to read. You can make them easier to understand by entering explanatory text in the form of comments. You add comments by preceding the explanatory text with an apostrophe. For example, Figure 4 shows the Discount function with comments. Adding comments like these makes it easier for you or others to maintain your VBA code as time passes. If you need to make a change to the code in the future, you'll have an easier time understanding what you did originally.

The Discount custom function now includes comments.

Figure 4: The Discount custom function now includes comments.

An apostrophe tells Excel to ignore everything to the right on the same line, so you can place a comment to the right of a VBA statement. You can also insert comments between the statements in a macro or custom function. For example, you might begin a relatively long block of code with a comment that explains its overall purpose and then use inline comments to document individual statements.

Another way to document your macros and custom functions is to give them descriptive names. For example, rather than name a macro Labels, you could name it RelMonthLabels, where Rel indicates that the macro uses relative references and MonthLabels describes the type of label the macro creates. Using descriptive names for macros and custom functions is especially helpful when you've created many procedures, particularly if you create procedures that have similar, but not identical, purposes.

How you document your macros and custom functions is a matter of personal preference. It doesn't matter which method you use, as long as you document them. Documentation is most important for long and complex procedures, for procedures that you look at only once in a while, and for procedures that will be maintained by other people.

Creating custom functions with optional arguments

Some of Excel's built-in functions let you omit certain arguments. For example, if you omit the type and future value arguments from the PV function, Excel still computes the result because those arguments are optional. Your custom functions can also make use of optional arguments.

For example, suppose you want to create a custom function called RightTriangle that uses the Pythagorean theorem to compute the length of any side of a right triangle given the lengths of the other two sides. The equation that expresses the Pythagorean theorem is a2 + b2 = c2, in which a and b are the short sides and c is the hypotenuse. Given any two sides, you can use this equation to solve for the third side.

In a general-purpose Triangle function, you want to accept three arguments (one for each side of the triangle) but make each argument optional so that the user of the function can omit the argument that the function should solve for. The following code does the trick:

Function Triangle(Optional side1, Optional side2, _
    Optional hypotenuse)

    If Not(IsMissing(side1)) And Not (IsMissing(side2)) Then
        Triangle = Sqr(side1 ^ 2 + side2 ^ 2)
    Else
        If Not(IsMissing(side1)) And Not(IsMissing(hypotenuse)) Then
            Triangle = Sqr(hypotenuse ^ 2 - side1 ^ 2)
        Else
            If Not(IsMissing(side2)) And Not(IsMissing(hypotenuse)) Then
                Triangle = Sqr(hypotenuse ^ 2 - side2 ^ 2)
            Else
                Triangle = "Please supply two arguments."
            End If
        End If
    End If
End Function

The first statement names the custom function and the optional arguments side1, side2, and hypotenuse. The following block of code contains a series of If statements that use the VBA IsMissing function to test whether each possible pair of arguments has been supplied and to calculate and return the length of the unknown side:

If Not(IsMissing(side1)) And Not (IsMissing(side2)) Then
    Triangle = Sqr(side1 ^ 2 + side2 ^ 2)

This code tests for the presence of side1 and side2. The IsMissing function returns True if the argument has not been supplied. If side1 is not missing and side2 is not missing, Excel computes the square root of the sum of the squares of the two short sides and returns the length of the hypotenuse to the worksheet.

If fewer than two arguments are supplied, the following statement returns a text string to the worksheet:

Triangle = "Please supply two arguments."

Now let's see what happens when we use this custom function in a worksheet formula. The formula =Triangle(3,4) returns 5.

The hypotenuse argument is omitted, so the function returns the square root of (32 + 42). You could also write the formula =Triangle(3,4,), but the second comma is not necessary. The formula =Triangle(,4,5) returns 3 because the side1 argument is omitted. The formula =Triangle (4,,5) also returns 3.

The function as written has at least two flaws. First, if the user supplies all three arguments, the function behaves as though the third argument were omitted. You might prefer to have it return an error message. Second, the function accepts negative and zero arguments even though triangles cannot have sides of negative or zero length.

You can eliminate the first of these defects by adding the following If … End If block immediately after the Function statement:

If Not(IsMissing(side1)) And Not(IsMissing(side2)) And _
    Not(IsMissing(hypotenuse)) Then
    Triangle = "Please supply only two arguments."
    Exit Function
End If

Note that this block includes an Exit Function statement. This saves the function the trouble of searching for missing argument when it has already discovered that none are missing.

You can use a similar If … End If construction to check for arguments less than or equal to zero, returning an appropriate error message and exiting the function if any are found. Note that other kinds of inappropriate arguments (text, for example) will cause the function to return one of Excel's built-in error constants. If you call the function and offer a text argument, the function returns #VALUE! because it attempts to perform arithmetic operations on a nonarithmetic value.

How much error trapping you add to your custom functions depends, of course, on how much work you want to do and how you plan to use the function. If you're writing a function for your personal use, you might not need to deal with every conceivable aberrant use. If you write the function for others, you'll probably want to eliminate all possibility of error—or at least to try to do so.

Making your custom functions available anywhere

To use a custom function, the workbook containing the module in which you create the function must be open. If that workbook is not open, you get a #NAME? error when you try to use the function. Even if the workbook is open, if you use the function in a different workbook, you must precede the function name with the name of the workbook in which the function resides. For example, if you create a function called Discount in a workbook called Personal.xls, and you call that function from another workbook, you must write =personal.xls!Discount(), not simply =Discount().

You can save yourself some keystrokes (and possible typing errors) by selecting your custom functions from the Insert Function dialog box. (Your custom functions appear in the User Defined category.) An easier way to make your custom functions available at all times is to store them in a separate workbook and then save that workbook as an add-in (an XLA file) in your XLStart folder. (The XLStart folder is a subfolder of the folder containing your Excel files. When you start Excel, the program opens any documents it finds in XLStart.) To save a workbook as an add-in, choose File, Save As (or File, Save). Then choose Microsoft Excel Add-in from the Files Of Type list.

If your user-defined functions are stored in an XLA file that is present in memory, you don't have to specify the name of that file when you call a function. If the XLA file is saved in your XLStart folder, it will be present in memory whenever you run Excel.

 
 
Applies to:
Excel 2003