Some Useful Worksheet Functions

The examples in this section are custom functions that can be used in worksheet formulas. Remember, these Function procedures must be defined in a VBA module (not a code module associated with ThisWorkbook , a Sheet , or a UserForm ).

CD-ROM  

The examples in this section are available on the companion CD-ROM in a file named  worksheet functions.xlsm .

Returning cell formatting information

This section contains a number of custom functions that return information about a cell's formatting. These functions are useful if you need to sort data based on formatting (for example, sort such that all bold cells are together). Keep in mind that these functions work only with explicitly-applied formatting; they do not work for formatting applied using conditional formatting.

Caution  

You'll find that these functions aren't always updated automatically. This is because changing formatting, for example, doesn't trigger Excel's recalculation engine. To force a global recalculation (and update all the custom functions), press Ctrl+Alt+F9.

Alternatively, you can add the following statement to your function:

 Application.Volatile 

When this statement is present, then pressing F9 will recalculate the function.

The following function returns TRUE if its single-cell argument has bold formatting. If a range is passed as the argument, the function uses the upper-left cell of the range.

 Function IsBold(cell) As Boolean '   Returns TRUE if cell is bold     IsBold = cell.Range("A1").Font.Bold End Function 

The following function returns TRUE if its single-cell argument has italic formatting:

 Function IsItalic(cell) As Boolean '   Returns TRUE if cell is italic     IsItalic = cell.Range("A1").Font.Italic End Function 

Both of the preceding functions will return an error if the cell has mixed formatting - for example, if only some characters are bold. The following function returns TRUE only if all characters in the cell are bold:

 Function AllBold(cell) As Boolean '   Returns TRUE if all characters in cell are bold     If IsNull(cell.Font.Bold) Then         AllBold = False     Else         AllBold = cell.Font.Bold     End If End Function 

The ALLBOLD function can be simplified as follows :

 Function AllBold (cell) As Boolean '   Returns TRUE if all characters in cell are bold     AllBold = Not IsNull(cell.Font.Bold) End Function 

The FillColor function returns an integer that corresponds to the color index of the cell's interior. The actual color depends on the workbook theme that's applied. If the cell's interior is not filled, the function returns “4142 .

 Function FillColor(cell) As Integer '   Returns an integer corresponding to '   cell's interior color     FillColor = cell.Range("A1").Interior.ColorIndex End Function 

A talking worksheet

The SayIt function uses Excel's text-to-speech generator to "speak" it's argument (which can be literal text or a cell reference).

 Function SayIt(txt)     Application.Speech.Speak (txt)     SayIt = txt End Function 

This function has some amusement possibilities, but it can also be useful. For example, use the function in a formula like this:

 =IF(SUM(A:A)>25000,SayIt("Goal Reached")) 

If the sum of the values in column A exceeds 25,000, you'll hear the synthesized voice tell you that the goal has been reached. You can also use the Speak method at the end of a lengthy procedure. That way, you can do something else and you'll get an audible notice when the procedure ends.

Displaying the date when a file was saved or printed

An Excel workbook contains several built-in document properties, accessible from the BuiltinDocumentProperties property of the Workbook object. The following function returns the date and time that the workbook was last saved:

 Function LastSaved()     Application.Volatile     LastSaved = ThisWorkbook. _       BuiltinDocumentProperties("Last Save Time") End Function 

The following function is similar, but it returns the date and time when the workbook was last printed or previewed. If the workbook has never been printed or previewed, the function returns a #VALUE error.

 Function LastPrinted()     Application.Volatile     LastPrinted = ThisWorkbook. _       BuiltinDocumentProperties("Last Print Date") End Function 

If you use these functions in a formula, you might need to force a recalculation (by pressing F9) to get the current values of these properties.

Note  

Quite a few additional built-in properties are available, but Excel does not use all of them. For example, attempting to access the Number of Bytes property will generate an error. For a list of all built-in properties, consult the Help.

The preceding LastSaved and LastPrinted functions are designed to be stored in the workbook in which they are used. In some cases, you might want to store the function in a different workbook (for example, personal.xlsb ) or in an add-in. Because these functions reference ThisWorkbook , they will not work correctly. Following are more general-purpose versions of these functions. These functions use Application.Caller , which returns a Range object that represents the cell that calls the function. The use of Parent.Parent returns the workbook (that is, the parent of the parent of the Range object - a Workbook object). This topic is explained further in the next section.

 Function LastSaved2()     Application.Volatile     LastSaved2 = Application.Caller.Parent.Parent. _       BuiltinDocumentProperties("Last Save Time") End Function 

