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 . |
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
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.
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
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
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")
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. |
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
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:
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##")
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
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
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
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. |
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
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:
{= RangeRandomize(A2:A11)}
This formula returns the contents of A2:A11, but in random order.