In This Chapter
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.
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.
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
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.
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
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
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
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).
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
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.
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:
=CELLTYPE(A1)
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:
=STATFUNCTION(C1:C12,B14)
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.
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.
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.
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)
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).
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.
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.
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)
The workbook, commission function.xlsm, shown in Figure 26.3, is available at this book’s website.
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.
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")
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.
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.
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.
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)
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)
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:
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.
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
=SPELLDOLLARS(A1)
Note that negative numbers are spelled out and enclosed in parentheses.
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.
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]*")
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.
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.
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.
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))
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
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.
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
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:
Figure 26.6 shows a workbook that uses a few of these functions.
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.
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 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 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
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.
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.
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:
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)
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.
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:
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.
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.
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.
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()}
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
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()}
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
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)}
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
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)
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.
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.
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:
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
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.
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:
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.
18.119.131.235