Understanding object parents

As you know, Excel's object model is a hierarchy: Objects are contained in other objects. At the top of the hierarchy is the Application object. Excel contains other objects, and these objects contain other objects, and so on. The following hierarchy depicts how a Range object fits into this scheme:

 Application object       Workbook object        Worksheet object         Range object 

In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's parent is the Workbook object that contains the worksheet, and a Workbook object's parent is the Application object.

How can this information be put to use? Examine the SheetName VBA function that follows. This function accepts a single argument (a range) and returns the name of the worksheet that contains the range. It uses the Parent property of the Range object. The Parent property returns an object: the object that contains the Range object.

 Function SheetName(ref) As String     SheetName = ref.Parent.Name End Function 

The next function, WorkbookName , returns the name of the workbook for a particular cell. Notice that it uses the Parent property twice. The first Parent property returns a Worksheet object, and the second Parent property returns a Workbook object.

 Function WorkbookName(ref) As String     WorkbookName = ref.Parent.Parent.Name End Function 

The AppName function that follows carries this exercise to the next logical level, accessing the Parent property three times. This function returns the name of the Application object for a particular cell. It will, of course, always return Microsoft Excel .

 Function AppName(ref) As String     AppName = ref.Parent.Parent.Parent.Name End Function 

Counting cells between two values

The following function, named CountBetween , returns the number of values in a range (first argument) that fall between values represented by the second and third arguments:

 Function CountBetween(InRange, num1, num2) As Long '   Counts number of values between num1 and num2     With Application.WorksheetFunction         If num1 <= num2 Then             CountBetween = .CountIf(InRange, ">=" & num1) - _               .CountIf(InRange, ">" & num2)         Else             CountBetween = .CountIf(InRange, ">=" & num2) - _               .CountIf(InRange, ">" & num1)         End If     End With End Function 

Note that this function uses Excel's COUNTIF function. In fact, the CountBetween function is essentially a wrapper that can simplify your formulas.

Following is an example formula that uses the CountBetween function. The formula returns the number of cells in A1:A100 that are greater than or equal to 10 and less than or equal to 20.

 =CountBetween(A1:A100,10,20) 

Using this VBA function is simpler than entering the following lengthy (and somewhat confusing) formula:

 =COUNTIF(A1:A100,">=10")-COUNTIF(A1:A100,">20") 

Counting visible cells in a range

The CountVisible function that follows accepts a range argument and returns the number of non-empty visible cells in the range. A cell is not visible if it's in a hidden row or a hidden column.

 Function CountVisible(rng) '   Counts visible cells     Dim CellCount As Long     Dim cell As Range     Application.Volatile     CellCount = 0     Set rng = Intersect(rng.Parent.UsedRange, rng)     For Each cell In rng        If Not IsEmpty(cell) Then           If Not cell.EntireRow.Hidden And _              Not cell.EntireColumn.Hidden Then _              CellCount = CellCount + 1         End If     Next cell     CountVisible = CellCount End Function 

This function loops through each cell in the range, first checking whether the cell is empty. If it's not empty, it checks the hidden properties of the cell's row and column. If neither the row nor column is hidden, the CellCount variable is incremented.

The CountVisible function is useful when you're working with AutoFilters or outlines. Both of these features make use of hidden rows.

Tip  

If you're using Excel 2003 or later, the CountVisible function is no longer required. You can use Excel's SUBTOTAL function instead. For example, to count the visible numeric cells in a range named data, use this formula:

 =SUBTOTAL(103,data) 

This formula works even if rows or columns are hidden manually. In previous versions, the SUBTOTAL function worked correctly only if the cells were hidden by using AutoFilters or outlines.

Determining the last non-empty cell in a column or row

In this section, I present two useful functions: LastInColumn returns the contents of the last non-empty cell in a column; LastInRow returns the contents of the last non-empty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column (for LastInColumn ) or a complete row (for LastInRow ). If the supplied argument is not a complete column or row, the function uses the column or row of the upper-left cell in the range. For example, the following formula returns the last value in column B:

 =LastInColumn(B5) 

The following formula returns the last value in row 7:

 =LastInRow(C7:D9) 

The LastInColumn function follows:

 Function LastInColumn(rng As Range) '   Returns the contents of the last non-empty cell in a column     Dim LastCell As Range     Application.Volatile     With rng.Parent         With .Cells(.Rows.Count, rng.Column)             If Not IsEmpty(.Value) Then                 LastInColumn = .Value             ElseIf IsEmpty(.End(xlUp)) Then                 LastInColumn = ""             Else                 LastInColumn = .End(xlUp).Value             End If          End With     End With End Function 

