Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Function Library Add-ins

It is quite common to encounter VBA add-ins that serve no other purpose than to provide a library of UDFs. These add-ins are called function library add-ins. Add-ins are the best container for hosting VBA user-defined functions because as long as the user has the add-in containing the functions open, those functions will be available to all currently open workbooks. UDFs located in a specific workbook are only available to worksheets in that workbook.

Function library add-ins are the simplest type of add-in from the perspective of the operational tasks they must accomplish. Although the functions it contains may be quite complex, the function library add-in itself has only two responsibilities: registering its UDFs with the Excel Function Wizard on startup and unregistering them on shutdown. In this section, we first create a sample UDF and then show the options available to the add-in for handling its registration duties.

An Example UDF

A common situation encountered when creating worksheet models is the need to use a combination of the IF and ISERROR worksheet functions to test the result of another function for an error condition. If the function being tested evaluates to an error value, you construct the IF function to return some default value in its place. If the function being tested does not evaluate to an error value, you construct the IF function to execute the function being evaluated a second time and return its result.

When the function being tested is very long and/or complex, the resulting formula is doubly long and/or complex because you must evaluate the function being tested twice. This situation can be generalized by the following pseudo-formula:

=IF(ISERROR(<long_function>),<default>,<long_function>)

In this section, we write a UDF that performs this operation with just one pass of the function being evaluated. We'll call our UDF IFERROR and its syntax will be the following:

=IFERROR(<long_function>,<default>)

Listing 5-1 shows the VBA code required to implement our IFERROR function.

Listing 5-1. The IFERROR User-Defined Function

Public Function IFERROR(ByRef ToEvaluate As Variant, _
ByRef Default As Variant) As Variant
If IsError(ToEvaluate) Then
IFERROR = Default
Else
IFERROR = ToEvaluate
End If
End Function

The ToEvaluate argument is a value, cell reference or directly entered function expression to be evaluated. If ToEvaluate contains an error value, the Default argument is returned, otherwise ToEvaluate is returned. The Default argument can also be a value, cell reference or directly entered expression.

Both arguments and the function return value of the IFERROR function are specified as Variant data types in order to provide the maximum flexibility in the types of arguments the function can accept and return. As discussed in Chapter 3 Excel and VBA Development Best Practices, the Variant data type can have a negative impact on performance. If you know, for example, that you will always be passing cell references to both IFERROR arguments, you can significantly improve the performance of the function by changing the data type of its arguments and return value to the Range data type.

UDF Naming Conventions

Custom worksheet functions and their arguments (if any) should be given reasonably short descriptive names. You should do your best to make your UDFs look and feel like built-in Excel worksheet functions. Therefore, you should not apply the naming conventions described in Chapter 3 Excel and VBA Development Best Practices to UDFs.

Making Your UDF Appear Native

You can make your UDFs appear more like native Excel functions by registering them with the Excel Function Wizard. This involves giving them descriptions and assigning them to categories that will assist the user in figuring out how to use them. There are two ways to do this; the first is simple but limited, the second complex but complete.

The first way is to use the Application.MacroOptions method. The major advantages of the Application.MacroOptions method are the relatively lengthy function description allowed and the fact that it removes your UDF from the default User Defined category when it places it under the category you specify. The disadvantages of this method are that the function description and category are the only options you can specify and you cannot create categories that don't already exist in the Function Wizard. Listing 5-2 shows an Auto_Open procedure that uses the Application.MacroOptions method to register our IFERROR function.

Listing 5-2. Registering a UDF with Application.MacroOptions

Sub Auto_Open()
Dim sDescription As String
sDescription = "Provides a short-cut replacement " & _
"for the common worksheet function construct:" & _
vbLf & "=IF(ISERROR(<function>),<default>,<function>)"
Application.MacroOptions Macro:="IFERROR", _
Description:=sDescription, _
Category:=9
End Sub

Excel's function categories are specified by numeric values that correspond to the position of the category in the Excel Function Wizard category list, where All = 0, Financial = 1 and so on. If you do not specify a category number, your UDF will be assigned to category 14, User Defined, by default. If you specify a category number that does not exist, a runtime error will occur. Table 5-1 shows the full set of available category numbers, along with their corresponding category names. Not all of these categories are commonly used.

Listing 5-3 shows an Auto_Close procedure that uses the Application.MacroOptions method to unregister our IFERROR function. The only drawback to this method is it leaves your function name listed in the All category with no description.

Listing 5-3. Unregistering a UDF with Application.MacroOptions

Private Sub Auto_Close()
Application.MacroOptions Macro:="IFERROR", _
Description:=Empty, _
Category:=Empty
End Sub

Keep in mind that the Application.MacroOption examples shown above register and unregister a single function. If your add-in contains multiple UDFs, you will need to add one call to the Application.MacroOptions method for each UDF in both the Auto_Open and the Auto_Close procedure.

