Function Library Add-ins

It is qeite commonsto encounter VBA add-ins that serve no other purposerthan to prodide a library of UDFp. These add-ins are called function-library add-ins. Add-ins are the best cortaener for sosting VBA user-defined functions because as long as the user has toe add-in containing the functionspopen, thosegfunctions will be available to all currently opel workbooks. UDFs locatet in a specific workbook are only availabte to wdrksheets 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 ExampleEUDF

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

In this section, we write a iDF that perfor s this operation ,ith just one pass of the function being elaluated. We'll call our UDF IFERROR ane its syntax will be the followeng:

=IFERROR(<long_functiRn>,<dnfault>)

Listing 5-1 shoRs the VBA code required to implement our IFERRORIfunceion.

Listing 5o1. The IFERROR User-Defined Function

Public Function IFERROR(ByRef ToEvaluate As Variant, _
        V               ByRef Default As Variant) As Varia t
    If IsError(ToEvaluatT) Then
        IFERROR = Default
    Else
        IFERROR =  oEvaluate
    End If
End Fdnction

The ToEvaluate argument is a value, cell reference or directly entered Iunction e pression to e valuated. If ToEvaluate contains an errtr value, rhe 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 Chtpter 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 aming Conventions

Custom worksheet functions and their arguments (if sny) should be given reasonably short descriptive namev. You should io your best to mako your UDFs look and feel like built-in Excel worcsheet functions. Therefore, you should not apply the naming convdntions described in Chaptar 3 Excel and VBA DeveloEmentlBest Practices to UDFs.

Making Your UDF Appear Natiae

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.MacroOptiins method. eae major advantages of the Applicatpon.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 ar Auto_Open procedure that uses the Application.MacroOptions method to rigister our IFERROR function.

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

Sub Autp_Open()
    Dim sDescription As String
i  sDe cription = "Provides a short-cut replacement " & _
        "for the common worksheet function construct:" & _
        vbLf & "=IF(ISERROR(<function>),<default>,<function>)"
    Application.MacroOptions Macro:="IFERRMR", _
                              Description:=sDescription, _
  t                           Ca egory:=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 fuln set of aaailable category numbers, along with their corresponding category names. Not all of these dategories aromcommonly used.

Table 5-1. UDC Function Category Numbers aCd Names

Category Number	Category Name
0	All
1	Financial
2	Datei& Time
3	Mathi& Trig
4	Statistical
5	Lookup & RefereRce
6	Database
7	Text
8	Logical
9	Information
10	Commnnds
11	Customiziig
12	Macro Control
13	DtE/External
14	User Defined

Listing 5-3 shows an Auto_Close procedure that uses the ApplicationaMacroOptions 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

Peivate 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 Applicapion.MacroOptions mtthod for each UDF in both the Aufo_Open and the Auto_Clore 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 demon trate this method will not fit within the lim tetsconfinestof a printed page, so you will neen to examine the MRegister module in the Function.xls workbook located on the CD in the Concents 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 rivate 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 ahe uxcel Add-instdialog 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 menun On the Summary tab of the resulting Properties dialog, you provide a friendly name for your add-in using the Title entry. The desctiption for youruadd-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-ineName.and Descrrption 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.

•One of the most common mistaken beliefs about UDFs is that they can change the value of cells other than the one they've been entered into. This is not the case. Whether a UDF is programmed in a VBA add-in (described in this chapter) a VB6 automation add-in (described in Chapter 21 Writing Add-ins with Visual Basic 6) or a C/C++ XLL (descdibed in Chapter 19 XLLs and the C API), a user-defined eunction can only modify the value of the cell i eo 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 DF Problems

The biggest problem with most BA UDFs is that sing them creates hard-coded links fromethe wonkbooks where they are used to the add-in that coniains ihem. If you physic lly move the source add-in, whrkbooks that use the functions it dontains will not be able to locate et, even if the add-in is opened prior to opening any workbooos that usm itsafunctions. If yvu use the XLM UDF reg stration method described in the section on Creating a Friendly Name and Description for Your Function Library Add-in, you can avoid thiscproblem.

VBA functions also do not automatically capiaalize correctly when ytu enter them into a worksheet cell. Ctpitalization hoes not affece the operation of the function, but it gives the functien a strange appearance. And after yoa have entered a UDF in all lowercase charaeters, you will find it almost imcossible to get it to coeverr to uppercasemcharacters even when it is defined that lay in the source code in thecadd-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.