In This Chapter

• Understanding the difference between Sub procedures and Function procedures
• Creating custom functions
• Looking at Function procedures and function arguments
• Creating a function that emulates Excel’s SUM function
• Using functions that enable you to work with pre-1900 dates in your worksheets
• Debugging functions, dealing with the Insert Function dialog box, and using add-ins to store custom functions
• Calling the Windows Application Programming Interface (API) to perform otherwise impossible feats

Sub Procedures versus Function Procedures

A VBA Function is a procedure that performs calculations and returns a value. You can use these functions in your Visual Basic for Applications (VBA) code or in worksheet formulas.

VBA enables you to create Sub procedures and Function procedures. You can think of a Sub procedure as a command that either the user or another procedure can execute. Function procedures, on the other hand, usually return a single value (or an array), just like Excel worksheet functions and VBA built-in functions. As with built-in functions, your Function procedures can use arguments.

Function procedures are versatile, and you can use them in two situations:

• As part of an expression in a VBA procedure
• In formulas that you create in a worksheet

In fact, you can use a Function procedure anywhere that you can use an Excel worksheet function or a VBA built-in function. The only exception is that you can’t use a VBA function in a data validation formula. You can, however, use a custom VBA function in a conditional formatting formula.

We cover Sub procedures in the preceding chapter and Function procedures in this chapter.

Why Create Custom Functions?

You’re undoubtedly familiar with Excel worksheet functions; even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. Excel includes more than 450 predefined worksheet functions that you can use in formulas. In addition, you can create custom functions by using VBA.

With all the functions available in Excel and VBA, you might wonder why you’d ever need to create new functions. The answer: to simplify your work. With a bit of planning, custom functions are useful in worksheet formulas and VBA procedures.

Often, for example, you can create a custom function that can significantly shorten your formulas. And shorter formulas are more readable and easier to work with. The trade-off, however, is that custom functions are usually much slower than built-in functions. And, of course, the user must enable macros to use these functions.

When you create applications, you may notice that some procedures repeat certain calculations. In such cases, consider creating a custom function that performs the calculation. Then you can call the function from your procedure. A custom function can eliminate the need for duplicated code, thus reducing errors.

An Introductory Function Example

Without further ado, this section presents an example of a VBA Function procedure.

The following is a custom function defined in a VBA module. This function, named REMOVEVOWELS, uses a single argument. The function returns the argument, but with all the vowels removed.

Function REMOVEVOWELS(Txt) As String
' Removes all vowels from the Txt argument
    Dim i As Long
    RemoveVowels =""
    For i = 1 To Len(Txt)
        If Not UCase(Mid(Txt, i, 1)) Like"[AEIOU]" Then
            REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)
        End If
    Next i
End Function

This function certainly isn’t the most useful function, but it demonstrates some key concepts related to functions. We explain how this function works later, in the “Analyzing the custom function” section.

Using the function in a worksheet

When you enter a formula that uses the REMOVEVOWELS function, Excel executes the code to get the result that’s returned by the function. Here’s an example of how you’d use the function in a formula:

=REMOVEVOWELS(A1)

See Figure 5.1 for examples of this function in action. The formulas are in column B, and they use the text in column A as their arguments. As you can see, the function returns the single argument, but with the vowels removed.

Actually, the function works like any built-in worksheet function. You can insert it in a formula by choosing Formulas ➜ Function Library ➜ Insert Function or by clicking the Insert Function Wizard icon to the left of the formula bar. Either of these actions displays the Insert Function dialog box. In the Insert Function dialog box, your custom functions are located, by default, in the User Defined category.

You can also nest custom functions and combine them with other elements in your formulas. For example, the following formula nests the REMOVEVOWELS function inside Excel’s UPPER function. The result is the original string (sans vowels), converted to uppercase.

=UPPER(REMOVEVOWELS(A1))

Using the function in a VBA procedure

In addition to using custom functions in worksheet formulas, you can use them in other VBA procedures. The following VBA procedure, which is defined in the same module as the custom REMOVEVOWELS function, first displays an input box to solicit text from the user. Then the procedure uses the VBA built-in MsgBox function to display the user input after the REMOVEVOWELS function processes it (see Figure 5.2). The original input appears as the caption in the message box.

Sub ZapTheVowels()
     Dim UserInput as String
     UserInput = InputBox("Enter some text:")
     MsgBox REMOVEVOWELS(UserInput), vbInformation, UserInput
End Sub

Figure 5.2 shows text entered into an input box, and the result displayed in a message box .

Analyzing the custom function

Function procedures can be as complex as you need them to be. Most of the time, they’re more complex and much more useful than this sample procedure. Nonetheless, an analysis of this example may help you understand what is happening.

Here’s the code, again:

Function REMOVEVOWELS(Txt) As String
' Removes all vowels from the Txt argument
    Dim i As Long
    REMOVEVOWELS =""
    For i = 1 To Len(Txt)
        If Not UCase(Mid(Txt, i, 1)) Like"[AEIOU]" Then
            REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)
        End If
    Next i
End Function

Note that the procedure starts with the keyword Function, rather than Sub, followed by the name of the function (REMOVEVOWELS). This custom function uses only one argument (Txt), enclosed in parentheses. As String defines the data type of the function’s return value. Excel uses the Variant data type if no data type is specified.