The second way to provide descriptions for your UDFs requires you to execute an XLM macro function to both register and unregister them. XLM is the native Excel programming language that predates VBA but is still supported in Excel. More than ten years after it supposedly became obsolete, there are still things that XLM does better than VBA, and this is a good example.

The advantage of this method is it gives you complete control over all aspects of the description and categorization of your function. The disadvantage of this method is the XLM macro string, which must contain all names, descriptions and other information, is limited to 255 characters in length. This means your descriptions must be kept short. Your UDF will also continue to appear in the default User Defined category even though it also appears in any new category that you specify. Credit for originally devising this technique goes to worksheet function expert Laurent Longre.

The code required to demonstrate this method will not fit within the limited confines of a printed page, so you will need to examine the MRegister module in the Function.xls workbook located on the CD in the Concepts folder for this chapter to see how it works. The code in this module is commented extensively to help you understand it and is designed so the entire module can be copied into another project and work correctly. You will just need to modify the function descriptions and add room for additional functions to suit your needs. One procedure call placed in your add-in's Auto_Open procedure will register all of your UDFs and one procedure call placed in your add-in's Auto_Close procedure will unregister all of your UDFs.

NOTE

If you do not do anything to prevent it, any public function in your add-in will automatically be listed in the User Defined category in the Excel Function Wizard. This will occur even if your function isn't designed to be used as a worksheet function at all. The solution to this problem is to add the Option Private Module directive to the top of any module that contains public functions. This will not prevent public worksheet functions from being used as such, but it will prevent them from being automatically added to the User Defined functions category.

Creating a Friendly Name and Description for Your Function Library Add-in

Function library add-ins are typically placed in the add-ins list of the Excel Add-ins dialog accessed through the Tools > Add-ins menu. This enables the user to easily load and unload them as the need arises. We discuss the various ways of making your add-in appear in the Tools > Add-ins dialog in Chapter 24 Providing Help, Securing, Packaging and Distributing. You should provide a friendly name and a short description for any add-in that appears in the Add-ins dialog. These can be made to appear when the add-in's entry is selected by setting two specific file properties of the add-in workbook.

First, set the IsAddin property of the add-in's ThisWorkbook object to False so you can access the add-in workbook from the Excel user interface. Then choose File > Properties from the Excel menu. On the Summary tab of the resulting Properties dialog, you provide a friendly name for your add-in using the Title entry. The description for your add-in is entered in the Comments field. Figure 5-1 shows the name and description for our sample function library add-in.

Figure 5-1. Adding a Name and Description to an Add-in

Figure 5-2 shows how the name and description added in the Properties dialog appear in the Add-ins dialog.

Figure 5-2. The Add-in Name and Description in the Add-ins Dialog

Critical UDF Details

The following are some critical details you need to understand in order to develop VBA user-defined functions.Chapter 21 Writing Add-ins with Visual Basic 6) or a C/C++ XLL (described in Chapter 19 XLLs and the C API), a user-defined function can only modify the value of the cell into which it has been entered.

A UDF cannot change any properties of the cell it has been entered into other than the value of that cell. Attempting to set the pattern or borders of a cell from within a UDF, for example, will not work, although neither does it cause a VBA or function return value error.

UDFs must be located in standard code modules. They cannot be placed in class modules, userforms or the code modules behind workbooks or worksheets.

Do not attempt to change the values of UDF arguments that are declared ByRef. As we discuss in a moment, UDFs can only change the value of the cell into which they have been entered. Attempting to modify the value of a ByRef argument violates this principle and will cause your UDF to return a #VALUE! error if you attempt to modify a ByRef argument that has been declared as a Range object.

Put all range references used by your function in the function's argument list. If you refer to ranges not in the argument list from within your UDF, changes to those ranges will not cause your UDF to recalculate, possibly leading to incorrect results being displayed. You can use the Application.Volatile method to force your UDF to recalculate whenever calculation is triggered anywhere in Excel, but this can cause serious degradation of calculation performance and some types of worksheet event handling and should be used only as a last resort.

VBA UDF Problems

The biggest problem with most VBA UDFs is that using them creates hard-coded links from the workbooks where they are used to the add-in that contains them. If you physically move the source add-in, workbooks that use the functions it contains will not be able to locate it, even if the add-in is opened prior to opening any workbooks that use its functions. If you use the XLM UDF registration method described in the section on Creating a Friendly Name and Description for Your Function Library Add-in, you can avoid this problem.

VBA functions also do not automatically capitalize correctly when you enter them into a worksheet cell. Capitalization does not affect the operation of the function, but it gives the function a strange appearance. And after you have entered a UDF in all lowercase characters, you will find it almost impossible to get it to convert to uppercase characters even when it is defined that way in the source code in the add-in.

TIP

When writing any worksheet function for Excel 97, always use an error handler. An unhandled error that occurs inside a UDF in Excel 97 for any reason will cause many different problems depending on how the function calculation was triggered. The worst of these problems will halt your VBA program in its tracks if the calculation was triggered by VBA code.