5.7 User-Defined Worksheet Functions

5.7 User -Defined Worksheet Functions

Fundamentals

Excel has available a large assortment of predefined worksheet functions, the most well known and important of which is SUM. For more complex applications the function IF plays an important role as well. IF allows for case testing within worksheet formulas. However, in complex situations IF formulas can be so opaque that their application is practically impossible (or extremely error-prone ). There are also situations in which the demands made on the formula's syntax are simply too great.

For such situations Excel offers the possibility of defining your own functions in VBA. These functions are then known as user-defined worksheet functions. An important advantage of such user-defined functions is that the function need be defined only once in a VBA module. It is then easy to make changes in such a function. (In contrast, worksheet formulas must be copied into all cells . When subsequent changes are made, all affected cells must also be changed.)

The programming of user-defined functions is often much easier than writing " genuine " Excel programs. In most cases they use no objects, methods , or properties. Indeed, often simple If “Then tests suffice.

Tip  

The calculation of a user-defined worksheet function is always incomparably slower than the use of a predefined Excel function. Before you begin to program a new worksheet function, look first in the on-line help to see whether there exists a predefined function that meets your needs.

Defining Your Own Functions

You may already have guessed that a user-defined worksheet function is defined by a garden-variety VBA function. (The function definition may not be declared as Private ; that would restrict the domain of validity to the current module!)

Let us look at an example: The function Discount (see Figure 5-9) calculates the final price, given the unit price and the number of pieces. If ten or more pieces are purchased, then a five percent discount is applied.

Figure 5-9: Short description of the Discount function

The function can be placed in a worksheet just like a predefined worksheet function. It is allowed to take as input actual numbers as well as cell references.

 =Discount(8, 12)      ' returns 91.2 =Discount(A1, B1) ' Function.xls Function  Discount  (unitprice As Double, pieces As Double) As Double   If pieces >= 10 Then     Discount = pieces * unitprice * 0.95   Else     Discount = pieces * unitprice   End If End Function 

Caution  

As in previous versions, Excel does not feel obliged to recalculate the affected cells automatically when there is a change in the VBA code of a worksheet function. The explicit request for recalculation with F9 usually functions properly, but is not always completely reliable. (This problem has plagued Excel since version 5.) In particularly stubborn cases it usually helps to add and then delete a new row or column above, respectively to the left of, the affected cells.

Short Description of Functions for Inserting a Function

In the form INSERTFUNCTION (in previous versions this was the function assistant) a short description of all functions is displayed. For user-defined functions you can provide such a description if you select this function in the object browser and select the command PROPERTIES with the right mouse button. The form MEMBER OPTIONS that appears gives an impression of incompleteness, but it fulfills its mission.

Function Categories

In Excel 5/7 user-defined worksheet functions could be put into various categories, such as "financial" and "date and time." Starting with Excel 97, the form MEMBER OPTIONS no longer offers this possibility, and all user-defined functions belong to the category "User Defined" in the PASTE FUNCTION form. However, if you wish to assign your functions to another category, you must give an instruction in the immediate window like the following:

 Application.MacroOptions Macro:="Discount", Category:=1 

The function will then be permanently associated to the group "Financial" (that is, the setting for this function will be stored together with the Excel file). The following list gives the numbers of the most important categories:

CATEGORY	CATEGORY NAME
1	Finance
2	Date & Time
3	Math & Trig
4	Statistical
5	Lookup & Reference
6	Database
7	Text
8	Logical
9	Information
14	User Defined

Using User-Defined Functions in Other Workbooks

The function Discount can be used only in worksheets of the workbook in whose module it is defined (Figure 5-10). If you wish to use Discount in other workbooks as well, then you must provide the name of the workbook in which the function is defined, that is, = Function.xls!Discount(8, 12) . Alternatively, you can create a reference to Function.xls in the current workbook with TOOLSREFERENCES. Then it can be used without the prefixed file name. (The command TOOLSREFERENCES can be used only when a module sheet is active.)

Figure 5-10: The user-defined function "Discount" has been associated with the function category "Financial."

Tip  

Please note that Excel distinguishes between the use of user-defined functions in worksheets and in VBA code. If you wish to use Discount in the VBA code of another workbook, then you always have to supply a reference. The prefixing of the file name is not possible in VBA code.