This function is rather complicated, so here are a few points that may help you understand it:

• Application.Volatile causes the function to be executed whenever the sheet is calculated.

• Rows.Count returns the number of rows in the worksheet. I used this, rather than hard-coding the value, because not all worksheets have the same number of rows.

• rng.Column returns the column number of the upper-left cell in the rng argument.

• Using rng.Parent causes the function to work properly even if the rng argument refers to a different sheet or workbook.

• The End method (with the xlUp argument) is equivalent to activating the last cell in a column, pressing End, and then pressing the up-arrow key.

• The IsEmpty function checks whether the cell is empty. If so, it returns an empty string. Without this statement, an empty cell would be returned as .

The LastInRow function follows. This is very similar to the LastInColumn function.

 Function LastInRow(rng As Range) '   Returns the contents of the last non-empty cell in a row     Application.Volatile     With rng.Parent         With .Cells(rng.Row, .Columns.Count)             If Not IsEmpty(.Value) Then                 LastInRow = .Value             ElseIf IsEmpty(.End(xlToLeft)) Then                 LastInRow = ""             Else                 LastInRow = .End(xlToLeft).Value             End If         End With     End With End Function 

Does a string match a pattern?

The IsLike function is very simple (but also very useful). This function returns TRUE if a text string matches a specified pattern.

This function, which follows, is remarkably simple. As you can see, the function is essentially a wrapper that lets you take advantage of VBA's powerful Like operator in your formulas.

 Function IsLike(text As String, pattern As String) As Boolean '   Returns true if the first argument is like the second     IsLike = text Like pattern End Function 

This IsLike function takes two arguments:

• text : A text string or a reference to a cell that contains a text string

• pattern : A string that contains wildcard characters according to the following list:

Open table as spreadsheet

Character(s) in pattern	Matches in text
?	Any single character
*	Zero or more characters
#	Any single digit (0 “9)
[ charlist ]	Any single character in charlist
[! charlist ]	Any single character not in charlist

The following formula returns TRUE because * matches any number of characters. It returns TRUE if the first argument is any text that begins with g.

 =IsLike("guitar","g*") 

The following formula returns TRUE because ? matches any single character. If the first argument were "Unit12" , the function would return FALSE .

 =IsLike("Unit1","Unit?") 

The next formula returns TRUE because the first argument is a single character in the second argument.

 =ISLIKE("a","[aeiou]") 

The following formula returns TRUE if cell A1 contains a, e, i, o, u, A, E, I, O, or U. Using the UPPER function for the arguments makes the formula not case-sensitive.

 =IsLike(UPPER(A1), UPPER("[aeiou]")) 

The following formula returns TRUE if cell A1 contains a value that begins with 1 and has exactly three digits (that is, any integer between 100 and 199).

 =IsLike(A1,"1##") 

Extracting the nth element from a string

ExtractElement is a custom worksheet function (which can also be called from a VBA procedure) that extracts an element from a text string. For example, if a cell contains the following text, you can use the ExtractElement function to extract any of the substrings between the hyphens.

 123-456-789-0133-8844 

The following formula, for example, returns 0133 , which is the fourth element in the string. The string uses a hyphen ( - ) as the separator.

 =ExtractElement("123-456-789-0133-8844",4,"-") 

The ExtractElement function uses three arguments:

• Txt : The text string from which you're extracting. This can be a literal string or a cell reference.

• n : An integer that represents the element to extract.

• Separator : A single character used as the separator.

Note  

If you specify a space as the Separator character, multiple spaces are treated as a single space, which is almost always what you want. If n exceeds the number of elements in the string, the function returns an empty string.

The VBA code for the ExtractElement function follows:

 Function ExtractElement(Txt, n, Separator) As String '   Returns the  n  th 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 uses VBA's Split function, which returns a variant array that contains each element of the text string. This array begins with (not 1 ), so using n-1 references the desired element.

The Split function was introduced in Excel 2000. For compatibility with older versions of Excel, you need to use the following function:

 Function ExtractElement2(Txt, n, Separator) As String '   Returns the  n  th element of a text string, where the '   elements are separated by a specified separator character     Dim Txt1 As String, TempElement As String     Dim ElementCount As Integer, i As Integer     Txt1 = Txt '   If space separator, remove excess spaces     If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1) '   Add a separator to the end of the string     If Right(Txt1, Len(Txt1)) <> Separator Then _         Txt1 = Txt1 & Separator '   Initialize     ElementCount = 0     TempElement = "" '   Extract each element     For i = 1 To Len(Txt1)         If Mid(Txt1, i, 1) = Separator Then             ElementCount = ElementCount + 1             If ElementCount = n Then '              Found it, so exit                ExtractElement2 = TempElement                Exit Function             Else                TempElement = ""             End If         Else             TempElement = TempElement & Mid(Txt1, i, 1)         End If     Next i     ExtractElement2 = "" End Function 

