Declaring a function

The official syntax for declaring a function is as follows:

[Public | Private][Static] Function name ([arglist]) [As type]
    [statements]
    [name = expression]
    [Exit Function]
    [statements]
    [name = expression]
End Function

The following list describes the elements in a Function procedure declaration:

• Public: Indicates that the function is accessible to all other procedures in all other modules in the workbook (optional).
• Private: Indicates that the function is accessible only to other procedures in the same module (optional). If you use the Private keyword, your functions don’t appear in the Insert Function dialog box and are not shown in the Formula AutoComplete drop-down list.
• Static: Indicates that the values of variables declared in the function are preserved between calls (optional).
• Function: Indicates the beginning of a Function procedure (required).
• Name: Can be any valid variable name. When the function finishes, the result of the function is the value assigned to the function’s name (required).
• Arglist: A list of one or more variables that represent arguments passed to the function. The arguments are enclosed in parentheses. Use a comma to separate arguments. (Arguments are optional.)
• Type: The data type returned by the function (optional).
• Statements: Valid VBA statements (optional).
• Exit Function: A statement that causes an immediate exit from the function (optional).
• End Function: A keyword that indicates the end of the function (required).

Choosing a name for your function

Each function must have a unique name, and function names must adhere to a few rules:

• You can use alphabetic characters, numbers, and some punctuation characters. However, the first character must be alphabetic.
• You can use any combination of uppercase and lowercase letters. VBA does not distinguish between cases. To make a function name more readable, you can use InterestRate rather than interestrate.
• You can’t use a name that looks like a worksheet cell’s address (such as J21 or SUM100). Actually, Excel allows you to use such a name for a function, but a worksheet formula calling the function returns a #REF! error.
• You can’t use spaces or periods. Many of Excel’s built-in functions include a period character, but that character is not allowed in VBA function names. To make function names more readable, you can use the underscore character (Interest_Rate).
• You can’t embed the following characters in a function’s name: #, $, %, &, or !. These are type declaration characters that have a special meaning in VBA.
• You can use a function name with as many as 255 characters. However, shorter names are usually more readable and easier to work with.

Using functions in formulas

Using a custom VBA function in a worksheet formula is like using a built-in worksheet function. However, you must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook as the formula, you don’t have to do anything special. If it’s in a different workbook, you may have to tell Excel where to find it. You can do so in three ways:

• Precede the function’s name with a file reference. For example, if you want to use a function called CountNames that’s defined in a workbook named Myfuncs.xlsm, you can use a formula like the following:

  =MyFuncs.xlsm!CountNames(A1:A1000)

  If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically.

• Set up a reference to the workbook. You do this with the VB Editor’s Tools ➜ References command (see Figure 25.2). If the function is defined in a referenced workbook, you don’t need to use the worksheet name. Even when the dependent workbook is assigned as a reference, the Insert Function dialog box continues to insert the workbook reference (even though it’s not necessary). Note that the referenced workbook must be open in order to use the functions defined in it.
• Function names in a referenced workbook do not appear in the Formula AutoComplete drop-down list. Formula AutoComplete works only when the formula is entered into the workbook that contains the custom function or when it is contained in an installed add-in.
• Create an add-in. When you create an add-in from a workbook that has Function procedures, you don’t need to use the file reference when you use one of the functions in a formula; however, the add-in must be installed. We discuss add-ins later in this chapter (see the “Creating Add-Ins for Functions“ section).

Using function arguments

Custom functions, like Excel’s built-in functions, vary in their use of arguments. Keep the following points in mind regarding VBA Function procedure arguments:

• A function can have no argument.
• A function can have a fixed number of required arguments (from 1 to 60).
• A function can have a combination of required and optional arguments.
• A function can have a special optional argument called a ParamArray, which allows a function to have an indefinite number of arguments.

All cells and ranges that a function uses should be passed as arguments. In other words, a Function procedure should never contain direct references to cells or ranges.

Adding a function description

When you select one of Excel’s built-in functions in the Insert Function dialog box, a brief description of the function appears (see Figure 25.3). You may want to provide such a description for the custom functions that you create.

Here’s a simple custom function that returns its argument, but with no spaces:

Function REMOVESPACES(txt)
    REMOVESPACES = Replace(txt, " ", "")
End Function

The following steps describe how to provide a description for a custom function:

1. Create your function in the VB Editor.

2. Activate Excel and choose Developer ➜ Code ➜ Macros (or press Alt+F8).

   The Macro dialog box lists available Sub procedures but not functions.

