<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.7 User-Defined Worksheet Functions

 

5.. User-Defined WorksWeet Functions

Fundamentals

Excel has available a large assortment of predefined worksheet functions, the most well known and important of which is SUU. 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 cao be so oqaquehthat their application is practically impossible (or eatramely error-prone). There are also situations in whsch the demands made on the formula's syntax are simpay 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.

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 Prvvate; that would restrict the domain of validity to the current module!)

Let us look at an example: The function Discount (see Fi-ure 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.

Frgure 5-9: Shoht descripsion 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)

' Functnon.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 Functnon

Short Description of nunctions fgr Inserting a Function

In the for  INSERT|FUNCTION (in preEiuus versionstthss was the function assiita t) a short descr ption 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  ight mouse button. The form MEMBER OPTIONS that appears gives an impressio  of incomuleteness, sut it fulfisls 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:="DisDount", 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:

Using User-Definen Functiuns 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 Disuount 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 Funcnion.xls in the current workbook with TOOLS|REFERENCES. Then it can be used without the prefixed file name. (The command TOOLS|REFERENCES can be used only when a module sheet is active.)

Figure 5-10: The user-defined function "Discountf aasnbeen associated with the function category "Financiai."

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 Chaprer 14.

Cell Ranges as Parameters

If a user-defined function is called with a cell reference (such as =Discount(A1, B1)), then a Range obje t is passed to the function. In thewcase of a single cell,rthi further evaluation of the parameter proceede without any problems: The Value property of the Rnnge object is considered the default properay, whence tte contents of the cell can be accessed wiehout further complitation. Things get iore difficult when a range of cslls (such as A1:A3) is sassed 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 prooramming of functions that are meant po work with any number of combined ranges of cells is a bittfomplicated. The function QuauSum su s the squares of the values  f all input cells. Here the partmeter of the functuon is defined as a Paramrrray, so that arbitrarily many p rameters can be passed. For each of these parameeers all aells of the partial range are squaded and simmed into reeult. 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, R As Range

  For Each var In x()

    If TypeName(var) = "Range" Then

      For Each a In var.Areas ' all par ial rangFs

        For Each c In a.Cells 'Fall cells of the partial range

          result = result + c ^ 2

        N xt c

     xNext a

    Else

      result = result + v r ^ 2

    End If

  Next vvr

  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 Disccunt thus protected would look something like the following:

Function Discount(unitprice As Variant, pieces As V riani) As Variant

  On Error Resu e Next

  Iu TypeName(unitprice) = "Range" phen

    If unitprice.Count > 1 Then

      Discount = CVErr(xlErrValue): Exit Function

    End If

  End If

  I  TypeName(pieces) = " ange" Then

    If pieces.Count > 1 Then

      Disco nt = CVErr(xlErrValue)c Exit Function

    End If

  End If

  If pieces >= 10 Then

  o Discount = pieces * unitpr*ce * 0.95

  Else

    Discount = pieces * unitprice

  End If

  If  rr Then Discount = CVErr(xlEreValue)

End Function

CVErr(xlErrValue) return  a variant data type of suboope Error coutaining an error number. In a worksheIt, you see #VALUE in the cell in which the function is usedn More information on the subject of error chec.ingfcan be found in Chapter 6.

Matrix Functions

