Excel 2016 Formulas (2016)
PART VI
Developing Custom Worksheet Functions
Chapter 26
VBA Custom Function Examples
In This Chapter
· Simple custom function examples
· A custom function to determine a cell’s data type
· A custom function to make a single worksheet function act like multiple functions
· A custom function for generating random numbers and selecting cells at random
· Custom functions for calculating sales commissions
· Custom functions for manipulating text
· Custom functions for counting and summing cells
· Custom functions that deal with dates
· A custom function example for returning the last nonempty cell in a column or row
· Custom functions that work with multiple worksheets
· Advanced custom function techniques
This chapter is jam-packed with a variety of useful (or potentially useful) VBA custom worksheet functions. You can use many of the functions as they are written. You may need to modify other functions to meet your particular needs. For maximum speed and efficiency, these Function procedures declare all variables that are used.
Simple Functions
The functions in this section are relatively simple, but they can be very useful. Most of them are based on the fact that VBA can obtain helpful information that’s not normally available for use in a formula.
On the Web
This book’s website contains the workbook simple functions.xlsm that includes all the functions in this section.
Is the cell hidden?
The following CELLISHIDDEN function accepts a single cell argument and returns TRUE if the cell is hidden. A cell is considered a hidden cell if either its row or its column is hidden:
Function CELLISHIDDEN(cell As Range) As Boolean
' Returns TRUE if cell is hidden
Dim UpperLeft As Range
Set UpperLeft = cell.Range("A1")
CELLISHIDDEN = UpperLeft.EntireRow.Hidden Or _
UpperLeft.EntireColumn.Hidden
End Function
Using the functions in this chapter
If you see a function listed in this chapter that you find useful, you can use it in your own workbook. All the Function procedures in this chapter are available at this book's website. Just open the appropriate workbook, activate the VB Editor, and copy and paste the function listing to a VBA module in your workbook. It’s impossible to anticipate every function that you’ll ever need. However, the examples in this chapter cover a variety of topics, so it’s likely that you can locate an appropriate function and adapt the code for your own use.
Returning a worksheet name
The following SHEETNAME 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 worksheet object that contains the Range object:
Function SHEETNAME(rng As Range) As String
' Returns the sheet name for rng
SHEETNAME = rng.Parent.Name
End Function
The following function is a variation on this theme. It does not use an argument; rather, it relies on the fact that a function can determine the cell from which it was called by using Application.Caller:
Function SHEETNAME2() As String
' Returns the sheet name of the cell that contains the function
SHEETNAME2 = Application.Caller.Parent.Name
End Function
In this function, the Caller property of the Application object returns a Range object that corresponds to the cell that contains the function. For example, suppose that you have the following formula in cell A1:
=SHEETNAME2()
When the SHEETNAME2 function is executed, the Application.Caller property returns a Range object corresponding to the cell that contains the function. The Parent property returns the Worksheet object, and the Name property returns the name of the worksheet.
You can use the SHEET function to return a sheet number rather than a sheet name.
Returning a workbook name
The next function, WORKBOOKNAME, returns the name of the workbook. Notice that it uses the Parent property twice. The first Parent property returns a Worksheet object, the second Parent property returns a Workbook object, and the Name property returns the name of the workbook:
Function WORKBOOKNAME() As String
' Returns the workbook name of the cell that contains the function
WORKBOOKNAME = Application.Caller.Parent.Parent.Name
End Function
Returning the application’s name
The following function, although not very useful, carries this discussion of object parents to the next logical level by accessing the Parent property three times. This function returns the name of the Application object, which is always the string Microsoft Excel:
Function APPNAME() As String
' Returns the application name of the cell that contains the function
APPNAME = Application.Caller.Parent.Parent.Parent.Name
End Function
Understanding object parents
Objects in Excel are arranged in a hierarchy. At the top of the hierarchy is the Application object (Excel itself). Excel contains other objects; these objects contain other objects, and so on. The following hierarchy depicts the way a Range object fits into this scheme:
· Application object (Excel)
· Workbook object
· Worksheet object
· Range object
In the lingo of object-oriented programming (OOP), a Range object’s parent is the Worksheet object that contains it. A Worksheet object’s parent is the workbook that contains the worksheet. And a Workbook object’s parent is the Application object. Armed with this knowledge, you can use the Parent property to create a few useful functions.
Returning Excel’s version number
The following function returns Excel's version number. For example, if you use Excel 2016, it returns the text string 16.0:
Function EXCELVERSION() as String
' Returns Excel's version number
EXCELVERSION = Application.Version
End Function
Note that the EXCELVERSION function returns a string, not a value. The following function returns TRUE if the application is Excel 2007 or later (Excel 2007 is version 12). This function uses the VBA Val function to convert the text string to a value:
Function EXCEL2007ORLATER() As Boolean
EXCEL2007ORLATER = Val(Application.Version) >= 12
End Function
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, sorting all bold cells together).
Warning
The functions in this section use the following statement:
Application.Volatile True
This statement causes the function to be reevaluated when the workbook is calculated. You’ll find, however, that these functions don’t always return the correct value. This is because changing cell formatting, for example, does not trigger Excel’s recalculation engine. To force a global recalculation (and update all the custom functions), press Ctrl+Alt +F9.
The following function returns TRUE if its single-cell argument has bold formatting:
Function ISBOLD(cell As Range) As Boolean
' Returns TRUE if cell is bold
Application.Volatile True
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 Range) As Boolean
' Returns TRUE if cell is italic
Application.Volatile True
ISITALIC = cell.Range("A1").Font.Italic
End Function
Both of the preceding functions have a slight flaw: they return an error (#VALUE!) if the cell has mixed formatting. For example, it’s possible that only some characters in the cell are bold.
The following function returns TRUE only if all the characters in the cell are bold. If the Bold property of the Font object returns Null (indicating mixed formatting), the If statement generates an error, and the function name is never set to TRUE. The function name was previously set to FALSE, so that’s the value that the function returns:
Function ALLBOLD(cell As Range) As Boolean
' Returns TRUE if all characters in cell are bold
Dim UpperLeft As Range
Application.Volatile True
Set UpperLeft = cell.Range("A1")
ALLBOLD = False
If UpperLeft.Font.Bold Then ALLBOLD = True
End Function
The following FILLCOLOR function returns a value that corresponds to the color of the cell’s interior (the cell’s fill color). If the cell’s interior is not filled, the function returns 16,777,215. The Color property values range from 0 to 16,777,215:
Function FILLCOLOR(cell As Range) As Long
' Returns a value corresponding to the cell's interior color
Application.Volatile True
FILLCOLOR = cell.Range("A1").Interior.Color
End Function
Note
If a cell is part of a table that uses a style, the FILLCOLOR function does not return the correct color. Similarly, a fill color that results from conditional formatting is not returned by this function. In both cases, the function returns 16,777,215.
The following function returns the number format string for a cell:
Function NUMBERFORMAT(cell As Range) As String
' Returns a string that represents
' the cell's number format
Application.Volatile True
NUMBERFORMAT = cell.Range("A1").NumberFormat
End Function
If the cell uses the default number format, the function returns the string General.
Determining a Cell’s Data Type
Excel provides a number of built-in functions that can help determine the type of data contained in a cell. These include ISTEXT, ISNONTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions such as ISEMPTY, ISDATE, and ISNUMERIC.
The following function accepts a range argument and returns a string (Blank, Text, Logical, Error, Date, Time, or Value) that describes the data type of the upper-left cell in the range:
Function CELLTYPE(cell As Range) As String
' Returns the cell type of the upper-left cell in a range
Dim UpperLeft As Range
Application.Volatile True
Set UpperLeft = cell.Range("A1")
Select Case True
Case UpperLeft.NumberFormat = "@"
CELLTYPE = "Text"
Case IsEmpty(UpperLeft.Value)
CELLTYPE = "Blank"
Case WorksheetFunction.IsText(UpperLeft)
CELLTYPE = "Text"
Case WorksheetFunction.IsLogical(UpperLeft.Value)
CELLTYPE = "Logical"
Case WorksheetFunction.IsErr(UpperLeft.Value)
CELLTYPE = "Error"
Case IsDate(UpperLeft.Value)
CELLTYPE = "Date"
Case InStr(1, UpperLeft.Text, ":") <> 0
CELLTYPE = "Time"
Case IsNumeric(UpperLeft.Value)
CELLTYPE = "Value"
End Select
End Function
Figure 26.1 shows the CELLTYPE function in use. Column B contains formulas that use the CELLTYPE function with an argument from column A. For example, cell B1 contains the following formula:
Figure 26.1 The CELLTYPE function returns a string that describes the contents of a cell.
=CELLTYPE(A1)
On the Web
The workbook celltype function.xlsm that demonstrates the CELLTYPE function is available at this book’s website.
A Multifunctional Function
This section demonstrates a technique that may be helpful in some situations—the technique of making a single worksheet function act like multiple functions. The following VBA custom function, named STATFUNCTION, takes two arguments: the range (rng) and the operation (op). Depending on the value of op, the function returns a value computed by 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:
=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 As Variant, op As String) As Variant
Select Case UCase(op)
Case "SUM"
STATFUNCTION = Application.Sum(rng)
Case "AVERAGE"
STATFUNCTION = Application.Average(rng)
Case "MEDIAN"
STATFUNCTION = Application.Median(rng)
Case "MODE"
STATFUNCTION = Application.Mode(rng)
Case "COUNT"
STATFUNCTION = Application.Count(rng)
Case "MAX"
STATFUNCTION = Application.Max(rng)
Case "MIN"
STATFUNCTION = Application.Min(rng)
Case "VAR"
STATFUNCTION = Application.Var(rng)
Case "STDEV"
STATFUNCTION = Application.StDev(rng)
Case Else
STATFUNCTION = CVErr(xlErrNA)
End Select
End Function
Figure 26.2 shows the STATFUNCTION function that is used in conjunction with a drop-down list generated by Excel’s Data ➜ Data Tools ➜ Data Validation command. The formula in cell C14 is as follows:
Figure 26.2 Selecting an operation from the list displays the result in cell C14.
=STATFUNCTION(C1:C12,B14)
On the Web
The workbook, statfunction function.xlsm, shown in Figure 26.2, is available on this book’s website.
The following STATFUNCTION2 function is a much simpler approach that works exactly like the STATFUNCTION function. It uses the Evaluate method to evaluate an expression:
Function STATFUNCTION2(rng As Range, op As String) As Double
STATFUNCTION2 = Evaluate(Op & "(" & _
rng.Address(external:=True) & ")")
End Function
For example, assume that the rng argument is C1:C12 and that the op argument is the string SUM. The expression that is used as an argument for the Evaluate method is this:
SUM(C1:C12)
The Evaluate method evaluates its argument and returns the result. In addition to being much shorter, a benefit of this version of STATFUNCTION is that it’s not necessary to list all the possible functions.
Note
Note that the Address property has an argument: external:=True. That argument controls the way the address is returned. The default value, FALSE, returns a simple range address. When the external argument is TRUE, the address includes the workbook name and worksheet name. This allows the function to use a range that’s on a different worksheet or even workbook.
Worksheet function data types
You may have noticed some differences in the data types used for functions and arguments so far. For instance, in STATFUNCTION, the variable rng was declared as a Variant, whereas the same variable was declared as a Range in STATFUNCTION2. Also, the former's return value was declared as a Variant, whereas the latter’s is a Double data type.
Data types are two-edged swords. They can be used to limit the type of data that can be passed to, or returned from, a function, but they can also reduce the flexibility of the function. Using Variant data types maximizes flexibility but may slow execution speed a bit.
One of the possible return values of STATFUNCTION is an error in the Case Else section of the Select Case statement. That means that the function can return a Double data type or an Error. The most restrictive data type that can hold both an Error and aDouble is a Variant (which can hold anything), so the function is typed as a Variant. On the other hand, STATFUNCTION2 does not have a provision continued for returning an error, so it’s typed as the more restrictive Double data type. Numeric data in cells is treated as a Double even if it looks like an Integer.
The rng arguments are also typed differently. In STATFUNCTION2, the Address property of the Range object is used. Because of this, you must pass a Range to the function, or it will return an error. However, there is nothing in STATFUNCTION that forces rng to be aRange. By declaring rng as a Variant, the user has the flexibility to provide inputs in other ways. Excel happily tries to convert whatever it’s given into something it can use. If it can’t convert it, the result is surely an error. A user can enter the following formula:
=STATFUNCTION({123.45,643,893.22},"Min")
Neither argument is a cell reference, but Excel doesn’t mind. It can find the minimum of an array constant as easily as a range of values. It works the other way, too, as in the case of the second argument. If a cell reference is supplied, Excel tries to convert it to a String and has no problem doing so.
In general, you should use the most restrictive data types possible for your situation while providing for the most user flexibility.
Generating Random Numbers
This section presents functions that deal with random numbers. One generates random numbers that don’t change. The other selects a cell at random from a range.
On the Web
The functions in this section are available at this book’s website. The filename is random functions.xlsm.
Generating random numbers that don’t change
You can use the Excel RAND function to quickly fill a range of cells with random values. But, as you may have discovered, the RAND function generates a new random number whenever the worksheet is recalculated. If you prefer to generate random numbers that don’t change with each recalculation, use the following STATICRAND Function procedure:
Function STATICRAND() As Double
' Returns a random number that doesn't
' change when recalculated
STATICRAND = Rnd
End Function
The STATICRAND function uses the VBA Rnd function, which, like Excel’s RAND function, returns a random number between 0 and 1. When you use STATICRAND, however, the random numbers don’t change when the sheet is calculated.
Note
Pressing F9 does not generate new values from the STATICRAND function, but pressing Ctrl+Alt+F9 (Excel’s “global recalc” key combination) does.
Following is another version of the function that returns a random integer within a specified range of values:
Function STATICRANDBETWEEN(lo As Long, hi As Long) As Long
' Returns a random integer that doesn't change when recalculated
STATICRANDBETWEEN = Int((hi – lo + 1) * Rnd + lo)
End Function
For example, if you want to generate a random integer between 1 and 1,000, you can use a formula such as this:
=STATICRANDBETWEEN(1,1000)
Controlling function recalculation
When you use a custom function in a worksheet formula, when is it recalculated?
Custom functions behave like Excel’s built-in worksheet functions. Normally, a custom function is recalculated only when it needs to be recalculated—that is, when you modify any of a function’s arguments—but you can force functions to recalculate more frequently. Adding the following statement to a Function procedure makes the function recalculate whenever the workbook is recalculated:
Application.Volatile True
The Volatile method of the Application object has one argument (either True or False). Marking a Function procedure as “volatile” forces the function to be calculated whenever calculation occurs in any cell in the worksheet.
For example, the custom STATICRAND function presented in this chapter can be changed to emulate the Excel RAND() function by using the Volatile method, as follows:
Function NONSTATICRAND()
' Returns a random number that
' changes when the sheet is recalculated
Application.Volatile True
NONSTATICRAND = Rnd
End Function
Using the False argument of the Volatile method causes the function to be recalculated only when one or more of its arguments change. (If a function has no arguments, this method has no effect.) By default, all custom functions work as if they include an Application.Volatile False statement.
Selecting a cell at random
The following function, named DRAWONE, randomly chooses one cell from an input range and returns the cell’s contents:
Function DRAWONE(rng As Variant) As Double
' Chooses one cell at random from a range
DRAWONE = rng(Int((rng.Count) * Rnd + 1))
End Function
If you use this function, you’ll find that it is not recalculated when the worksheet is calculated. In other words, the function is not volatile. (For more information about controlling recalculation, see the previous sidebar, “Controlling function recalculation.” You can make the function volatile by adding the following statement:
Application.Volatile True
After doing so, the DRAWONE function displays a new random cell value whenever the sheet is calculated.
A more general function, one that accepts array constants as well as ranges, is shown here:
Function DRAWONE2(rng As Variant) As Variant
' Chooses one value at random from an array
Dim ArrayLen As Long
If TypeName(rng) = "Range" Then
DRAWONE2 = rng(Int((rng.Count) * Rnd + 1)).Value
Else
ArrayLen = UBound(rng) – LBound(rng) + 1
DRAWONE2 = rng(Int(ArrayLen * Rnd + 1))
End If
End Function
This function uses the VBA built-in TypeName function to determine whether the argument passed is a Range. If not, it’s assumed to be an array. Following is a formula that uses the DRAWONE2 function. This formula returns a text string that corresponds to a suit in a deck of cards:
=DRAWONE2({"Clubs","Hearts","Diamonds","Spades"})
Following is a formula that has the same result, written using Excel’s built-in functions:
=CHOOSE(RANDBETWEEN(1,4),"Clubs","Hearts","Diamonds","Spades")
We present two additional functions that deal with randomization later in this chapter (see the “Advanced Function Techniques” section).
Calculating Sales Commissions
Sales managers often need to calculate the commissions earned by their sales forces. The calculations in the function example presented here are based on a sliding scale: employees who sell more earn a higher commission rate (see Table 26.1). For example, a salesperson with sales between $10,000 and $19,999 qualifies for a commission rate of 10.5%.
Table 26.1 Commission Rates for Monthly Sales
Monthly Sales |
Commission Rate |
Less than $10,000 |
8.0% |
$10,000 to $19,999 |
10.5% |
$20,000 to $39,999 |
12.0% |
$40,000 or more |
14.0% |
You can calculate commissions for various sales amounts entered into a worksheet in several ways. You can use a complex formula with nested IF functions, such as the following:
=IF(A1<0,0,IF(A1<10000,A1*0.08,IF(A1<20000,A1*0.105,
IF(A1<40000,A1*0.12,A1*0.14))))
This may not be the best approach for a couple of reasons. First, the formula is overly complex, thus making it difficult to understand. Second, the values are hard-coded into the formula, thus making the formula difficult to modify.
A better approach is to use a lookup table function to compute the commissions. For example:
=VLOOKUP(A1,Table,2)*A1
Using VLOOKUP is a good alternative, but it may not work if the commission structure is more complex. (See the “A function for a simple commission structure” section for more information.) Yet another approach is to create a custom function.
A function for a simple commission structure
The following COMMISSION function accepts a single argument (sales) and computes the commission amount:
Function COMMISSION(Sales As Double) As Double
' Calculates sales commissions
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 0.14
Select Case Sales
Case Is >= 40000
COMMISSION = Sales * Tier4
Case Is >= 20000
COMMISSION = Sales * Tier3
Case Is >= 10000
COMMISSION = Sales * Tier2
Case Is < 10000
COMMISSION = Sales * Tier1
End Select
End Function
The following worksheet formula, for example, returns 3,000. (The sales amount—25,000—qualifies for a commission rate of 12%.)
=COMMISSION(25000)
This function is easy to understand and maintain. It uses constants to store the commission rates as well as a Select Case structure to determine which commission rate to use.
Note
When a Select Case structure is evaluated, program control exits the Select Case structure when the first true Case is encountered.
A function for a more complex commission structure
If the commission structure is more complex, you may need to use additional arguments for your COMMISSION function. Imagine that the aforementioned sales manager implements a new policy to help reduce turnover: the total commission paid increases by 1 percent for each year that a salesperson stays with the company.
The following is a modified COMMISSION function (named COMMISSION2). This function now takes two arguments: the monthly sales (sales) and the number of years employed (years):
Function COMMISSION2(Sales As Double, Years As Long) As Double
' Calculates sales commissions based on
' years in service
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 0.14
Select Case Sales
Case Is >= 40000
COMMISSION2 = Sales * Tier4
Case Is >= 20000
COMMISSION2 = Sales * Tier3
Case Is >= 10000
COMMISSION2 = Sales * Tier2
Case Is < 10000
COMMISSION2 = Sales * Tier1
End Select
COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100)
End Function
Figure 26.3 shows the COMMISSION2 function in use. Here’s the formula in cell D2: =COMMISSION2(B2,C2)
Figure 26.3 Calculating sales commissions based on sales amount and years employed.
The workbook, commission function.xlsm, shown in Figure 26.3, is available at this book’s website.
Text Manipulation Functions
Text strings can be manipulated with functions in a variety of ways, including reversing the display of a text string, scrambling the characters in a text string, or extracting specific characters from a text string. This section offers a number of function examples that manipulate text strings.
On the Web
This book’s website contains a workbook named text manipulation functions.xlsm that demonstrates all the functions in this section.
Reversing a string
The following REVERSETEXT function returns the text in a cell backward:
Function REVERSETEXT(text As String) As String
' Returns its argument, reversed
REVERSETEXT = StrReverse(text)
End Function
This function simply uses the VBA StrReverse function. The following formula, for example, returns tfosorciM:
=REVERSETEXT("Microsoft")
Scrambling text
The following function returns the contents of its argument with the characters randomized. For example, using Microsoft as the argument may return oficMorts or some other random permutation:
Function SCRAMBLE(text As Variant) As String
' Scrambles its string argument
Dim TextLen As Long
Dim i As Long
Dim RandPos As Long
Dim Temp As String
Dim Char As String * 1
If TypeName(text) = "Range" Then
Temp = text.Range("A1").text
ElseIf IsArray(text) Then
Temp = text(LBound(text))
Else
Temp = text
End If
TextLen = Len(Temp)
For i = 1 To TextLen
Char = Mid(Temp, i, 1)
RandPos = WorksheetFunction.RandBetween(1, TextLen)
Mid(Temp, i, 1) = Mid(Temp, RandPos, 1)
Mid(Temp, RandPos, 1) = Char
Next i
SCRAMBLE = Temp
End Function
This function loops through each character and then swaps it with another character in a randomly selected position.
You may be wondering about the use of Mid. Note that when Mid is used on the right side of an assignment statement, it is a function. However, when Mid is used on the left side of the assignment statement, it is a statement. Consult the Help system for more information about Mid.
Returning an acronym
The ACRONYM function returns the first letter (in uppercase) of each word in its argument. For example, the following formula returns IBM:
=ACRONYM("International Business Machines")
The listing for the ACRONYM Function procedure follows:
Function ACRONYM(text As String) As String
' Returns an acronym for text
Dim TextLen As Long
Dim i As Long
text = Application.Trim(text)
TextLen = Len(text)
ACRONYM = Left(text, 1)
For i = 2 To TextLen
If Mid(text, i, 1) = " " Then
ACRONYM = ACRONYM & Mid(text, i + 1, 1)
End If
Next i
ACRONYM = UCase(ACRONYM)
End Function
This function uses the Excel TRIM function to remove any extra spaces from the argument. The first character in the argument is always the first character in the result. The For-Next loop examines each character. If the character is a space, the character after the space is appended to the result. Finally, the result converts to uppercase by using the VBA UCase function.
Does the text match a pattern?
The following function returns TRUE if a string matches a pattern composed of text and wildcard characters. The ISLIKE function is remarkably simple and is essentially a wrapper for the useful VBA Like operator:
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
The supported wildcard characters are as follows:
? |
Matches any single character |
* |
Matches zero or more characters |
# |
Matches any single digit (0–9) |
[list] |
Matches any single character in the list |
[!list] |
Matches any single character not in the list |
The following formula returns TRUE because the question mark (?) matches any single character. If the first argument were “Unit12”, the function would return FALSE:
=ISLIKE("Unit1","Unit?")
The function also works with values. The following formula, for example, returns TRUE if cell A1 contains a value that begins with 1 and has exactly three numeric digits:
=ISLIKE(A1,"1##")
The following formula returns TRUE because the first argument is a single character contained in the list of characters specified in the second argument:
=ISLIKE("a","[aeiou]")
If the character list begins with an exclamation point (!), the comparison is made with characters not in the list. For example, the following formula returns TRUE because the first argument is a single character that does not appear in the second argument’s list:
=ISLIKE("g","[!aeiou]")
To match one of the special characters from the previous table, put that character in brackets. This formula returns TRUE because the pattern is looking for three consecutive question marks. The question marks in the pattern are in brackets, so they no longer represent a single character:
=ISLIKE("???","[?][?][?]")
The Like operator is versatile. For complete information about the VBA Like operator, consult the Help system.
Does a cell contain a particular word?
What if you need to determine whether a particular word is contained in a string? Excel’s FIND function can determine whether a text string is contained in another text string. For example, the formula that follows returns 5, the character position of rate in the string The rate has changed:
=FIND("rate","The rate has changed")
The following formula also returns 5:
=FIND("rat","The rate has changed")
However, Excel provides no way to determine whether a particular word is contained in a string. Here’s a VBA function that returns TRUE if the second argument is contained in the first argument:
Function EXACTWORDINSTRING(Text As String, Word As String) As Boolean
EXACTWORDINSTRING = " " & UCase(Text) & _
" " Like "*[!A–Z]" & UCase(Word) & "[!A–Z]*"
End Function
Figure 26.4 shows this function in use. Column A contains the text used as the first argument, and column B contains the text used as the second argument. Cell C1 contains this formula, which was copied down the column: =EXACTWORDINSTRING(A1,B1)
Figure 26.4 A VBA function that determines whether a particular word is contained in a string.
Note
Thanks to Rick Rothstein for suggesting this function, which is much more efficient than our original function.
On the Web
A workbook that demonstrates the EXACTWORDINSTRING function is available on this book’s website. The filename is exact word.xlsm.
Does a cell contain text?
A number of Excel’s worksheet functions are at times unreliable when dealing with text in a cell. For example, the ISTEXT function returns FALSE if its argument is a number that’s formatted as Text. The following CELLHASTEXT function returns TRUE if the range argument contains text or contains a value formatted as Text:
Function CELLHASTEXT(cell As Range) As Boolean
' Returns TRUE if cell contains a string
' or cell is formatted as Text
Dim UpperLeft as Range
CELLHASTEXT = False
Set UpperLeft = cell.Range("A1")
If UpperLeft.NumberFormat = "@" Then
CELLHASTEXT = True
Exit Function
End If
If Not IsNumeric(UpperLeft.Value) Then
CELLHASTEXT = True
Exit Function
End If
End Function
The following formula returns TRUE if cell A1 contains a text string or if the cell is formatted as Text:
=CELLHASTEXT(A1)
Extracting the nth element from a string
The EXTRACTELEMENT function is a custom worksheet function that extracts an element from a text string based on a specified separator character. Assume that cell A1 contains the following text:
123-456-789-9133-8844
For example, the following formula returns the string 9133, which is the fourth element in the string. The string uses a hyphen (-) as the separator:
=EXTRACTELEMENT(A1,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 As String, n As Long,
Separator As String) As String
' Returns the <i>n</i>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 the VBA Split function, which returns a variant array that contains each element of the text string. This array begins with 0 (not 1), so using n–1 references the desired element.
Spelling out a number
The SPELLDOLLARS function returns a number spelled out in text—as on a check. For example, the following formula returns the string One hundred twenty-three and 45/100 dollars:
=SPELLDOLLARS(123.45)
Figure 26.5 shows some additional examples of the SPELLDOLLARS function. Column C contains formulas that use the function. For example, the formula in C1 is
Figure 26.5 Examples of the SPELLDOLLARS function.
=SPELLDOLLARS(A1)
Note that negative numbers are spelled out and enclosed in parentheses.
On the Web
The SPELLDOLLARS function is too lengthy to list here, but you can view the complete listing in spelldollars function.xlsm at this book’s website.
Counting Functions
Chapter 7, “Counting and Summing Techniques,” contains many formula examples to count cells based on various criteria. If you can’t arrive at a formula-based solution for a counting problem, you can probably create a custom function. This section contains three functions that perform counting.
On the Web
This book’s website contains the workbook counting functions.xlsm that demonstrates the functions in this section.
Counting pattern-matched cells
The COUNTIF function accepts limited wildcard characters in its criteria: the question mark and the asterisk, to be specific. If you need more robust pattern matching, you can use the LIKE operator in a custom function:
Function COUNTLIKE(rng As Range, pattern As String) As Long
' Count the cells in a range that match a pattern
Dim cell As Range
Dim cnt As Long
For Each cell In rng.Cells
If cell.Text Like pattern Then cnt = cnt + 1
Next cell
COUNTLIKE = cnt
End Function
The following formula counts the number of cells in B4:B11 that contain the letter e:
=COUNTLIKE(B4:B11,"*[eE]*")
Counting sheets in a workbook
The following countsheets function accepts no arguments and returns the number of sheets in the workbook from where it’s called:
Function COUNTSHEETS() As Long
COUNTSHEETS = Application.Caller.Parent.Parent.Sheets.Count
End Function
This function uses Application.Caller to get the range where the formula was entered. Then it uses two Parent properties to go to the sheet and the workbook. Once at the workbook level, the Count property of the Sheets property is returned. The count includes worksheets and chart sheets.
Counting words in a range
The WORDCOUNT function accepts a range argument and returns the number of words in that range:
Function WORDCOUNT(rng As Range) As Long
' Count the words in a range of cells
Dim cell As Range
Dim WdCnt As Long
Dim tmp As String
For Each cell In rng.Cells
tmp = Application.Trim(cell.Value)
If WorksheetFunction.IsText(tmp) Then
WdCnt = WdCnt + (Len(tmp) – _
Len(Replace(tmp, " ", "")) + 1)
End If
Next cell
WORDCOUNT = WdCnt
End Function
We use a variable, tmp, to store the cell contents with extra spaces removed. Looping through the cells in the supplied range, the ISTEXT worksheet function is used to determine whether the cell has text. If it does, the number of spaces are counted and added to the total. Then one more space is added because a sentence with three spaces has four words. Spaces are counted by comparing the length of the text string with the length after the spaces have been removed with the VBA Replace function.
Date Functions
Chapter 6, “Working with Dates and Times,” presents a number of useful Excel functions and formulas for calculating dates, times, and time periods by manipulating date and time serial values. This section presents additional functions that deal with dates.
On the Web
This book’s website contains a workbook, date functions.xlsm, that demonstrates the functions presented in this section.
Calculating the next Monday
The following NEXTMONDAY function accepts a date argument and returns the date of the following Monday:
Function NEXTMONDAY(d As Date) As Date
NEXTMONDAY = d + 8 – WeekDay(d, vbMonday)
End Function
This function uses the VBA WeekDay function, which returns an integer that represents the day of the week for a date (1 = Sunday, 2 = Monday, and so on). It also uses a predefined constant, vbMonday.
The following formula returns 12/28/2015, which is the first Monday after Christmas Day, 2015 (which is a Friday):
=NEXTMONDAY(DATE(2015,12,25))
Note
The function returns a date serial number. You need to change the number format of the cell to display this serial number as an actual date.
If the argument passed to the NEXTMONDAY function is a Monday, the function returns the following Monday. If you prefer the function to return the same Monday, use this modified version:
Function NEXTMONDAY2(d As Date) As Date
If WeekDay(d) = vbMonday Then
NEXTMONDAY2 = d
Else
NEXTMONDAY2 = d + 8 – WeekDay(d, vbMonday)
End If
End Function
Calculating the next day of the week
The following NEXTDAY function is a variation on the NEXTMONDAY function. This function accepts two arguments: a date and an integer between 1 and 7 that represents a day of the week (1 = Sunday, 2 = Monday, and so on). The NEXTDAY function returns the date for the next specified day of the week:
Function NEXTDAY(d As Date, day As Integer) As Variant
' Returns the next specified day
' Make sure day is between 1 and 7
If day < 1 Or day > 7 Then
NEXTDAY = CVErr(xlErrNA)
Else
NEXTDAY = d + 8 – WeekDay(d, day)
End If
End Function
The NEXTDAY function uses an If statement to ensure that the day argument is valid (that is, between 1 and 7). If the day argument is not valid, the function returns #N/A. Because the function can return a value other than a date, it is declared as type Variant.
Which week of the month?
The following MONTHWEEK function returns an integer that corresponds to the week of the month for a date:
Function MONTHWEEK(d As Date) As Variant
' Returns the week of the month for a date
Dim FirstDay As Integer
' Check for valid date argument
If Not IsDate(d) Then
MONTHWEEK = CVErr(xlErrNA)
Exit Function
End If
' Get first day of the month
FirstDay = WeekDay(DateSerial(Year(d), Month(d), 1))
' Calculate the week number
MONTHWEEK = Application.RoundUp((FirstDay + day(d) – 1) / 7, 0)
End Function
Working with dates before 1900
Many users are surprised to discover that Excel can’t work with dates prior to the year 1900. To correct this deficiency, we created a series of extended date functions. These functions enable you to work with dates in the years 0100 through 9999.
The extended date functions follow:
§ XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.
§ XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.
§ XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.
§ XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).
§ XDATEYEAR(xdate1): Returns the year of a date.
§ XDATEMONTH(xdate1): Returns the month of a date.
§ XDATEDAY(xdate1): Returns the day of a date.
§ XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).
Figure 26.6 shows a workbook that uses a few of these functions.
Figure 26.6 Examples of the extended date function.
On the Web
These functions are available on this book’s website, in a file named extended date functions.xlsm. The website also contains a PDF file (extended date functions help.pdf) that describes these functions. The functions are assigned to the Date & Time function category.
Caution
The extended date functions don’t make adjustments for changes made to the calendar in 1582. Consequently, working with dates prior to October 15, 1582, may not yield correct results.
Returning the Last Nonempty Cell in a Column or Row
This section presents two useful functions: LASTINCOLUMN, which returns the contents of the last nonempty cell in a column, and LASTINROW, which returns the contents of the last nonempty cell in a row. Chapter 15, “Performing Magic with Array Formulas,” presents standard formulas for this task, but you may prefer to use a custom function.
On the Web
This book’s website contains last nonempty cell.xlsm, a workbook that demonstrates the functions presented in this section.
Each of these functions accepts a range as its single argument. The range argument can be a column reference (for LASTINCOLUMN) or a row reference (for LASTINROW). If the supplied argument is not a complete column or row reference (such as 3:3 or D:D), the function uses the column or row of the upper-left cell in the range. For example, the following formula returns the contents of the last nonempty cell in column B:
=LASTINCOLUMN(B5)
The following formula returns the contents of the last nonempty cell in row 7:
=LASTINROW(C7:D9)
The LASTINCOLUMN function
The following is the LASTINCOLUMN function:
Function LASTINCOLUMN(rng As Range) As Variant
' Returns the contents of the last nonempty cell in a column
Dim LastCell As Range
With rng.Parent
With .Cells(.Rows.Count, rng.Column)
If Not IsEmpty(.Value) Then
LASTINCOLUMN = .Value
ElseIf IsEmpty(.End(xlUp).Value) Then
LASTINCOLUMN = ""
Else
LASTINCOLUMN = .End(xlUp).Value
End If
End With
End With
End Function
Notice the references to the Parent of the range. This is done to make the function work with arguments that refer to a different worksheet or workbook.
The LASTINROW function
The following is the LASTINROW function:
Function LASTINROW(rng As Range) As Variant
' Returns the contents of the last nonempty cell in a row
With rng.Parent
With .Cells(rng.Row, .Columns.Count)
If Not IsEmpty(.Value) Then
LASTINROW = .Value
ElseIf IsEmpty(.End(xlToLeft).Value) Then
LASTINROW = ""
Else
LASTINROW = .End(xlToLeft).Value
End If
End With
End With
End Function
Cross-Ref
In Chapter 15, we describe array formulas that return the last cell in a column or row.
Multisheet Functions
You may need to create a function that works with data contained in more than one worksheet within a workbook. This section contains two VBA custom functions that enable you to work with data across multiple sheets, including a function that overcomes an Excel limitation when copying formulas to other sheets.
On the Web
This book’s website contains the workbook multisheet functions.xlsm that demonstrates the multisheet functions presented in this section.
Returning the maximum value across all worksheets
If you need to determine the maximum value in a cell (for example, B1) across a number of worksheets, use a formula like this one:
=MAX(Sheet1:Sheet4!B1)
This formula returns the maximum value in cell B1 for Sheet1, Sheet4, and all sheets in between. But what if you add a new sheet (Sheet5) after Sheet4? Your formula does not adjust automatically, so you need to edit it to include the new sheet reference:
=MAX(Sheet1:Sheet5!B1)
The following function accepts a single-cell argument and returns the maximum value in that cell across all worksheets in the workbook. For example, the following formula returns the maximum value in cell B1 for all sheets in the workbook:
=MAXALLSHEETS(B1)
If you add a new sheet, you don't need to edit the formula:
Function MAXALLSHEETS(cell as Range) As Variant
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 Not Wksht.Name = cell.Parent.Name Or _
Not Addr = Application.Caller.Address Then
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 = CVErr(xlErrValue)
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 checks 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
You can easily modify the MAXALLSHEETS function to perform other cross-worksheet calculations: Minimum, Average, Sum, and so on.
The SHEETOFFSET function
A recurring complaint about Excel (including Excel 2016) is its poor support for relative sheet references. For example, suppose that you have a multisheet workbook, and you enter a formula like the following on Sheet2:
=Sheet1!A1+1
This formula works fine. However, if you copy the formula to the next sheet (Sheet3), the formula continues to refer to Sheet1. Or if you insert a sheet between Sheet1 and Sheet2, the formula continues to refer to Sheet1, when most likely, you want it to refer to the newly inserted sheet. In fact, you can’t create formulas that refer to worksheets in a relative manner. However, you can use the SHEETOFFSET function to overcome this limitation.
Following is a VBA Function procedure named SHEETOFFSET:
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)_</p><p>
.Range(cell(1).Address).Value
Exit Function
Else
WksNum = WksNum + 1
End If
Next wks
End Function
The SHEETOFFSET function accepts two arguments:
§ offset: The sheet offset, which can be positive, negative, or 0.
§ cell: (Optional) A single-cell reference. If this argument is omitted, the function uses the same cell reference as the cell that contains the formula.
For more information about optional arguments, see the section “Using optional arguments,” later in this chapter.
The following formula returns the value in cell A1 of the sheet before the sheet that contains the formula:
=SHEETOFFSET(–1,A1)
The following formula returns the value in cell A1 of the sheet after the sheet that contains the formula:
=SHEETOFFSET(1,A1)
Advanced Function Techniques
In this section, we explore some even more advanced functions. The examples in this section demonstrate some special techniques that you can use with your custom functions.
Returning an error value
In some cases, you may want your custom function to return a particular error value. Consider the simple REVERSETEXT function, which we presented earlier in this chapter:
Function REVERSETEXT(text As String) As String
' Returns its argument, reversed
REVERSETEXT = StrReverse(text)
End Function
This function reverses the contents of its single-cell argument (which can be text or a value). If the argument is a multicell range, the function returns #VALUE!
Assume that you want this function to work only with strings. If the argument does not contain a string, you want the function to return an error value (#N/A). You may be tempted to simply assign a string that looks like an Excel formula error value. For example:
REVERSETEXT = "#N/A"
Although the string looks like an error value, it is not treated as such by other formulas that may reference it. To return a real error value from a function, use the VBA CVErr function, which converts an error number to a real error.
Fortunately, VBA has built-in constants for the errors that you want to return from a custom function. These constants are listed here:
§ xlErrDiv0
§ xlErrNA
§ xlErrName
§ xlErrNull
§ xlErrNum
§ xlErrRef
§ xlErrValue
The following is the revised REVERSETEXT function:
Function REVERSETEXT(text As Variant) As Variant
' Returns its argument, reversed
If WorksheetFunction.ISNONTEXT(text) Then
REVERSETEXT = CVErr(xlErrNA)
Else
REVERSETEXT = StrReverse(text)
End If
End Function
First, change the argument from a String data type to a Variant. If the argument’s data type is String, Excel tries to convert whatever it gets (for example, number, Boolean value) to a String and usually succeeds. Next, the Excel ISNONTEXT function is used to determine whether the argument is not a text string. If the argument is not a text string, the function returns the #N/A error. Otherwise, it returns the characters in reverse order.
Note
The data type for the return value of the original REVERSETEXT function was String because the function always returned a text string. In this revised version, the function is declared as a Variant because it can now return something other than a string.
Returning an array from a function
Most functions that you develop with VBA return a single value. It’s possible, however, to write a function that returns multiple values in an array.
Cross-Ref
Part IV, “Array Formulas,” deals with arrays and array formulas. Specifically, these chapters provide examples of a single formula that returns multiple values in separate cells. As you’ll see, you can also create custom functions that return arrays.
VBA includes a useful function called Array. The Array function returns a variant that contains an array. It’s important to understand that the array returned is not the same as a normal array composed of elements of the variant type. In other words, a variant array is not the same as an array of variants.
If you’re familiar with using array formulas in Excel, you have a head start understanding the VBA Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts brackets around the formula to indicate that it’s an array formula. See Chapter 14, “Introducing Arrays,” and Chapter 15 for more details on array formulas.
Note
The lower bound of an array created by using the Array function is, by default, 0. However, the lower bound can be changed if you use an Option Base statement.
The following MONTHNAMES function demonstrates how to return an array from a Function procedure:
Function MONTHNAMES() As Variant
MONTHNAMES = Array( _
"Jan", "Feb", "Mar", "Apr", _
"May", "Jun", "Jul", "Aug", _
"Sep", "Oct", "Nov", "Dec")
End Function
Figure 26.7 shows a worksheet that uses the MONTHNAMES function. You enter the function by selecting A4:L4 and then entering the following formula:
{=MONTHNAMES()}
Figure 26.7 The MONTHNAMES function entered as an array formula.
Note
As with any array formula, you must press Ctrl+Shift+Enter to enter the formula. Don’t enter the brackets—Excel inserts the brackets for you.
The MONTHNAMES function, as written, returns a horizontal array in a single row. To display the array in a vertical range in a single column, select the range and enter the following formula:
{=TRANSPOSE(MONTHNAMES())}
Alternatively, you can modify the function to do the transposition. The following function uses the Excel TRANSPOSE function to return a vertical array:
Function VMONTHNAMES() As Variant
VMONTHNAMES = Application.Transpose(Array( _
"Jan", "Feb", "Mar", "Apr", _
"May", "Jun", "Jul", "Aug", _
"Sep", "Oct", "Nov", "Dec"))
End Function
On the Web
The workbook monthnames.xlsm that demonstrates MONTHNAMES and VMONTHNAMES is available at this book’s website.
Returning an array of nonduplicated random integers
The RANDOMINTEGERS function returns an array of nonduplicated integers. This function is intended for use in a multicell array formula. Figure 26.8 shows a worksheet that uses the following formula in the range A3:D12:
{=RANDOMINTEGERS()}
Figure 26.8 An array formula generates nonduplicated consecutive integers, arranged randomly.
This formula was entered into the entire range by using Ctrl+Shift+Enter. The formula returns an array of nonduplicated integers, arranged randomly. Because 40 cells contain the formula, the integers range from 1 to 40. The following is the code for RANDOMINTEGERS:
Function RANDOMINTEGERS()
Dim FuncRange As Range
Dim V() As Integer, ValArray() As Integer
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
Randomize
' 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
On the Web
The workbook random integers function.xlsm containing the RANDOMINTEGERS function is available at this book’s website.
Randomizing a range
The following RANGERANDOMIZE function 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
Randomize
' 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 closely resembles the code for the RANDOMINTEGERS function. Figure 26.9 shows the function in use. The following array formula, which is in E15:F27, returns the contents of B15:C27 in a random order:
{=RANGERANDOMIZE(B15:C27)}
Figure 26.9 The RANGERANDOMIZE function returns the contents of a range, but in a randomized order.
On the Web
The workbook range randomize function.xlsm, which contains the RANGERANDOMIZE function, is available at this book’s website.
Using optional arguments
Many of the built-in Excel worksheet functions use optional arguments. For example, the LEFT function returns characters from the left side of a string. Its official syntax is as follows:
LEFT(text,<i>num_chars</i>)
The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1.
Custom functions that you develop in VBA can also have optional arguments. You specify an optional argument by preceding the argument’s name with the keyword Optional. The following is a simple function that returns the user’s name:
Function USER()
USER = Application.UserName
End Function
Suppose that in some cases, you want the user’s name to be returned in uppercase letters. The following function uses an optional argument:
Function USER(Optional UpperCase As Variant) As String
If IsMissing(UpperCase) Then UpperCase = False
If UpperCase = True Then
USER = Ucase(Application.UserName)
Else
USER = Application.UserName
End If
End Function
Note
If you need to determine whether an optional argument was passed to a function, you must declare the optional argument as a variant data type. Then you can use the IsMissing function within the procedure, as demonstrated in this example.
If the argument is FALSE or omitted, the user’s name is returned without changes. If the argument is TRUE, the user’s name converts to uppercase (using the VBA Ucase function) before it is returned. Notice that the first statement in the procedure uses the VBA IsMissing function to determine whether the argument was supplied. If the argument is missing, the statement sets the UpperCase variable to FALSE (the default value).
Optional arguments also allow you to specify a default value in the declaration, rather than testing it with the IsMissing function. The preceding function can be rewritten in this alternate syntax as follows:
Function USER(Optional UpperCase As Boolean = False) As String
If UpperCase = True Then
USER = UCase(Application.UserName)
Else
USER = Application.UserName
End If
End Function
If no argument is supplied, UpperCase is automatically assigned a value of FALSE. This allows you to type the argument appropriately instead of with the generic Variant data type. If you use this method, however, there is no way to tell whether the user omitted the argument or supplied the default argument. Also, the argument will be tagged as optional in the Insert Function dialog.
All the following formulas are valid in either syntax (and the first two have the same effect):
=USER()
=USER(False)
=USER(True)
Using an indefinite number of arguments
Some of the Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:
SUM(number1,number2…)
The first argument is required, but you can have as many as 254 additional arguments. Here’s an example of a formula that uses the SUM function with four range arguments:
=SUM(A1:A5,C1:C5,E1:E5,G1:G5)
You can mix and match the argument types. For example, the following example uses three arguments—a range, followed by a value, and finally an expression:
=SUM(A1:A5,12,24*3)
You can create Function procedures that have an indefinite number of arguments. The trick is to use an array as the last (or only) argument, preceded by the keyword ParamArray.
Note
ParamArray can apply only to the last argument in the procedure. It is always a variant data type, and it is always an optional argument (although you don’t use the Optional keyword).
A simple example of arguments
The following is a Function procedure that can have any number of single-value arguments. It simply returns the sum of the arguments:
Function SIMPLESUM(ParamArray arglist() As Variant) As Double
Dim arg as Variant
For Each arg In arglist
SIMPLESUM = SIMPLESUM + arg
Next arg
End Function
The following formula returns the sum of the single-cell arguments:
=SIMPLESUM(A1,A5,12)
The most serious limitation of the SIMPLESUM function is that it does not handle multicell ranges. This improved version does:
Function SIMPLESUM(ParamArray arglist() As Variant) As Double
Dim arg as Variant
Dim cell as Range
For Each arg In arglist
If TypeName(arg) = "Range" Then
For Each cell In arg
SIMPLESUM = SIMPLESUM + cell.Value
Next cell
Else
SIMPLESUM = SIMPLESUM + arg
End If
Next arg
End Function
This function checks each entry in the Arglist array. If the entry is a range, the code uses a For Each-Next loop to sum the cells in the range.
Even this improved version is certainly no substitute for the Excel SUM function. Try it by using various types of arguments, and you’ll see that it fails unless each argument is a value or a range reference. Also, if an argument consists of an entire column, you’ll find that the function is very slow because it evaluates every cell—even the empty ones.
Emulating the Excel SUM function
This section presents a Function procedure called MYSUM. Unlike the SIMPLESUM function listed in the previous section, MYSUM emulates the Excel SUM function perfectly.
Before you look at the code for the MYSUM function, take a minute to think about the Excel SUM function. This versatile function can have any number of arguments (even missing arguments), and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded functions. For example, consider the following formula:
=SUM(A1,5,"6",,TRUE,SQRT(4),B1:B5,{1,3,5})
This formula—which is valid—contains all the following types of arguments, listed here in the order of their presentation:
§ A single cell reference (A1)
§ A literal value (5)
§ A string that looks like a value (“6”)
§ A missing argument
§ A logical value (TRUE)
§ An expression that uses another function (SQRT)
§ A range reference (B1:B5)
§ An array ({1,3,5})
The following is the listing for the MYSUM function that handles all these argument types:
Function MySum(ParamArray args() As Variant) As Variant
' Emulates Excel's SUM function
' Variable declarations
Dim i As Variant
Dim TempRange As Range, cell As Range
Dim ECode As String
Dim m, n
MySum = 0
' Process each argument
For i = 0 To UBound(args)
' Skip missing arguments
If Not IsMissing(args(i)) Then
' What type of argument is it?
Select Case TypeName(args(i))
Case "Range"
' Create temp range to handle full row or column ranges
Set TempRange = Intersect(args(i).Parent.UsedRange, args(i))
For Each cell In TempRange
If IsError(cell) Then
MySum = cell ' return the error
Exit Function
End If
If cell = True Or cell = False Then
MySum = MySum + 0
Else
If IsNumeric(cell) Or IsDate(cell) Then _
MySum = MySum + cell
End If
Next cell
Case "Variant()"
n = args(i)
For m = LBound(n) To UBound(n)
MySum = MySum(MySum, n(m)) 'recursive call
Next m
Case "Null" 'ignore it
Case "Error" 'return the error
MySum = args(i)
Exit Function
Case "Boolean"
' Check for literal TRUE and compensate
If args(i) = "True" Then MySum = MySum + 1
Case "Date"
MySum = MySum + args(i)
Case Else
MySum = MySum + args(i)
End Select
End If
Next i
End Function
On the Web
The workbook sum function emulation.xlsm containing the MYSUM function is available at this book’s website.
Figure 26.10 shows a workbook with various formulas that use SUM (column E) and MYSUM (column G). As you can see, the functions return identical results.
Figure 26.10 Comparing Excel’s SUM function with a custom function.
MYSUM is a close emulation of the SUM function, but it’s not perfect. It cannot handle operations on arrays. For example, this array formula returns the sum of the squared values in range A1:A4:
{=SUM(A:A4^2)}
This formula returns a #VALUE! error:
{=MYSUM(A1:A4^2)}
As you study the code for MYSUM, keep the following points in mind:
§ Missing arguments (determined by the IsMissing function) are simply ignored.
§ The procedure uses the VBA TypeName function to determine the type of argument (Range, Error, or something else). Each argument type is handled differently.
§ For a range argument, the function loops through each cell in the range and adds its value to a running total.
§ The data type for the function is Variant because the function needs to return an error if any of its arguments is an error value.
§ If an argument contains an error (for example, #DIV0!), the MYSUM function simply returns the error—just like the Excel SUM function.
§ The Excel SUM function considers a text string to have a value of 0 unless it appears as a literal argument (that is, as an actual value, not a variable). Therefore, MYSUM adds the cell’s value only if it can be evaluated as a number (VBA’s IsNumeric function is used for this).
§ Dealing with Boolean arguments is tricky. For MYSUM to emulate SUM exactly, it needs to test for a literal TRUE in the argument list and compensate for the difference (that is, add 2 to –1 to get 1).
§ For range arguments, the function uses the Intersect method to create a temporary range that consists of the intersection of the range and the sheet’s used range. This handles cases in which a range argument consists of a complete row or column, which would take forever to evaluate.
You may be curious about the relative speeds of SUM and MYSUM. MYSUM, of course, is much slower, but just how much slower depends on the speed of your system and the formulas themselves. On our system, a worksheet with 5,000 SUM formulas recalculated instantly. After we replaced the SUM functions with MYSUM functions, it took about 8 seconds. MYSUM may be improved a bit, but it can never come close to SUM’s speed.
By the way, we hope you understand that the point of this example is not to create a new SUM function. Rather, it demonstrates how to create custom worksheet functions that look and work like those built into Excel.