3. Type the name of your function in the Macro Name box.

   Make sure that you spell it correctly.

4. Click the Options button to display the Macro Options dialog box.

   If the Options button is not enabled, you probably spelled the function’s name incorrectly.

5. Type the function description in the Description field (see Figure 25.4).

   The Shortcut key field is irrelevant for functions.

6. Click OK and then click Cancel.

Specifying a function category

Oddly, Excel does not provide a direct way to assign a custom function to a particular function category. If you want your custom function to appear in a function category other than User Defined, you need to execute some VBA code. Assigning a function to a category also causes it to appear in the drop-down controls in the Formulas ➜ Function Library group.

For example, assume that you created a custom function named REMOVESPACES, and you’d like this function to appear in the Text category (that is, Category 7) in the Insert Function dialog box. To accomplish this, you need to execute the following VBA statement:

Application.MacroOptions Macro:="REMOVESPACES", Category:=7

One way to execute this statement is to use the Immediate window in the VB Editor. If the Immediate window is not visible, choose View ➜ Immediate Window (or press Ctrl+G). Figure 25.5 shows an example. Just type the statement and press Enter. Then save the workbook, and the category assignment is also stored in the workbook. Therefore, this statement needs to be executed only one time. In other words, it is not necessary to assign the function to a new category every time the workbook is opened.

Alternatively, you can create a Sub procedure and then execute the procedure.

Sub AssignToFunctionCategory()
    Application.MacroOptions Macro:="REMOVESPACES", Category:=7
End Sub

After you execute the procedure, you can delete it. A function can be assigned to only one category. The last category assignment replaces the previous category assignment (if any).

You will, of course, substitute the actual name of your function, and you can specify a different function category. The AssignToFunctionCategory procedure can contain any number of statements—one for each of your functions.

Table 25.1 lists the function category numbers that you can use. Notice that a few of these categories (10–13) normally don’t display in the Insert Function dialog box. If you assign your function to one of these categories, the category appears.

You can also create custom function categories. The statement that follows creates a new function category named My VBA Functions and assigns the REMOVESPACES function to this category:

Application.MacroOptions Macro:="REMOVESPACES", Category:="My VBA Functions"

Adding argument descriptions

When you use the Insert Function dialog box to enter a function, the Function Arguments dialog box displays after you click OK. For built-in functions, the Function Arguments dialog box displays a description for each of the function’s arguments.

In Chapter 26, we present a function named EXTRACTELEMENT:

Function EXTRACTELEMENT(Txt, n, Separator) As String
'   Returns the nth element of a text string, where the
'   elements are separated by a specified separator character
    Dim AllElements As Variant
    AllElements = Split(Txt, Separator)
    EXTRACTELEMENT = AllElements(n - 1)
End Function

This function returns an element from a delimited text string and uses three arguments. For example, the following formula returns the string fghi (the third element in the string, which uses a dash to separate the elements):

=EXTRACTELEMENT("ab-cde-fghi-jkl", 3, "-")

Following is a VBA Sub procedure that adds argument descriptions, which appear in the Function Arguments dialog box:

Sub DescribeFunction()
  Dim desc(1 To 3) As String
  desc(1) = "The delimited text string"
  desc(2) = "The number of the element to extract"
  desc(3) = "The delimiter character"
  Application.MacroOptions Macro:="EXTRACTELEMENT", ArgumentDescriptions:=desc
End Sub

The argument descriptions are stored in an array, and that array is used as the ArgumentDescriptions argument for the MacroOptions method. You need to run this procedure only one time. After doing so, the argument descriptions are stored in the workbook.

Using the VBA MsgBox statement

The MsgBox statement, when used in your VBA code, displays a pop-up dialog box. You can use MsgBox statements at strategic locations within your code to monitor the value of specific variables. The following example is a Function procedure that should reverse a text string passed as its argument. For example, passing Hello as the argument should return olleH. If you try to use this function in a formula, however, you see that it does not work—it contains a logical error:

Function REVERSETEXT(text) As String
'   Returns its argument, reversed
    Dim TextLen As Long, i As Long
    TextLen = Len(text)
    For i = TextLen To 1 Step -1
        REVERSETEXT = Mid(text, i, 1) & REVERSETEXT
    Next i
End Function

You can insert a temporary MsgBox statement to help you figure out the source of the problem. Here’s the function again, with the MsgBox statement inserted within the loop:

Function REVERSETEXT(text) As String
'   Returns its argument, reversed
    Dim TextLen As Long, i As Long
    TextLen = Len(text)
    For i = TextLen To 1 Step -1
        REVERSETEXT = Mid(text, i, 1) & REVERSETEXT
        MsgBox REVERSETEXT
    Next i
End Function

When this function is evaluated, a pop-up message box appears, once for each time through the loop. The message box shows the current value of REVERSETEXT. In other words, this technique enables you to monitor the results as the function is executed. Figure 25.6 shows an example.

The information displayed in the series of message boxes shows that the text string is being built within the loop, but the new text is being added to the beginning of the string, not the end. The corrected assignment statement is

REVERSETEXT = REVERSETEXT & Mid(text, i, 1)

When the function is working properly, make sure that you remove all the MsgBox statements.

To display more than one variable in a message box, you need to concatenate the variables and insert a space character between each variable. The following statement, for example, displays the value of three variables (x, y, and z) in a message box:

MsgBox x & " " & y & " " & z

If you omit the blank space, you can’t distinguish the separate values.

Alternatively, you can separate the variable with vbNewLine, which is a constant that inserts a line break. When you execute the following statement, x, y, and z each appear on a separate line in the message box:

MsgBox x & vbNewLine & y & vbNewLine & z

Using Debug.Print statements in your code

If you find that using MsgBox statements is too intrusive, another option is to insert some temporary code that writes values directly to the VB Editor Immediate window. (See the sidebar “Using the Immediate window“ later in this chapter.) You use the Debug.Print statement to write the values of selected variables.

For example, if you want to monitor a value inside a loop, use a routine like the following:

Function VOWELCOUNT(r)
    Dim Count As Long, Ch As String
    Dim i As Long
    Count = 0
    For i = 1 To Len(r)
        Ch = UCase(Mid(r, i, 1))
        If Ch Like "[AEIOU]" Then
           Count = Count + 1
            Debug.Print Ch, i
        End If
    Next i
    VOWELCOUNT = Count
End Function

In this case, the value of two variables (Ch and i) prints to the Immediate window whenever the Debug.Print statement is encountered. Figure 25.7 shows the result when the function has an argument of North Carolina.

When your function is debugged, make sure that you remove the Debug.Print statements.

Calling the function from a Sub procedure

Another way to test a Function procedure is to call the function from a Sub procedure. To do this, simply add a temporary Sub procedure to the module and insert a statement that calls your function. This is particularly useful because runtime errors display as they occur.

The following Function procedure contains a runtime error. As we noted previously, the runtime errors don’t display when you are testing a function by using a worksheet formula. Rather, the function simply returns an error (#VALUE!):

Function REVERSETEXT(text) As String
'   Returns its argument, reversed
    Dim TextLen As Long, i As Long
    TextLen = Len(text)
    For i = TextLen To 1 Step -1
        REVERSETEXT = REVERSETEXT And Mid(text, i, 1)
    Next i
End Function

To help identify the source of the runtime error, insert the following Sub procedure:

Sub Test()
    x = REVERSETEXT("Hello")
    MsgBox x
End Sub

This Sub procedure simply calls the REVERSETEXT function and assigns the result to a variable named x. The MsgBox statement displays the result.

You can execute the Sub procedure directly from the VB Editor. Simply move the cursor anywhere within the procedure and choose Run ➜ Run Sub/UserForm (or just press F5). When you execute the Test procedure, you see the error message that is shown in Figure 25.8.

Click the Debug button, and the VB Editor highlights the statement causing the problem (see Figure 25.9). The error message does not tell you how to correct the error, but it does narrow your choices. After you identify the statement that’s causing the error, you can examine it more closely, or you can use the Immediate window. See the sidebar “Using the Immediate window“ to help locate the exact problem.

In this case, the problem is the use of the And operator instead of the concatenation operator (&). The correct statement is as follows:

REVERSETEXT = REVERSETEXT & Mid(text, i, 1)

Setting a breakpoint in the function

Another debugging technique is to set a breakpoint in your code. Execution pauses when VBA encounters a breakpoint. You can then use the Immediate window to check the values of your variables, or you can use F8 to step through your code line by line.

To set a breakpoint, move the cursor to the statement at which you want to pause execution and choose Debug ➜ Toggle Breakpoint. Alternatively, you can press F9 or click the vertical bar to the left of the code window. Any of these actions highlights the statement to remind you that a breakpoint is in effect. (You also see a dot in the code window margin.) You can set any number of breakpoints in your code. To remove a breakpoint, move the cursor to the statement and press F9. Figure 25.10 shows a Function procedure that contains a breakpoint.