A multifunctional function

This example describes a technique that may be helpful in some situations: making a single worksheet function act like multiple functions. For example, the following VBA listing is for a custom function called StatFunction . It takes two arguments: the range ( rng ) and the operation ( op ). Depending on the value of op , the function returns a value computed using any of the following worksheet functions: AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR.

For example, you can use this function in your worksheet as follows:

 =StatFunction(B1:B24,A24) 

The result of the formula depends on the contents of cell A24, which should be a string such as Average , Count , Max , and so on. You can adapt this technique for other types of functions.

 Function StatFunction(rng, op)     Select Case UCase(op)         Case "SUM"             StatFunction = WorksheetFunction.Sum(rng)         Case "AVERAGE"             StatFunction = WorksheetFunction.Average(rng)         Case "MEDIAN"             StatFunction = WorksheetFunction.Median(rng)         Case "MODE"             StatFunction = WorksheetFunction.Mode(rng)         Case "COUNT"             StatFunction = WorksheetFunction.Count(rng)         Case "MAX"             StatFunction = WorksheetFunction.Max(rng)         Case "MIN"             StatFunction = WorksheetFunction.Min(rng)         Case "VAR"             StatFunction = WorksheetFunction.Var(rng)         Case "STDEV"             StatFunction = WorksheetFunction.StDev(rng)         Case Else             StatFunction = CVErr(xlErrNA)     End Select End Function 

The SheetOffset function

You probably know that Excel's support for 3-D workbooks is limited. For example, if you need to refer to a different worksheet in a workbook, you must include the worksheet's name in your formula. This is not a big problem until you attempt to copy the formula across other worksheets. The copied formulas continue to refer to the original worksheet name, and the sheet references are not adjusted as they would be in a true 3-D workbook.

The example discussed in this section is a VBA function (named SheetOffset ) that enables you to address worksheets in a relative manner. For example, you can refer to cell A1 on the previous worksheet by using this formula:

 =SheetOffset(-1,A1) 

The first argument represents the relative sheet, and it can be positive, negative, or zero. The second argument must be a reference to a single cell. You can copy this formula to other sheets, and the relative referencing will be in effect in all the copied formulas.

The VBA code for the SheetOffset function follows:

 Function SheetOffset(Offset As Long, Optional Cell As Variant) '   Returns cell contents at Ref, in sheet offset     Dim WksIndex As Long, WksNum As Long     Dim wks As Worksheet     Application.Volatile     If IsMissing(Cell) Then Set Cell = Application.Caller     WksNum = 1     For Each wks In Application.Caller.Parent.Parent.Worksheets         If Application.Caller.Parent.Name = wks.Name Then             SheetOffset = Worksheets(WksNum + Offset).Range(Cell(1).Address)             Exit Function         Else             WksNum = WksNum + 1         End If     Next wks End Function 

Returning the maximum value across all worksheets

If you need to determine the maximum value in cell B1 across a number of worksheets, you would use a formula such as this:

 =MAX(Sheet1:Sheet4!B1) 

This formula returns the maximum value in cell B1 for Sheet1 , Sheet4 , and all the sheets in between.

But what if you add a new sheet ( Sheet5 ) after Sheet4 ? Your formula won't adjust automatically, so you need to edit it to include the new sheet reference:

 =MAX(Sheet1:Sheet5!B1) 

The MaxAllSheets function, which follows, accepts a single-cell argument and returns the maximum value in that cell across all worksheets in the workbook. The formula that follows, for example, returns the maximum value in cell B1 for all sheets in the workbook:

 =MaxAllSheets(B1) 

If you add a new sheet, there's no need to edit the formula:

 Function MaxAllSheets(cell)     Dim MaxVal As Double     Dim Addr As String     Dim Wksht As Object     Application.Volatile     Addr = cell.Range("A1").Address     MaxVal = -9.9E+307     For Each Wksht In cell.Parent.Parent.Worksheets         If Wksht.Name = cell.Parent.Name And _            Addr = Application.Caller.Address Then         '  avoid circular reference     Else         If IsNumeric(Wksht.Range(Addr)) Then             If Wksht.Range(Addr) > MaxVal Then _               MaxVal = Wksht.Range(Addr).Value             End If         End If     Next Wksht     If MaxVal = -9.9E+307 Then MaxVal = 0     MaxAllSheets = MaxVal End Function 