User-Defined Functions in Add-Ins

You can compile a workbook with the definitions of several user-defined functions into an add-in. These functions then are available in all workbooks once this add in has been activated. In contrast to the definition of functions in normal workbooks, neither a prefixed file name when the function is called nor the provision of a reference is required. Extensive information on creating your own add-ins can be found in Chapter 14.

Cell Ranges as Parameters

If a user-defined function is called with a cell reference (such as = Discount(A1, B1) ), then a Range object is passed to the function. In the case of a single cell, the further evaluation of the parameter proceeds without any problems: The Value property of the Range object is considered the default property, whence the contents of the cell can be accessed without further complication. Things get more difficult when a range of cells (such as A1:A3) is passed as parameter.

The situation presents particular difficulties because in Excel A1:A3, C1:C3 usually means a range of cells made up of the partial ranges A1:A3 and C1:C3. But "A1:A3, C1:C3" could as well indicate two arguments (for a function with two parameters). If ranges are given in this form, then Excel indeed interprets this as two arguments. However, when the entire range of cells is placed in parentheses (that is, (A1:A3, C1:C3) or when the range is stored under a name, then Excel considers the argument a single parameter.

For this reason the programming of functions that are meant to work with any number of combined ranges of cells is a bit complicated. The function QuadSum sums the squares of the values of all input cells. Here the parameter of the function is defined as a ParamArray , so that arbitrarily many parameters can be passed. For each of these parameters all cells of the partial range are squared and summed into result . Then, thanks to the test TypeName(var)="Range" , the function QuadSum works also with numeric parameters. Thus QuadSum(1, 2, 3) returns 14.

 ' Function.xls, "Module1" ' returns the sum of the squares of all input cells Function  QuadSum  (ParamArray x() As Variant) As Double   Dim var As Variant, result As Double   Dim a As Range, c As Range   For Each var In x()     If TypeName(var) = "Range" Then       For Each a In var.Areas ' all partial ranges         For Each c In a.Cells ' all cells of the partial range           result = result + c ^ 2         Next c       Next a     Else       result = result + var ^ 2     End If   Next var   QuadSum = result End Function 

Error Checking

If you wish to program a function that is not conceived for ranges of cells, but exclusively for individual values, you should use an error check to exclude the possibility of invalid parameters being passed. The function Discount thus protected would look something like the following:

 Function  Discount  (unitprice As Variant, pieces As Variant) As Variant   On Error Resume Next   If TypeName(unitprice) = "Range" Then     If unitprice.Count > 1 Then       Discount = CVErr(xlErrValue): Exit Function     End If   End If   If TypeName(pieces) = "Range" Then     If pieces.Count > 1 Then       Discount = CVErr(xlErrValue): Exit Function     End If   End If   If pieces >= 10 Then     Discount = pieces * unitprice * 0.95   Else     Discount = pieces * unitprice   End If   If Err Then Discount = CVErr(xlErrValue) End Function 

CVErr(xlErrValue) returns a variant data type of subtype Error containing an error number. In a worksheet, you see #VALUE in the cell in which the function is used. More information on the subject of error checking can be found in Chapter 6.

Matrix Functions

Excel recognizes so-called matrix functions. Since these functions seldom occur in the everyday use of Excel, we first present an example of the application of a matrix function that is part of Excel. The function LINEST calculates the slope m and y -intercept b for the line that is the best least squares approximation to a given set of points (arrays of y -values and x -values are passed as parameters). If there are several ranges of x -values, then instead of a single value m the values m ₁ , m ₂ , are returned corresponding to the equation y = m ₁ x ₁ + m ₂ x ₂ + + b . (Extensive information on LINEST can be found in the on-line help.)

The function LINEST is interesting for us here because it returns a matrix (namely, in the sequence , m ₃ , m ₂ , m ₁ , b ). Since only one result can be displayed in a cell, matrix functions must be divided over several cells. Moreover, matrix formulas must be so identified. Excel has a rather complex input form for them:

You must first select, with the mouse or with Shift and the cursor keys, as a range all cells in which the results of the matrix function are to be displayed. Then you press F2 to input the function into the active cell of the range (it doesn't matter in which cell of the range). Finally, the input must be terminated with Ctrl+Shift+Return . With this, the formula is copied into all cells of the range. The entire range is now considered to be a matrix.

Furthermore, you can transform a normal formula into a matrix formula: Move the cell pointer into the cell that contains the formula, beginning with it select the remaining cells, press F2 , and confirm the unchanged formula with Ctrl+Shift+Return . It is not possible to convert individual cells into a matrix. You can carry out changes only with Ctrl+Shift+Return . Then they again hold for all cells of the matrix. It is also impossible to delete individual cells. You can delete only the entire matrix.

Programming Matrix Functions

Programming matrix functions is simpler (from a purely formal point of view) than placing a matrix formula into a worksheet. The only difference from normal functions is that the result is not a single value, but rather must be returned as a field. The following example shows a matrix function that takes no parameters and returns a 2*2 matrix with the values 1, 2, 3, and 4. This function demonstrates only the syntax of a matrix function, without accomplishing anything useful.

 Function  Matrix  () As Variant   Dim x(1, 1) As Double   x(0, 0) = 1: x(0, 1) = 2   x(1, 0) = 3: x(1, 1) = 4   Matrix = x() End Function 

The Volatile Method

With the instruction Application. Volatile a user-defined function can be set as "volatile." (Normally, functions are recalculated only when their precursor , or precedent, cells change. This is usually sufficient and certainly much more efficient.)

 Public Function  VolatileTest  () As Double   Application.Volatile True   VolatileTest = Rnd End Function 

If you input the formula = VolatileTest() into a cell, the indicated value will change whenever any cell in the worksheet is changed or recalculated.

Peculiarities and Problems

Basically, the same rules apply to the code of user-defined functions as for all other procedures. However, there are a few exceptions:

• It is not possible to change ranges of cells in the worksheet directly with user-defined functions that are used within a worksheet. (For example, you cannot write a function that will color the cell green or red according to its content. However, you can accomplish this by another route, namely, with the FormatCondition object, described at the beginning of Chapter 9.)

  Not only that: Even if you do not wish to change ranges of cells, many Excel methods and properties, such as SpecialCells and CurrentRegion, do not work in accessing associated ranges of cells.

  Note that in testing such a function in the immediate window everything works just as you would wish. (The changing of ranges of cells is permitted with VBA code.) But when you use the function in a worksheet, then errors occur. These restrictions are nowhere documented, and thus it is difficult to say how many methods and properties are affected.

• If an error occurs in a user-defined function (such as division by 0), then in the result cell the error message #VALUE! is displayed. There is no VBA error message, however. The same things holds when an error occurs in the passing of parameters (such as an incorrect data type). This behavior makes it much more difficult to detect errors in user-defined functions. The solution is to attempt to run the function in the immediate window with comparable parameters.

• Since Excel 5 there have been continuing problems with the automatic recalculation of cells. Earlier Excel versions occasionally did not recalculate all cells after a change in the worksheet structure, and thus there were old, and hence false, results to be seen. Many of these problems appeared only with user-defined functions, while others occurred exclusively with built-in functions.

  In this regard, the information police in the hallowed halls of Microsoft have been more than reticent (to formulate it politely). The problems appearing in Excel 97 were solved only in a series of updates. The best source of information around this issue was http://www.wopr.com/wow/wowarch.shtml.

  There have been no known problems in Excel 2000 and 2002 with the calculation functions. There is, however, an apparent problem that fortunately appears only during code development: After the VBA code of a user defined function has been changed, the affected cells are not automatically recalculated. The recalculation must be accomplished explicitly with F9 .

Examples

In this section you will find some additional user-defined functions. A more complex function has been introduced previously: Holiday(date) tests whether a given day is a holiday and returns the name of the holiday if it is.

NumberToText

The following example is suited above all for applications in the world of banking (for example, for check writing). Given a number, the function NumberToText returns a character string in which each digit is given as text. For example, given 12.34 the function returns the character string ” one two point three four ”". The function works within the range ±9999999999.99. At most two decimal places are returned. Numbers between “0.005 and 0.005 are represented by " ”zero ”".

The function first tests whether the parameter x contains a valid number for being converted. If that is the case, then the number is converted to a character string with Format . So that this character string can be more easily further processed , as many space characters as necessary so that the character strings are of a uniform length are added with Space . If the character string ends with ".00", the last three characters of the string are ignored in the conversion to the text of digits.

 'example file function.xls; Function  NumberToText  (x As Variant) As String   Dim i&, result As String, character$, lastchar&   Dim digit$(9)   digit(0) = "zero": digit(1) = "one": digit(2) = "two"   digit(3) = "three": digit(4) = "four": digit(5) = "five"   digit(6) = "six": digit(7) = "seven": digit(8) = "eight"   digit(9) = "nine"   If isEmpty(x) Then NumberToText = "": Exit Sub   If x >= 10000000000# Or x <= -10000000000# Then     NumberToText = "number too big or too small"     Exit Function   End If   If x < 0 Then result = "minus ": x = -x   x = Format$(x, "0.00")   x =   x = Space(13 - Len(x)) + x   If Right(x, 3) = ".00" Then lastchar = 10 Else lastchar = 13   For i = 1 To lastchar     character = Mid(x, i, 1)     If character >= "0" And character <= "9" Then       result = result + digit(Val(character)) + " "     ElseIf character = "." Then       result = result + "point "     End If   Next i   NumberToText = "------ " + Trim(result) + " ------" End Function 

AverageProduct

Most problems in the programming of new functions, such as for statistical applications, are caused by their parameters, say if two ranges of cells are to be passed that must be exactly the same size , or if the number of ranges should be variable. In the following two examples you should therefore pay attention to the evaluation of the list of parameters.

AverageProduct expects two ranges as parameters. These ranges are not permitted to be formed of partial ranges (control with Area.Count ) and must have the same number of cells (control with Cells.Count ). If that is the case, then the first cell of the first range is multiplied by the first cell of the second range. Similarly, products are taken of the corresponding second, third, and so on, cells, and these products are then summed. Finally, this sum is divided by the number of cells in each range. A correct call to the function looks like this: = AverageProduct(F19:F22, G19:G22) .

 Public Function  AverageProduct  (p As Range, q As Range) As Variant   Dim i&, result As Double   If p.Areas.Count > 1 Or q.Areas.Count > 1 Then     AverageProduct = CVErr(xlErrRef): Exit Function   End If   If p.Cells.Count <> q.Cells.Count Then     AverageProduct = CVErr(xlErrRef): Exit Function   End If   For i = 1 To p.Cells.Count     result = result + p.Cells(i) * q.Cells(i)   Next   AverageProduct = result / p.Cells.Count End Function 

HighestAverageArgument

The function HighestAverageArgument expects as parameter an arbitrary number of ranges of cells, which can be of various sizes. For each range an average is computed. The function returns the number of the range whose average value is the greatest. (In the case of two or more ranges with the same average, the function returns the number of the first range.) A possible function call is the following:

 =HighestAverageArgument(F25:F27, G25:G26, H25:H28). 

Because of the use of ParamArray , it is impossible to declare p as a Range . Therefore, the correct passing of the ranges is tested in program code with a TypeName test. As in the example above, multipartite ranges are rejected. Likewise, the function is ensured against a range of cells containing only text (no numbers), in which case calculating the average value with the function Average leads to an error (test with IsNumeric for the result of Average ).

 Public Function  HighestAverageArgument  (ParamArray p() As Variant) As Variant   Dim nrOfRanges&, i&, maxnr&   Dim averg() As Double, tmp As Double   nrOfRanges = UBound(p())    'UBound returns 2, if 3 parameters   ReDim averg(nrOfRanges)     ' are passed   ' calculate average value for all ranges of cells   ' and store in a field   For i = 0 To nrOfRanges     If TypeName(p(i)) <> "Range" Then       HighestAverageArgument = CVErr(xlErrValue): Exit Function     End If     If p(i).Areas.Count > 1 Then       HighestAverageArgument = CVErr(xlErrRef): Exit Function     End If     averg(i) = WorksheetFunction.Average(p(i))     If Not IsNumeric(averg(i)) Then       HighestAverageArgument = CVErr(xlErrValue): Exit Function     End If   Next   ' find highest value   For i = 0 To nrOfRanges     If averg(i) > tmp Then       tmp = averg(i)       maxnr = i     End If   Next   ' return result; plus 1, thus 1 for the first range   ' (and not 0)   HighestAverageArgument = maxnr + 1 End Function