The second line is an optional comment that describes what the function does. This line is followed by a Dim statement, which declares the variable (i) used in the procedure as type Long.

The next five instructions make up a For-Next loop. The procedure loops through each character in the input and builds the string. The first instruction in the loop uses VBA’s Mid function to return a single character from the input string and converts this character to uppercase. That character is then compared to a list of characters by using Excel’s Like operator. In other words, the If clause is true if the character isn’t A, E, I, O, or U. In such a case, the character is appended to the REMOVEVOWELS variable.

When the loop is finished, REMOVEVOWELS consists of the input string with all vowels removed. This string is the value that the function returns.

The procedure ends with an End Function statement.

Keep in mind that you can do the coding for this function in a number of different ways. Here’s a function that accomplishes the same result but is coded differently:

Function REMOVEVOWELS(txt) As String
' Removes all vowels from the Txt argument
    Dim i As Long
    Dim TempString As String
    TempString =""
    For i = 1 To Len(txt)
        Select Case ucase(Mid(txt, i, 1))
            Case"A","E","I","O","U"
                'Do nothing
            Case Else
                TempString = TempString & Mid(txt, i, 1)
        End Select
    Next i
    REMOVEVOWELS = TempString
End Function

In this version, we used a string variable (TempString) to store the vowel-less string as it’s being constructed. Then, before the procedure ends, we assigned the contents of TempString to the function’s name. This version also uses a Select Case construct rather than an If-Then construct.

Function Procedures

A Function procedure has much in common with a Sub procedure. (For more information on Sub procedures, see Chapter 4.)

The syntax for declaring a function is as follows:

[Public | Private][Static] Function name ([arglist])[As type]
    [instructions]
    [name = expression]
    [Exit Function]
    [instructions]
    [name = expression]
End Function

The Function procedure contains the following elements:

• Public: Optional. Indicates that the Function procedure is accessible to all other procedures in all other modules in all active Excel VBA projects.
• Private: Optional. Indicates that the Function procedure is accessible only to other procedures in the same module.
• Static: Optional. Indicates that the values of variables declared in the Function procedure are preserved between calls.
• Function: Required. Indicates the beginning of a procedure that returns a value or other data.
• name: Required. Any valid Function procedure name, which must follow the same rules as a variable name.
• arglist: Optional. A list of one or more variables that represent arguments passed to the Function procedure. The arguments are enclosed in parentheses. Use a comma to separate pairs of arguments.
• type: Optional. The data type returned by the Function procedure.
• instructions: Optional. Any number of valid VBA instructions.
• Exit Function: Optional. A statement that forces an immediate exit from the Function procedure before its completion.
• End Function: Required. A keyword that indicates the end of the Function procedure.

A key point to remember about a custom function written in VBA is that a value is always assigned to the function’s name a minimum of one time, generally when it has completed execution.

To create a custom function, start by inserting a VBA module. You can use an existing module, as long as it’s a normal VBA module. Enter the keyword Function, followed by the function name and a list of its arguments (if any) in parentheses. You can also declare the data type of the return value by using the As keyword (this step is optional but recommended). Insert the VBA code that performs the work, making sure that the appropriate value is assigned to the term corresponding to the function name at least once in the body of the Function procedure. End the function with an End Function statement.

Function names must adhere to the same rules as variable names. If you plan to use your custom function in a worksheet formula, be careful if the function name is also a cell address. For example, if you use something such as ABC123 as a function name, you can’t use the function in a worksheet formula because ABC123 is a cell address. If you do so, Excel displays a #REF! error.

The best advice is to avoid using function names that are also cell references, including named ranges. And avoid using function names that correspond to Excel’s built-in function names. In the case of a function name conflict, Excel always uses its built-in function.

A function’s scope

In Chapter 4, we discuss the concept of a procedure’s scope (public or private). The same discussion applies to functions: A function’s scope determines whether it can be called by procedures in other modules or in worksheets.

Here are a few things to keep in mind about a function’s scope:

• If you don’t declare a function’s scope, its default scope is Public.
• Functions declared As Private don’t appear in Excel’s Insert Function dialog box. Therefore, when you create a function that should be used only in a VBA procedure, you should declare it Private so that users don’t try to use it in a formula.
• If your VBA code needs to call a function that’s defined in another workbook, set up a reference to the other workbook by choosing the Visual Basic Editor (VBE) Tools ➜ References command.
• You do not have to establish a reference if the function is defined in an add-in. Such a function is available for use in all workbooks.

Executing function procedures

Although you can execute a Sub procedure in many ways, you can execute a Function procedure in only four ways:

• Call it from another procedure
• Use it in a worksheet formula
• Use it in a formula that’s used to specify conditional formatting
• Call it from the VBE Immediate window

Total = SUMARRAY(MyArray)

Total = Application.Run ("SUMARRAY","MyArray")

In a conditional formatting formula

When you specify conditional formatting, one of the options is to create a formula. The formula must be a logical formula (that is, it must return either TRUE or FALSE). If the formula returns TRUE, the condition is met and formatting is applied to the cell.

You can use custom VBA functions in your conditional formatting formulas. For example, here’s a simple VBA function that returns TRUE if its argument is a cell that contains a formula:

Function CELLHASFORMULA(cell) As Boolean
    CELLHASFORMULA = cell.HasFormula
End Function

After defining this function in a VBA module, you can set up a conditional formatting rule so that cells that contain a formula contain different formatting:

1. Select the range that will contain the conditional formatting.

   For example, select A1:G20.

2. Choose Home ➜ Styles ➜ Conditional Formatting ➜ New Rule.
3. In the New Formatting Rule dialog box, select the option labeled Use a Formula to Determine Which Cells to Format.

4. Enter this formula in the formula box — but make sure that the cell reference argument corresponds to the upper-left cell in the range that you selected in Step 1:

   =CELLHASFORMULA(A1)

5. Click the Format button to specify the formatting for cells that meet this condition.
6. Click OK to apply the conditional formatting rule to the selected range.

Cells in the range that contain a formula will display the formatting you specified. In the New Formatting Rule dialog box shown in Figure 5.3, we are specifying a custom function in a formula.

From the VBE Immediate Window

The final way to call a Function procedure is from the VBE Immediate window. This method is generally used only for testing. Figure 5.4 shows an example. The ? character is a shortcut for print.

Function Arguments

Keep in mind the following points about Function procedure arguments:

• Arguments can be variables (including arrays), constants, literals, or expressions.
• Some functions don’t have arguments.
• Some functions have a fixed number of required arguments (from 1 to 60).
• Some functions have a combination of required and optional arguments.

Function Examples

In this section, we present a series of examples that demonstrate how to use arguments effectively with functions. By the way, this discussion applies also to Sub procedures.

Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don’t use arguments, including RAND, TODAY, and NOW. You can create similar functions.

This section contains examples of functions that don’t use an argument.

Here’s a simple example of a function that doesn’t use an argument. The following function returns the UserName property of the Application object. This is the name that appears in the Excel Options dialog box (General tab) and is stored in the Windows Registry.

Function USER()
'   Returns the name of the current user
    USER = Application.UserName
End Function

When you enter the following formula, the cell returns the name of the current user:

=USER()

There is no need to use this function in another procedure because you can simply access the UserName property directly in your code.

The USER function demonstrates how you can create a wrapper function that returns a property or the result of a VBA function. Following are three additional wrapper functions that take no argument:

Function EXCELDIR() As String
'   Returns the directory in which Excel is installed
    EXCELDIR = Application.Path
End Function
 
Function SHEETCOUNT()
'   Returns the number of sheets in the workbook
    SHEETCOUNT = Application.Caller.Parent.Parent.Sheets.Count
End Function
 
Function SHEETNAME()
'   Returns the name of the worksheet
    SHEETNAME = Application.Caller.Parent.Name
End Function

You can probably think of other potentially useful wrapper functions. For example, you can write a function to display the template’s location (Application.TemplatesPath), the default file location (Application.DefaultFilePath), and the version of Excel (Application.Version). Also, note that Excel 2013 introduced a worksheet function, SHEETS, that makes the SHEETCOUNT function obsolete.

Here’s another example of a function that doesn’t take an argument. Most people use Excel’s RAND function to quickly fill a range of cells with values. But the RAND function forces random values to be changed whenever the worksheet was recalculated, So after using the RAND function, most people will, convert the formulas to values.

As an alternative, you could use VBA to create a custom function that returns static random numbers that do not change. The custom function follows:

Function STATICRAND()
'   Returns a random number that doesn't
'   change when recalculated
    STATICRAND = Rnd()
End Function

If you want to generate a series of random integers between 0 and 1,000, you can use a formula such as this:

=INT(STATICRAND()*1000)

The values produced by this formula never change when the worksheet is calculated normally. However, you can force the formula to recalculate by pressing Ctrl+Alt+F9.

A function with one argument

This section describes a function for sales managers who need to calculate the commissions earned by their sales forces. The calculations in this example are based on the following table:

Note that the commission rate is nonlinear and also depends on the month’s total sales. Employees who sell more earn a higher commission rate.

You can calculate commissions for various sales amounts entered in a worksheet in several ways. If you’re not thinking too clearly, you can waste lots of time and come up with a lengthy formula such as this one:

=IF(AND(A1>=0,A1<=9999.99),A1*0.08,
 IF(AND(A1>=10000,A1<=19999.99),A1*0.105,
 IF(AND(A1>=20000,A1<=39999.99),A1*0.12,
 IF(A1>=40000,A1*0.14,0))))

This approach is bad for a couple of reasons. First, the formula is overly complex, making it difficult to understand. Second, the values are hard-coded into the formula, making the formula difficult to modify.

A better (non-VBA) approach is to use a lookup table function to compute the commissions. For example, the following formula uses VLOOKUP to retrieve the commission value from a range named Table and multiplies that value by the value in cell A1:

=VLOOKUP(A1,Table,2)*A1

Yet another approach (which eliminates the need to use a lookup table) is to create a custom function such as the following:

Function COMMISSION(Sales)
    Const Tier1 = 0.08
    Const Tier2 = 0.105
    Const Tier3 = 0.12
    Const Tier4 = 0.14
'   Calculates sales commissions
    Select Case Sales
       Case 0 To 9999.99: COMMISSION = Sales * Tier1
       Case 10000 To 19999.99: COMMISSION = Sales * Tier2
       Case 20000 To 39999.99: COMMISSION = Sales * Tier3
       Case Is >= 40000: COMMISSION = Sales * Tier4
    End Select
End Function

After you enter this function in a VBA module, you can use it in a worksheet formula or call the function from other VBA procedures.