The For Each statement uses the following expression to access the workbook:

 cell.Parent.Parent.Worksheets 

The parent of the cell is a worksheet, and the parent of the worksheet is the workbook. Therefore, the For Each-Next loop cycles among all worksheets in the workbook. The first If statement inside the loop performs a check to see whether the cell being checked is the cell that contains the function. If so, that cell is ignored to avoid a circular reference error.

Note  

This function can be modified easily to perform other cross-worksheet calculations, such as minimum, average, sum, and so on.

Returning an array of nonduplicated random integers

The function in this section, RandomIntegers , returns an array of nonduplicated integers. The function is intended to be used in a multicell array formula.

 {=RandomIntegers()} 

Select a range and then enter the formula by pressing Ctrl+Shift+Enter. The formula returns an array of nonduplicated integers, arranged randomly . For example, if you enter the formula into a 50-cell range, the formulas will return nonduplicated integers from 1 to 50.

The code for RandomIntegers follows:

 Function RandomIntegers()     Dim FuncRange As Range     Dim V() As Variant, ValArray() As Variant     Dim CellCount As Double     Dim i As Integer, j As Integer     Dim r As Integer, c As Integer     Dim Temp1 As Variant, Temp2 As Variant     Dim RCount As Integer, CCount As Integer '   Create Range object     Set FuncRange = Application.Caller '   Return an error if FuncRange is too large     CellCount = FuncRange.Count     If CellCount > 1000 Then         RandomIntegers = CVErr(xlErrNA)         Exit Function     End If '   Assign variables     RCount = FuncRange.Rows.Count     CCount = FuncRange.Columns.Count     ReDim V(1 To RCount, 1 To CCount)     ReDim ValArray(1 To 2, 1 To CellCount) '   Fill array with random numbers '   and consecutive integers     For i = 1 To CellCount         ValArray(1, i) = Rnd         ValArray(2, i) = i     Next i '   Sort ValArray by the random number dimension     For i = 1 To CellCount         For j = i + 1 To CellCount             If ValArray(1, i) > ValArray(1, j) Then                Temp1 = ValArray(1, j)                Temp2 = ValArray(2, j)                ValArray(1, j) = ValArray(1, i)                ValArray(2, j) = ValArray(2, i)                ValArray(1, i) = Temp1                ValArray(2, i) = Temp2             End If         Next j Next i '   Put the randomized values into the V array     i = 0     For r = 1 To RCount         For c = 1 To CCount             i = i + 1             V(r, c) = ValArray(2, i)         Next c     Next r     RandomIntegers = V End Function 

Randomizing a range

The RangeRandomize function, which follows, accepts a range argument and returns an array that consists of the input range - in random order:

 Function RangeRandomize(rng)     Dim V() As Variant, ValArray() As Variant     Dim CellCount As Double     Dim i As Integer, j As Integer     Dim r As Integer, c As Integer     Dim Temp1 As Variant, Temp2 As Variant     Dim RCount As Integer, CCount As Integer '   Return an error if rng is too large     CellCount = rng.Count     If CellCount > 1000 Then         RangeRandomize = CVErr(xlErrNA)         Exit Function     End If '   Assign variables     RCount = rng.Rows.Count     CCount = rng.Columns.Count     ReDim V(1 To RCount, 1 To CCount)     ReDim ValArray(1 To 2, 1 To CellCount) '   Fill ValArray with random numbers '   and values from rng     For i = 1 To CellCount         ValArray(1, i) = Rnd         ValArray(2, i) = rng(i)     Next i '   Sort ValArray by the random number dimension     For i = 1 To CellCount         For j = i + 1 To CellCount             If ValArray(1, i) > ValArray(1, j) Then                 Temp1 = ValArray(1, j)                 Temp2 = ValArray(2, j)                 ValArray(1, j) = ValArray(1, i)                 ValArray(2, j) = ValArray(2, i)                 ValArray(1, i) = Temp1                 ValArray(2, i) = Temp2             End If         Next j     Next i '   Put the randomized values into the V array     i = 0     For r = 1 To RCount         For c = 1 To CCount             i = i + 1             V(r, c) = ValArray(2, i)         Next c     Next r RangeRandomize = V End Function 

The code is very similar to that for the RandomIntegers function.

Figure 11-17 shows the function in use. The array formula in B2:B11 is:

Figure 11-17: The RangeRandomize function returns the contents of a range, in random order.

 {= RangeRandomize(A2:A11)} 

This formula returns the contents of A2:A11, but in random order.