Excel 2016 Formulas (2016)

PART VI
Developing Custom Worksheet Functions

Chapter 25
Function Procedure Basics

In This Chapter

· Why you may want to create custom functions

· An introductory VBA function example

· About VBA Function procedures

· Using the Insert Function dialog box to add a function description and assign a function category

· Tips for testing and debugging functions

· Creating an add-in to hold your custom functions

Previous chapters in this book examined Excel’s built-in worksheet functions and how you can use them to build more complex formulas. These functions provide a great deal of flexibility when creating formulas. However, you may encounter situations that call for custom functions. This chapter discusses why you may want to use custom functions, how you can create VBA Function procedures, and methods for testing and debugging them.

Why Create Custom Functions?

You are, of course, familiar with Excel’s worksheet functions. Even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. Excel 2016 includes more than 450 predefined worksheet functions—everything from ABS to ZTEST.

You can use VBA to create additional worksheet functions, which are known as custom functions or user-defined functions (UDFs). With all the functions that are available in Excel and VBA, you may wonder why you would ever need to create new functions. The answer: to simplify your work and give your formulas more power.

For example, you can create a custom function that can significantly shorten your formulas. Shorter formulas are more readable and easier to work with. However, it’s important to understand that custom functions in your formulas are usually much slower than built-in functions. On a fast system, though, the speed difference often goes unnoticed.

The process of creating a custom function is not difficult. In fact, many people (these authors included) enjoy creating custom functions. This book provides you with the information that you need to create your own functions. In this and the next chapter, you’ll find many custom function examples that you can adapt for your own use.

An Introductory VBA Function Example

Without further ado, we’ll show you a simple VBA Function procedure. This function, named USER, does not accept arguments. When used in a worksheet formula, this function simply displays the user’s name in uppercase characters. To create this function, follow these steps:

1. Start with a new workbook.

This is not really necessary, but keep it simple for right now.

2. Press Alt+F11 to activate the VB Editor.

3. Click your workbook’s name in the Project window.

If the Project window is not visible, press Ctrl+R to display it.

4. Choose Insert ➜ Module to add a VBA module to the project.

5. Type the following code into the code window:

6. Function USER()

7. ' Returns the user's name

8. USER = Application.UserName

9. USER = UCase(USER)

End Function

Figure 25.1 shows how the function looks in a code window.

Figure 25.1 A simple VBA function displayed in a code window.

To try out the USER function, activate Excel (press Alt+F11) and enter the following formula into any cell in the workbook:

=USER()

If you entered the VBA code correctly, the Function procedure executes, and your name displays (in uppercase characters) in the cell.

Note

If your formula returns an error, make sure that the VBA code for the USER function is in a VBA module (and not in a module for a Sheet or ThisWorkbook object). Also, make sure that the module is in the project associated with the workbook that contains the formula.

When Excel calculates your worksheet, it encounters the USER custom function and then goes to work following the instructions. Each instruction in the function is evaluated, and the result is returned to your worksheet. You can use this function any number of times in any number of cells.

This custom function works just like any other worksheet function. You can insert it into a formula by using the Insert Function dialog box. It also appears in the Formula AutoComplete drop-down list as you type it in a cell. In the Insert Function dialog box, custom functions appear (by default) in the User Defined category. As with any other function, you can use it in a more complex formula. For example, try this:

="Hello "&USER()

Or use this formula to display the number of characters in your name:

=LEN(USER())

If you don’t like the fact that your name is in uppercase, edit the procedure as follows:

Function USER()

' Returns the user's name

USER = Application.UserName

End Function

After editing the function, reactivate Excel and press F9 to recalculate. Any cell that uses the USER function displays a different result.

What custom worksheet functions can’t do

As you develop custom worksheet functions, you should understand a key point. A Function procedure used in a worksheet formula must be passive; in other words, it can’t change things in the worksheet.

You may be tempted to try to write a custom worksheet function that changes the formatting of a cell. For example, you may want to edit the USER function (presented in this section) so that the name displays in a different color. Try as you might, a function such as this is impossible to write—everybody tries this, and no one succeeds. No matter what you do, the function always returns an error because the code attempts to change something on the worksheet. Remember that a function can return only a value. It can’t perform actions with objects.

None of Excel’s built-in functions are able to change a worksheet, so it makes sense that custom VBA functions cannot change a worksheet.

About Function Procedures

In this section, we discuss some of the technical details that apply to Function procedures. These are general guidelines for declaring functions, naming functions, using custom functions in formulas, and using arguments in custom functions.

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.

UPPERCASE function names?

You’ve probably noticed that Excel’s built-in worksheet function names always use uppercase characters. Even if you enter a function using lowercase characters, Excel converts it to uppercase.