Entering the following formula into a cell produces a result of 3,000; the amount (25,000) qualifies for a commission rate of 12 percent:

=COMMISSION(25000)

Even if you don’t need custom functions in a worksheet, creating Function procedures can make your VBA coding much simpler. For example, if your VBA procedure calculates sales commissions, you can use the same function and call it from a VBA procedure. Here’s a tiny procedure that asks the user for a sales amount and then uses the COMMISSION function to calculate the commission due:

Sub CalcComm()
    Dim Sales as Long
    Sales = InputBox("Enter Sales:")
    MsgBox"The commission is" & COMMISSION(Sales)
End Sub

The CalcComm procedure starts by displaying an input box that asks for the sales amount. Then it displays a message box with the calculated sales commission for that amount.

This Sub procedure works, but it’s crude. Following is an enhanced version with a bit of error handling. It also displays formatted values and keeps looping until the user clicks No (see Figure 5.5).

Sub CalcComm()
    Dim Sales As Long
    Dim Msg As String, Ans As String
 
'   Prompt for sales amount
    Sales = Val(InputBox("Enter Sales:", _
    "Sales Commission Calculator"))
 
'   Exit if canceled
    If Sales = 0 Then Exit Sub
 
'   Build the Message
    Msg ="Sales Amount:" & vbTab & Format(Sales,"$#,##0.00")
    Msg = Msg & vbCrLf &"Commission:" & vbTab
    Msg = Msg & Format(COMMISSION(Sales),"$#,##0.00")
    Msg = Msg & vbCrLf & vbCrLf &"Another?"
 
'   Display the result and prompt for another
    Ans = MsgBox(Msg, vbYesNo,"Sales Commission Calculator")
    If Ans = vbYes Then CalcComm
End Sub

This function uses two VBA built-in constants: vbTab represents a tab (to space the output), and vbCrLf specifies a carriage return and line feed (to skip to the next line). VBA’s Format function displays a value in a specified format (in this case, with a dollar sign, a comma, and two decimal places).

In both examples, the Commission function must be available in the active workbook; otherwise, Excel displays an error message saying that the function isn’t defined.

A function with two arguments

Imagine that the aforementioned hypothetical sales managers implement a new policy to help reduce turnover: The total commission paid is increased by 1 percent for every year that the salesperson has been with the company.

We modified the custom COMMISSION function (defined in the preceding section) so that it takes two arguments. The new argument represents the number of years. Call this new function COMMISSION2:

Function COMMISSION2(Sales, Years)
'    Calculates sales commissions based on
'    years in service
     Const Tier1 = 0.08
     Const Tier2 = 0.105
     Const Tier3 = 0.12
     Const Tier4 = 0.14
     Select Case Sales
        Case 0 To 9999.99: COMMISSION2 = Sales * Tier1
        Case 10000 To 19999.99: COMMISSION2 = Sales * Tier2
        Case 20000 To 39999.99: COMMISSION2 = Sales * Tier3
        Case Is >= 40000: COMMISSION2 = Sales * Tier4
     End Select
     COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100)
End Function

Pretty simple, right? We just added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission.

Here’s an example of how you can write a formula using this function (it assumes that the sales amount is in cell A1 and the number of years the salesperson has worked is in cell B1):

=COMMISSION2(A1,B1)

A function with an array argument

A Function procedure also can accept one or more arrays as arguments, process the array(s), and return a single value. The array can also consist of a range of cells.

The following function accepts an array as its argument and returns the sum of its elements:

Function SUMARRAY(List) As Double
    Dim Item As Variant
    SumArray = 0
    For Each Item In List
        If WorksheetFunction.IsNumber(Item) Then _
            SUMARRAY = SUMARRAY + Item
    Next Item
End Function

Excel’s ISNUMBER function checks to see whether each element is a number before adding it to the total. Adding this simple error-checking statement eliminates the type-mismatch error that occurs when you try to perform arithmetic with something other than a number.

The following procedure demonstrates how to call this function from a Sub procedure. The MakeList procedure creates a 100-element array and assigns a random number to each element. Then the MsgBox function displays the sum of the values in the array by calling the SUMARRAY function.

Sub MakeList()
    Dim Nums(1 To 100) As Double
    Dim i As Integer
    For i = 1 To 100
        Nums(i) = Rnd * 1000
    Next i
    MsgBox SUMARRAY(Nums)
End Sub

Note that the SUMARRAY function doesn’t declare the data type of its argument (it’s a variant). Because it’s not declared as a specific numeric type, the function also works in your worksheet formulas in which the argument is a Range object. For example, the following formula returns the sum of the values in A1:C10:

=SUMARRAY(A1:C10)

You might notice that, when used in a worksheet formula, the SUMARRAY function works very much like Excel’s SUM function. One difference, however, is that SUMARRAY doesn’t accept multiple arguments. Understand that this example is for educational purposes only. Using the SUMARRAY function in a formula offers no advantages over the Excel SUM function.

A function with optional arguments

Many of Excel’s built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its syntax is:

LEFT(text,num_chars)

The first argument is required, but the second is optional. If the optional argument is omitted for the LEFT function, Excel assumes a value of 1. Therefore, the following two formulas return the same result:

=LEFT(A1,1)
=LEFT(A1)

The custom functions that you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument’s name with the keyword Optional. In the argument list, optional arguments must appear after any required arguments.

