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 wide 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 useful information that's not normally available for use in a formula. For example, your VBA code can access a cell's HasFormula property to determine whether a cell contains a formula. Oddly, Excel does not have a built-in worksheet function that tells you this.

This book's website contains the workbook simple functions.xlsm that includes all the functions in this section.

Does a cell contain a formula?

The following CELLHASFORMULA function accepts a single-cell argument and returns TRUE if the cell has a formula:

Function CELLHASFORMULA(cell As Range) As Boolean

‘   Returns TRUE if cell has a formula

    CELLHASFORMULA = cell.Range(“A1”).HasFormula

End Function

If a multicell range argument is passed to the function, the function works with the upper-left cell in the range.

Excel 2013 includes a new function, FORMULATEXT, that can be used to indicate whether a cell has a formula. This function returns the formula in a cell (as text). If the referenced cell does not contain a formula, it returns #N/A. To emulate the CELLHASFORMULA function, use a formula like this:

=NOT(ISNA(FORMULATEXT(A1)))

Returning a cell's formula

The following CELLFORMULA function returns the formula for a cell as a string. If the cell does not have a formula, it returns an empty string.

Function CELLFORMULA(cell As Range) As String

‘   Returns the formula in cell, or an

‘   empty string if cell has no formula

    Dim UpperLeft As Range

    Set UpperLeft = cell.Range(“A1”)

    If UpperLeft.HasFormula Then

        CELLFORMULA = UpperLeft.Formula

    Else

        CELLFORMULA = “”

    End If

End Function

This function creates a Range object variable named UpperLeft. This variable represents the upper-left cell in the argument that is passed to the function. This ensures that the code works with only one cell. Alternatively, the function can be written like this:

Function CELLFORMULA(cell As Range) As String

    Application.Volatile True

    If cell.Range(“A1”).HasFormula Then

        CELLFORMULA = cell.Range(“A1”).Formula

    Else

        CELLFORMULA = “”

    End If

End Function

If you don't require compatibility with versions prior to Excel 2013, you can use the new FORMULATEXT function instead of this custom function.

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

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.

Excel 2013 includes a new function, SHEET. This function returns 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

Returning Excel's version number

The following function returns Excel's version number. For example, if you use Excel 2013, it returns the text string 15.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).

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 returned by the function.

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

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:

=CELLTYPE(A1)

The workbook celltype function.xlsm that demonstrates the CELLTYPE function is available at this book's website.

Figure 26-1: The CELLTYPE function returns a string that describes the contents of a cell.

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:

=STATFUNCTION(C1:C12,B14)

Figure 26-2: Selecting an operation from the list displays the result in cell C14.

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 also that the op argument is the string SUM. The expression that is used as an argument for the Evaluate method is

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 that the Address property has an argument: external:=True. That argument controls how 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.

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.

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.

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

=STATICRANDBETWEEN(1,1000)

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 a volatile function. (For more information about controlling recalculation, see the nearby 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”)

I 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 percent):

=COMMISSION(25000)

This function is very 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.

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. The formula in cell D2 is

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

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 any single character:

=ISLIKE(“???”,”[?][?][?]”)

The Like operator is very 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.

Thanks to Rick Rothstein for suggesting this function, which is much more efficient than my original function.

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 cell 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.

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 nth 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

=SPELLDOLLARS(A1)

Note that negative numbers are spelled out and enclosed in parentheses.

Figure 26-5: Examples of the SPELLDOLLARS function.

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 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, then you can probably create a custom function. This section contains three functions that perform counting.

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

I 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 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.

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/30/2013, which is the first Monday after Christmas Day, 2013 (which is a Wednesday):

=NEXTMONDAY(DATE(2013,12,25))

The function returns a date serial number. You will 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, I 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 are

• 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.

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.

The extended date functions don't make any 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 presents standard formulas for this task, but you may prefer to use a custom function.

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 in order 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

In Chapter 15, I 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.

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.

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 2013) 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)_                .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, I 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 I 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.

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.

Part IV 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 Chapters 14 and 15 for more details on array formulas.

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.

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 (as in A7:A18 in Figure 26-5), 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 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

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 A17:D29, returns the contents of A2:D14 in a random order:

{=RANGERANDOMIZE(A2:D14)}

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,num_chars)

The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1.

Figure 26-9: The RANGERANDOMIZE function returns the contents of a range, but in a randomized order.

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 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 any 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

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 has the advantage of allowing you 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.

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.

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 indefinite 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, then 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 very 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 a valid formula — 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

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 my system, a worksheet with 5,000 SUM formulas recalculated instantly. After I 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, I 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.