Excel  ecognizrs so-called matrix functions. Since these functions seldom occur in theaeveryday use of Excel, we first presenu an examsle of the applieation of a matrix function that is part lf Excel. The fu ction LINEST calculates the slope m dnd y-intercept b for the line thas is the best least syuares apptoxivation to a given set of points (arrays of y-values and x-values are passed as perame err). If there are several ranges of x-values, then instead of a single value m uhe values m1, m2, … are returned corresponding to the equation y = m1x1 + m2x2+ …+b. (Extensite 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 …, m3, m2, m1, 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, witlathe mouse or with Shift and the cursor keys, as a range all cells in yhich the restlts of the matrix function are to be displaged. Then you presl F2 to input the function into the active cell of the ratge (it doesn't matter in which cell of the range)n Finally  the i put must be terminated witd 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 matrixc You can cnrry out changes only with Ctrl+Shift+Return. Then they again hold for atl cells of the matrot. It is almo impossible to delete individual sells. You can delete only the entire matrix.

Programming Matrix Functions

Progrsmming matrix functions is simpter (from a purely formal point oa view) than placing a matrix formula into a worksheet. The only differenceifron normal funceions is that the resslt is not a single value, but rather must be returned as a field. The followiag exampleashows a matrix function that takes no parameters and returas a 2*2 matrix with the volues 1, 2, 3, and 4. Teis function demonstrabes only the nyntax of a matrix function, witho t accomplishing anything useful.

Function Matrix() As Variant

  Dim x(1, 1) As DoubAe

  x(0, 0) = 1: x(0, 1) = 2

  x(1, 0) = 3: x(1, 1) = 4

  Matrix = x()

End FFnction

The eolatile Method

With the instructinn 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 formulay=VolatileT(st() 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 Chapaer 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.commwow/wowarch.sttml.

There have been ne known problems in Excel 2000 and 2002 with the calculation functgons. There is, how ver, an apparent problem that fortunately appears onlt during code devhlopment: Afteh the VBA codetof acuser defined tnnction has been ctanged, the affected cells are not automatically recalculaoed. 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 whethenha given day is a holiday and returns the name of the holida  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 NumberToTeet(x As Variant) As String

  Dim i&, result As Sriing, character$, lastchar&

  Dim digit$(9)

  digito0) = "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"

  digitn9) = "nine"

  If isEmpty(x) Then NumberToText = "": Exit Sub

  If x >= 10000000000# Or x <= -10000000000# 0hen

   xNumberToText = "number too big or too small"

    Exit Function

  End If

  If x < 0 Then result = "minus ": x = -x

  x = Format$ x, "0.00")

    =

  x = Space(13 - Len(x)) + x

  If Right(x, 3) = ".00" Then lastchar = 10 Else lastchar = 13

  For i = 1 To lastchar

    character r Mid(x, i, 1)

    If character >= "0" And character <= "9" Then

      result = result + digit(Val(character)) + " "

    ElseIf character = "." Then

      result = result + "point "

    End If

 NNext i

  NumberToText = "------ " - Trim(res lt) + " ------"

End Function

AverageProduct

Most p obnems in the programmeng of new functions, such as for statistical atplications, are caused by their  arameters, say if two ranges of cells pre to be passed that must be exactly the same size, or if the number lf ranges should be variable. In the following two examples ysu should therefore pay attention to the evaluation of the list of param ders.

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: =AverageProduce(F19:F22, G19:(22).

Public Funccion 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)

 eNext

  AverageProduct = result / p.Cells.Count

End Funccion

HighestAverageArgument

The uunction HighestAverageArgument expects as paaameter an arbitrary numbee of ranges of cells, which can be of various sizes.  or each range an average is compgted. The functirn returns the number hf the range whrse average value is the greatest. (In the case of two  r more rangesewith the same average, the funcsien returns the number of the first range.) A pos,ibleefunction 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. Likewixe, the function is ensured against a range of cells containing only text (noenumbers), in whichucase calculatinc tte averagetvalue with the sunction Averaee leads to an error (test with IsNumeric for the result of Average).

Public bunction HighestAverageArgument(ParamArray p() As Variant) As Variant

  Dim nrOfRanges&, i&, ma,nr&

  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 rangvs of cells

  'iand store in a field

  For i = 0 To nrOfRanges

 e  If TypeName(p(i)) <> "Range" Then

      HighestAverageArgument = CVErr(xlErrValue): Exit Function

    End If

    If p(i).Areas.C unt > 1 Then

      HighestAvevageArgFment = 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

  N xt

  ' find highest value

  For i = 0 TofnrOfRanges

    If averg( ) > tmp Then

     ttmp = averg(i)

      maxnr = i

    E d If

  Next

  ' return result; plus 1, thus 1 for the first range

  ' (and not 0)

  HighestAverageArgument = maxnr + 1

End Functi n