When you create custom worksheet functions, you can use uppercase, lowercase, or mixed case. It doesn’t matter. When we create functions that are intended to be used in worksheet formulas, we like to make them uppercase to match Excel’s style.

Sometimes, however, when we enter a formula that uses a custom function, Excel does not match the case that we used in the VBA code. For instance, assume you have a function named MYFUNC, and its function declaration uses uppercase for the name. When you type the function into a formula, though, Excel does not display it in uppercase. Here’s how to fix it.

In Excel, choose Formulas ➜ Defined Names ➜ Define Name and create a name called MYFUNC (in uppercase letters). It doesn’t matter what the name refers to.

Creating that name causes all formulas that use the MYFUNC function to display an error. That’s to be expected. But you’ll notice that the formula now displays MYFUNC in uppercase characters.

The final step: choose Formulas ➜ Defined Names ➜ Name Manager and delete the MYFUNC name. The formulas no longer display an error—and they retain the uppercase letters for the function name.

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

Figure 25.2 Use the References dialog box to create a reference to a project that contains a custom VBA function.

Note

By default, all projects are named VBAProject—and that’s the name that appears in the Available References list in the References dialog box. To make sure that you select the correct project in the References dialog box, keep your eye on the bottom of the dialog box, which shows the pathname and filename for the selected item. Better yet, change the name of the project to be more descriptive. To change the name, select the project, press F4 to display the Properties window, and then change the Name property to something other than VBAProject. Use a unique name because Excel does not let you create two references with the same name.

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.

Cross-Ref

See Chapter 26, “VBA Custom Function Examples,“ for examples of functions that use various types 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.

Using the Insert Function Dialog Box

Excel’s Insert Function dialog box is a handy tool that enables you to choose a particular worksheet function from a list of available functions. The Insert Function dialog box also displays a list of your custom worksheet functions and prompts you for the function’s arguments.

Note

Custom Function procedures defined with the Private keyword don’t appear in the Insert Function dialog box. Declaring a function as Private is useful if you create functions that are intended to be used by other VBA procedures in the same module rather than in a formula.

By default, custom functions are listed under the User Defined category, but you can have them appear under a different category. You also can add some text that describes the function.

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.

Figure 25.3 Excel’s Insert Function dialog box displays a brief description of the selected function.

Note

If you don’t provide a description for your custom function, the Insert Function dialog box displays the following text: No help available.

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.

Figure 25.4 Provide a function description in the Macro Options dialog box.

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.

Figure 25.5 Executing a VBA statement that assigns a function to a particular function category.

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.

Table 25.1 Function Categories

*The Compatibility category was introduced in Excel 2010.

**The Web category was introduced in Excel 2013.

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.

Testing and Debugging Your Functions

Naturally, testing and debugging your custom function is an important step that you must take to ensure that it carries out the calculation you intend. This section describes some debugging techniques you may find helpful.

Note

If you’re new to programming, the information in this section will make a lot more sense after you’re familiar with the material in Chapter 24, “VBA Programming Concepts.“

VBA code that you write can contain three general types of errors:

§ Syntax errors: An error in writing the statement—for example, a misspelled keyword, a missing operator, or mismatched parentheses. The VB Editor lets you know about syntax errors by displaying a pop-up error box. You can’t use the function until you correct all syntax errors.

§ Runtime errors: Errors that occur as the function executes. For example, attempting to perform a mathematical operation on a string variable generates a runtime error. Unless you spot it beforehand, you won’t be aware of a runtime error until it occurs.

§ Logical errors: Code that runs but simply returns the wrong result.

Tip

To force the code in a VBA module to be checked for syntax errors, choose Debug ➜ Compile xxx (where xxx is the name of your project). Executing this command highlights the first syntax error, if any exists. Correct the error and issue the command again until you find all the errors.

An error in code is sometimes called a bug. Locating and correcting such an error is called debugging.

When you test a Function procedure by using a formula in a worksheet, you may have a hard time locating runtime errors because (unlike syntax errors) they don’t appear in a pop-up error box. If a runtime error occurs, the formula that uses the function simply returns an error value (#VALUE!). This section describes several approaches to debugging custom functions.

Tip

While you’re testing and debugging a custom function, it’s a good idea to use the function in only one formula in the worksheet. If you use the function in more than one formula, the code is executed for each formula, which quickly becomes annoying!

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.

Figure 25.6 Use a MsgBox statement to monitor the value of a variable as a Function procedure executes.

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.

Tip

If you get tired of seeing the message boxes, you can halt the code by pressing Ctrl+Break. Then respond to the dialog box that’s presented. Clicking the End button stops the code. Clicking the Debug button enters Debug mode, in which you can step through the code line by line.

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.

Figure 25.7 Using the VB Editor Immediate window to display results while a function is running.

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.

Figure 25.8 A runtime error identified by VBA.

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.

Figure 25.9 The highlighted statement generated a runtime error.

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)