Following is a simple function example that returns the user’s name. The function’s argument is optional.

Function USER(Optional UpperCase As Variant)
    If IsMissing(UpperCase) Then UpperCase = False
    USER = Application.UserName
    If UpperCase Then USER = UCase(USER)
End Function

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 is converted to uppercase (using the VBA UCase function) before it’s returned. Note 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).

All the following formulas are valid, and the first two produce the same result:

=USER()
=USER(False)
=USER(True)

The following is another example of a custom function that uses an optional argument. This function randomly chooses one cell from an input range and returns that cell’s contents. If the second argument is True, the selected value changes whenever the worksheet is recalculated (that is, the function is made volatile). If the second argument is False (or omitted), the function isn’t recalculated unless one of the cells in the input range is modified.

Function DRAWONE(Rng As Variant, Optional Recalc As Variant = False)
'    Chooses one cell at random from a range
 
'    Make function volatile if Recalc is True
     Application.Volatile Recalc
 
'    Determine a random cell
     DRAWONE = Rng(Int((Rng.Count) * Rnd + 1))
End Function

Note that the second argument for DRAWONE includes the Optional keyword, along with a default value.

All the following formulas are valid, and the first two have the same effect:

=DRAWONE(A1:A100)
=DRAWONE(A1:A100,False)
=DRAWONE(A1:A100,True)

This function might be useful for choosing lottery numbers, picking a winner from a list of names, and so on.

A function that returns a VBA array

VBA includes a useful function called Array. The Array function returns a variant that contains an array (that is, multiple values). If you’re familiar with array formulas in Excel, you have a head start on understanding VBA’s Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts curly braces around the formula to indicate that it’s an array formula.

The MONTHNAMES function, which follows, is a simple example that uses VBA’s Array function in a custom function:

Function MONTHNAMES ()
    MONTHNAMES = Array("Jan","Feb","Mar","Apr","May","Jun", _
      "Jul","Aug","Sep","Oct","Nov","Dec")
End Function

The MONTHNAMES function returns a horizontal array of month names. You can create a multicell array formula that uses the MONTHNAMES function. Here’s how to use it: Make sure that the function code is present in a VBA module. Next, in a worksheet, select multiple cells in a row (start by selecting 12 cells). Then enter the formula that follows (without the braces) and press Ctrl+Shift+Enter:

{=MONTHNAMES()}

What if you’d like to generate a vertical list of month names? No problem; just select a vertical range, enter the following formula (without the braces), and then press Ctrl+Shift+Enter:

{=TRANSPOSE(MONTHNAMES ())}

This formula uses the Excel TRANSPOSE function to convert the horizontal array to a vertical array.

The following example is a variation on the MONTHNAMES function:

Function MonthNames(Optional MIndex)
    Dim AllNames As Variant
    Dim MonthVal As Long
    AllNames = Array("Jan","Feb","Mar","Apr","May","Jun", _
      "Jul","Aug","Sep","Oct","Nov","Dec")
    If IsMissing(MIndex) Then
        MONTHNAMES = AllNames
        Else
        Select Case MIndex
            Case Is >= 1
'            Determine month value (for example, 13=1)
             MonthVal = ((MIndex - 1) Mod 12)
             MONTHNAMES = AllNames(MonthVal)
          Case Is <= 0 ' Vertical array
             MONTHNAMES = Application.Transpose(AllNames)
         End Select
    End If
End Function

Note that we use the VBA IsMissing function to test for a missing argument. In this situation, it isn’t possible to specify the default value for the missing argument in the argument list of the function because the default value is defined in the function. You can use the IsMissing function only if the optional argument is a variant.

This enhanced function uses an optional argument that works as follows:

• If the argument is missing, the function returns a horizontal array of month names.
• If the argument is less than or equal to 0, the function returns a vertical array of month names. It uses Excel’s TRANSPOSE function to convert the array.
• If the argument is greater than or equal to 1, the function returns the month name that corresponds to the argument value.

You can use this function in a number of ways, as illustrated in Figure 5.6.

Range A1:L1 contains the following formula entered as an array. Start by selecting A1:L1, enter the formula (without the braces), and then press Ctrl+Shift+Enter.

{=MONTHNAMES()}

Range A3:A14 contains integers from 1 to 12. Cell B3 contains the following (nonarray) formula, which was copied to the 11 cells below it:

=MONTHNAMES(A3)

Range D3:D14 contains the following formula entered as an array:

{=MONTHNAMES(-1)}

Cell F3 contains this (nonarray) formula:

=MONTHNAMES(3)

A function that returns an error value

In some cases, you might want your custom function to return a particular error value. Consider the REMOVEVOWELS function, which we presented earlier in this chapter:

Function REMOVEVOWELS(Txt) As String
' Removes all vowels from the Txt argument
    Dim i As Long
    RemoveVowels =""
    For i = 1 To Len(Txt)
        If Not UCase(Mid(Txt, i, 1)) Like"[AEIOU]" Then
            REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)
        End If
    Next i
End Function