Note

When you click the Debug button, the procedure is still running—it’s just halted and is in break mode. After you make the correction, press F5 to continue execution, press F8 to continue execution on a line-by-line basis, or click the Reset button (on the Standard toolbar) to halt execution.

Using the Immediate window

The VB Editor Immediate window can be helpful when debugging code. To activate the Immediate window, choose View ➜ Immediate Window (or press Ctrl+G).

You can type VBA statements in the Immediate window and see the result immediately. For example, type the following code in the Immediate window and press Enter:

Print Sqr(1156)

The VB Editor prints the result of this square root operation (34). To save a few keystrokes, you can use a single question mark (?) in place of the Print keyword.

The Immediate window is particularly useful for debugging runtime errors when VBA is in break mode. For example, you can use the Immediate window to check the current value for variables or to check the data type of a variable.

Errors often occur because data is of the wrong type. The following statement, for example, displays the data type of a variable named Counter (which you probably think is an Integer or a Long variable):

? TypeName(Counter)

If you discover that Counter is of a data type other than Integer or Long, you may have solved your problem.

You can execute multiple statements in the Immediate window if you separate them with a colon. This line contains three statements:

x=12: y=13 : ? x+y

Most, but not all, statements can be executed in this way.

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.

Figure 25.10 The highlighted statement contains a breakpoint.

Tip

To remove all breakpoints in all open projects, choose Debug ➜ Clear All Breakpoints or press Ctrl+Shift+F9.

Creating Add-Ins for Functions

If you create some custom functions that you use frequently, you may want to store these functions in an add-in file. A primary advantage to this is that you can use the functions in formulas in any workbook without a filename qualifier.

Assume that you have a custom function named ZAPSPACES and that it’s stored in Myfuncs.xlsm. To use this function in a formula in a workbook other than Myfuncs.xlsm, you need to enter the following formula:

=Myfuncs.xlsm!ZAPSPACES(A1:C12)

If you create an add-in from Myfuncs.xlsm and the add-in is loaded, you can omit the file reference and enter a formula like the following:

=ZAPSPACES(A1:C12)

A few words about passwords

Microsoft has never promoted Excel as a product that creates applications with secure source code. The password feature provided in Excel is sufficient to prevent casual users from accessing parts of your application that you want to keep hidden. However, the truth is that several password-cracking utilities are available. The security features in more recent versions of Excel are much better than those in earlier versions, but it’s possible that these also can be cracked. If you must be absolutely sure that no one ever sees your code or formulas, Excel is not your best choice as a development platform.

Creating an add-in from a workbook is simple. The following steps describe how to create an add-in from a normal workbook file:

1. Develop your functions and make sure they work properly.

2. Activate the VB Editor and select the workbook in the Project window. Choose Tools ➜ xxx Properties and click the Protection tab (where xxx corresponds to the name of your project). Select the Lock Project for Viewing check box and enter a password (twice). Click OK.

You need to do this last step only if you want to prevent others from viewing or modifying your macros or custom dialog boxes.

3. Reactivate Excel. Choose File ➜ Info ➜ Properties ➜ Show Document Panel, and Excel displays its Document Properties panel above the Formula bar. Enter a brief, descriptive title in the Title field and a longer description in the Comments field.

This last step is not required, but it makes the add-in easier to use by displaying descriptive text in the Add-Ins dialog box.

4. Choose File ➜ Save As.

5. In the Save As dialog box, select Excel Add-In (*.xlam) from the Save as Type drop-down list.

6. If you don’t want to store the add-in in the default directory, select a different directory.

7. Click Save.

A copy of the workbook is saved (with an .xlam extension), and the original macro-enabled workbook (.xlsm) remains open.

Warning

When you use functions that are stored in an add-in, Excel creates a link to that add-in file. Therefore, if you distribute your workbook to others, they must also have a copy of the linked add-in. Furthermore, the add-in must be stored in the same directory because the links are stored with complete path references. As a result, the recipient of your workbook may need to use the Data ➜ Connections ➜ Edit Links command to change the source of the linked add-in.

After you create your add-in, you can install it by using the standard procedure:

1. Choose File ➜ Options, and click the Add-Ins tab.

2. Select Excel Add-ins from the Manage drop-down list.

3. Click Go. This shows the Add-Ins dialog box.

4. Click the Browse button in the Add-Ins dialog box.

5. Locate your *.xlam file.

6. Place a check mark next to the add-in name.

Tip

A much quicker way to display the Add-Ins dialog box is to press Alt+T+I.