When used in a worksheet formula, this function removes the vowels from its single-cell argument. If the argument is a numeric value, this function returns the value as a string. You may prefer that the function returns an error value (#N/A), rather than the numeric value converted to a string.

You may be tempted simply to assign a string that looks like an Excel formula error value. For example:

REMOVEVOWELS ="#N/A"

Although the string looks like an error value, other formulas that may reference it don’t treat it as such. 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 errors are Excel formula error values and not VBA runtime error values. These constants are as follows:

• xlErrDiv0 (for #DIV/0!)
• xlErrNA (for #N/A)
• xlErrName (for #NAME?)
• xlErrNull (for #NULL!)
• xlErrNum (for #NUM!)
• xlErrRef (for #REF!)
• xlErrValue (for #VALUE!)

To return a #N/A error from a custom function, you can use a statement like this:

REMOVEVOWELS = CVErr(xlErrNA)

The revised REMOVEVOWELS function follows. This function uses an If-Then construct to take a different action if the argument isn’t text. It uses Excel’s ISTEXT function to determine whether the argument is text. If the argument is text, the function proceeds normally. If the cell doesn’t contain text (or is empty), the function returns the #N/A error.

Function REMOVEVOWELS (Txt) As Variant
' Removes all vowels from the Txt argument
' Returns #VALUE if Txt is not a string
    Dim i As Long
    RemoveVowels =""
    If Application.WorksheetFunction.IsText(Txt) Then
        For i = 1 To Len(Txt)
            If Not UCase(Mid(Txt, i, 1)) Like"[AEIOU]" Then
                REMOVEVOWELS = REMOVEVOWELS & Mid(Txt, i, 1)
            End If
        Next i
    Else
        REMOVEVOWELS = CVErr(xlErrNA)
    End If
End Function

A function with an indefinite number of arguments

Some 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 specify as many as 254 additional arguments. Here’s an example of a SUM function with four range arguments:

=SUM(A1:A5,C1:C5,E1:E5,G1:G5)

You can even mix and match the argument types. For example, the following example uses three arguments: The first is a range, the second is a value, and the third is 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.

Following is a function that can have any number of single-value arguments. (It doesn’t work with multicell range arguments.) It simply returns the sum of the arguments.

Function SIMPLESUM(ParamArray arglist() As Variant) As Double
    For Each arg In arglist
        SIMPLESUM = SIMPLESUM + arg
    Next arg
End Function

To modify this function so that it works with multicell range arguments, you need to add another loop, which processes each cell in each of the arguments:

Function SIMPLESUM (ParamArray arglist() As Variant) As Double
    Dim cell As Range
    For Each arg In arglist
        For Each cell In arg
            SIMPLESUM = SIMPLESUM + cell
        Next cell
    Next arg
End Function

The SIMPLESUM function is similar to Excel’s SUM function, but it’s not nearly as flexible. Try it by using various types of arguments, and you’ll see that it fails if any of the cells contain a nonvalue, or even if you use a literal value for an argument.

Emulating Excel’s SUM Function

In this section, we present a custom function called MYSUM. Unlike the SIMPLESUM function listed in the preceding section, the MYSUM function emulates Excel’s SUM function (almost) perfectly.

Before you look at the code for MYSUM, take a minute to think about the Excel SUM function. It is versatile: It can have as many as 255 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(B1,5,"6",,TRUE,SQRT(4),A1:A5,D:D,C2*C3)

This perfectly valid formula contains all the following types of arguments, listed here in the order of their presentation:

• A single-cell reference
• A literal value
• A string that looks like a value
• A missing argument
• A logical TRUE value
• An expression that uses another function
• A simple range reference
• A range reference that includes an entire column
• An expression that calculates the product of two cells

The MYSUM function (see Listing 5-1) handles all these argument types.

Listing 5-1: MYSUM Function

Figure 5.7 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)}

If you’re interested in learning how the MYSUM function works, create a formula that uses the function. Then set a breakpoint in the code and step through the statements line by line. (See the section"Debugging Functions," later in this chapter.) Try this for several different argument types, and you’ll soon have a good feel for how the MYSUM function works.

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, and so on). Each argument type is handled differently.
• For a range argument, the function loops through each cell in the range, determines the type of data in the cell, and (if appropriate) 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 are an error value.
• If an argument contains an error (for example, #DIV/0!), the MYSUM function simply returns the error — just as Excel’s SUM function does.
• Excel’s 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. (The VBA IsNumeric function is used to determine whether a string can be evaluated as a number.)
• 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 technique 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. The MYSUM function, of course, is much slower, but just how much slower depends on the speed of your system and the formulas themselves. However, 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.

Extended Date Functions

A common complaint among Excel users is the inability to work with dates prior to 1900. For example, genealogists often use Excel to keep track of birth and death dates. If either of those dates occurs in a year prior to 1900, calculating the number of years the person lived isn’t possible.

We created a series of functions that take advantage of the fact that VBA can work with a much larger range of dates. The earliest date recognized by VBA is January 1, 0100.

The 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 5.8 shows a workbook that uses some of these functions.

Keep in mind that the date returned by these functions is a string, not a real date. Therefore, you can’t perform mathematical operations on the returned value using Excel’s standard operators. You can, however, use the return value as an argument for other Extended Date functions.

The functions are surprisingly simple. For example, here’s the listing for the XDATE function:

Function XDATE(y, m, d, Optional fmt As String) As String
    If IsMissing(fmt) Then fmt ="Short Date"
    XDATE = Format(DateSerial(y, m, d), fmt)
End Function

The arguments for XDATE are:

• y: Required. A four-digit year (0100–9999).
• m: Required. A month number (1–12).
• d: Required. A day number (1–31).
• fmt: Optional. A date format string.

If the fmt argument is omitted, the date is displayed using the system’s short date setting (as specified in the Windows Control Panel).

If the m or d argument exceeds a valid number, the date rolls over into the next year or month. For example, a month of 13 is interpreted as January of the next year.

Debugging Functions

When you’re using a formula in a worksheet to test a Function procedure, VBA runtime errors don’t appear in the all-too-familiar, pop-up error box. If an error occurs, the formula simply returns an error value (#VALUE!). The lack of a pop-up error message doesn’t present a problem for debugging functions because you have several possible workarounds:

• Place MsgBox functions at strategic locations to monitor the value of specific variables. Message boxes in Function procedures do pop up when the procedure is executed. But make sure that you have only one formula in the worksheet that uses your function; otherwise, message boxes will appear for each formula that is evaluated, which will quickly become annoying.
• Test the procedure by calling it from a Sub procedure, not from a worksheet formula. Runtime errors are displayed in the usual manner, and you can either fix the problem (if you know it) or jump right into using Debugger.
• Set a breakpoint in the function, and then step through the function. You then can access all standard VBA debugging tools. To set a breakpoint, move the cursor to the statement at which you want to pause execution and then choose Debug ➜ Toggle Breakpoint (or press F9). When the function is executing, press F8 to step through the procedure line-by-line.

• Use one or more temporary Debug.Print statements in your code to write values to the VBE Immediate window. For example, if you want to monitor a value inside a loop, use something like the following routine:

  Function VOWELCOUNT(r) As Long
      Dim Count As Long
      Dim i As Long
      Dim Ch As String * 1
      Count = 0
      For i = 1 To Len(r)
          Ch = UCase(Mid(r, i, 1))
          If Ch Like"[AEIOU]" Then
             Count = Count + 1
             Debug.Print Ch, i
          End If
      Next i
      VOWELCOUNT = Count
  End Function
  

  In this case, the values of two variables, Ch and i, are printed to the Immediate window whenever the Debug.Print statement is encountered. Figure 5.9 shows the result when the function has an argument of Tucson, Arizona.

Dealing with the Insert Function Dialog Box

Excel’s Insert Function dialog box is a handy tool. When you’re creating a worksheet formula, this tool lets you select a particular worksheet function from a list of functions. These functions are grouped into various categories to make locating a particular function easier. When you select a function and click OK, the Function Arguments dialog box appears to help insert the function’s arguments.

The Insert Function dialog box also displays your custom worksheet functions. By default, custom functions are listed under the User Defined category. The Function Arguments dialog box prompts you for a custom function’s arguments.

The Insert Function dialog box enables you to search for a function by keyword. Unfortunately, you can’t use this search feature to locate custom functions created in VBA.

Using the MacroOptions method

You can use the MacroOptions method of the Application object to make your functions appear just like built-in functions. Specifically, this method enables you to:

• Provide a description of the function
• Specify a function category
• Provide descriptions for the function arguments

Following is an example of a procedure that uses the MacroOptions method to provide information about a function:

Sub DescribeFunction()
    Dim FuncName As String
    Dim FuncDesc As String
    Dim FuncCat As Long
    Dim Arg1Desc As String, Arg2Desc As String
    FuncName ="DRAWONE"
    FuncDesc ="Displays the contents of a random cell from a range"
    FuncCat = 5
    Arg1Desc ="The range that contains the values"
    Arg2Desc ="(Optional) If False or missing, a new cell is selected when"
    Arg2Desc = Arg2Desc &"recalculated. If True, a new cell is selected"
    Arg2Desc = Arg2Desc &"when recalculated."
    Application.MacroOptions _
        Macro:=FuncName, _
        Description:=FuncDesc, _
        Category:=FuncCat, _
        ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub

This procedure uses variables to store the information, and the variables are used as arguments for the MacroOptions method. The function is assigned to function category 5 (Lookup & Reference). Note that descriptions for the two arguments are indicated by using an array as the last argument for the MacroOptions method.

Figure 5.10 shows the Insert Function and Function Arguments dialog boxes after executing this procedure.

You need to execute the DescribeFunction procedure only one time. After doing so, the information assigned to the function is stored in the workbook. You can also omit arguments for the MacroOptions method. For example, if you don’t need the arguments to have descriptions, just omit the ArgumentDescriptions argument in the code.

Specifying a function category

If you don’t use the MacroOptions method to specify a different category, your custom worksheet functions appear in the User Defined category in the Insert Function dialog box. You may prefer to assign your function to a different category. Assigning a function to a category also causes it to appear in the drop-down controls in the Formulas ➜ Function Library group on the Ribbon.

Table 5.1 lists the category numbers that you can use for the Category argument for the MacroOptions method. A few of these categories (10 through 13) aren’t normally displayed in the Insert Function dialog box. If you assign your function to one of these categories, the category will appear in the dialog box.

Adding a function description manually

As an alternative to using the MacroOptions method to provide a function description, you can use the Macro dialog box.

Follow these steps to provide a description for a custom function:

1. Create your function in VBE.
2. Activate Excel, making sure that the workbook that contains the function is the active workbook.

3. Choose Developer ➜ Code ➜ Macros (or press Alt+F8).

   The Macro dialog box lists available procedures, but your function won’t be in the list.

4. In the Macro Name box, type the name of your function.
5. Click the Options button to display the Macro Options dialog box.
6. In the Description box, enter the function description. The Shortcut Key field is irrelevant for functions.
7. Click OK and then click Cancel.

After you perform the preceding steps, the Insert Function dialog box displays the description that you entered in Step 6 when the function is selected.

Using Add-Ins to Store Custom Functions

You may prefer to store frequently used custom functions in an add-in file. A primary advantage is that you can use those functions in any workbook when the add-in is installed.

In addition, you can use the functions in formulas without a filename qualifier. Assume that you have a custom function named ZAPSPACES that is stored in Myfuncs.xlsm. To use this function in a formula in a workbook other than Myfuncs.xlsm, you need to enter the following formula:

=Myfuncs.xlsm!ZAPSPACES(A1)

If you create an add-in from Myfuncs.xlsm and the add-in is loaded, you can omit the file reference and enter a formula such as the following:

=ZAPSPACES(A1)

Using the Windows API

VBA can borrow methods from other files that have nothing to do with Excel or VBA — for example, the Dynamic Link Library (DLL) files that Windows and other software use. As a result, you can do things with VBA that would otherwise be outside the language’s scope.

The Windows Application Programming Interface (API) is a set of functions available to Windows programmers. When you call a Windows function from VBA, you’re accessing the Windows API. Many of the Windows resources used by Windows programmers are available in DLLs, which store programs and functions and are linked at runtime rather than at compile time.

Windows API examples

Before you can use a Windows API function, you must declare the function at the top of your code module. If the code module is for UserForm, Sheet, or ThisWorkbook, you must declare the API function as Private.

An API function must be declared precisely. The declaration statement tells VBA:

• Which API function you’re using
• In which library the API function is located
• The API function’s arguments

After you declare an API function, you can use it in your VBA code.

Determining the Windows directory

This section contains an example of an API function that displays the name of the Windows directory — something that’s not possible using standard VBA statements. This code works with Excel 2010 and later.

Here’s the API function declaration:

Declare PtrSafe Function GetWindowsDirectoryA Lib"kernel32" _
  (ByVal lpBuffer As String, ByVal nSize As Long) As Long

This function, which has two arguments, returns the name of the directory in which Windows is installed. After calling the function, the Windows directory is contained in lpBuffer, and the length of the directory string is contained in nSize.

After inserting the Declare statement at the top of your module, you can access the function by calling the GetWindowsDirectoryA function. The following is an example of calling the function and displaying the result in a message box:

Sub ShowWindowsDir()
    Dim WinPath As String * 255
    Dim WinDir As String
    WinPath = Space(255)
    WinDir = Left(WinPath, GetWindowsDirectoryA (WinPath, Len(WinPath)))
    MsgBox WinDir, vbInformation,"Windows Directory"
End Sub

Executing the ShowWindowsDir procedure displays a message box with the Windows directory.

Often, you’ll want to create a wrapper for API functions. In other words, you create your own function that uses the API function. This greatly simplifies using the API function. Here’s an example of a wrapper VBA function:

Function WINDOWSDIR() As String
'   Returns the Windows directory
    Dim WinPath As String * 255
    WinPath = Space(255)
    WINDOWSDIR=Left(WinPath,GetWindowsDirectoryA (WinPath,Len(WinPath)))
End Function

After declaring this function, you can call it from another procedure:

MsgBox WINDOWSDIR()

You can even use the function in a worksheet formula:

=WINDOWSDIR()

The reason for using API calls is to perform actions that would otherwise be impossible (or at least very difficult). If your application needs to find the path of the Windows directory, you could search all day and not find a function in Excel or VBA to do the trick. But knowing how to access the Windows API may solve your problem.

Detecting the Shift key

Here’s another example of using an API function. Suppose that you’ve written a VBA macro that will be executed by clicking a button on a worksheet. Furthermore, suppose that you want the macro to perform differently if the user presses the Shift key when the button is clicked. VBA doesn’t provide a way to detect whether the Shift key is pressed. But you can use the GetKeyState API function to find out. The GetKeyState function tells you whether a particular key is pressed. It takes a single argument, nVirtKey, which represents the code for the key in which you’re interested.

The following code demonstrates how to detect whether the Shift key is pressed when the Button_Click event-handler procedure is executed. Note that we define a constant for the Shift key (using a hexadecimal value) and then use this constant as the argument for GetKeyState. If GetKeyState returns a value less than zero, it means that the Shift key was pressed; otherwise, the Shift key wasn’t pressed. This code isn’t compatible with Excel 2007 and earlier versions.

Declare PtrSafe Function GetKeyState Lib"user32" _
  (ByVal nVirtKey As Long) As Integer
 
Sub Button_Click()
    Const VK_SHIFT As Integer = &H10
    If GetKeyState(VK_SHIFT) < 0 Then
        MsgBox"Shift is pressed"
    Else
        MsgBox"Shift is not pressed"
    End If
End Sub

Learning more about API functions

Working with the Windows API functions can be tricky. Many programming reference books list the declarations for common API calls and often provide examples. Usually, you can simply copy the declarations and use the functions without understanding the details. Many Excel programmers take a cookbook approach to API functions. The Internet has dozens of reliable examples that you can copy and paste. Or search the web for a file named Win32API_PtrSafe.txt. This file, from Microsoft, contains many examples of declaration statements.