Chapter 10: Creating Function Procedures

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 function is a VBA procedure that performs calculations and returns a value. You can use these functions in your Visual Basic for Applications (VBA) code or in 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 quite 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. As far as I know, the only exception is that you can't use a VBA function in a data validation formula.

I cover Sub procedures in the preceding chapter, and in this chapter, I discuss Function procedures.

Chapter 11 has many useful and practical examples of Function procedures. You can incorporate many of these techniques into your work.

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 2010 includes more than 400 predefined worksheet functions that you can use in formulas. If that's not enough, however, 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 very 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. I should also point out, however, that custom functions used in your formulas are usually much slower than built-in functions. And, of course, the user must enable macros in order 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 simply call the function from your procedure. A custom function can eliminate the need for duplicated code, thus reducing errors.

Also, coworkers often can benefit from your specialized functions. And some may be willing to pay you to create custom functions that save them time and work.

Although many cringe at the thought of creating custom worksheet functions, the process isn't difficult. In fact, I enjoy creating custom functions. I especially like how my custom functions appear in the Insert Function dialog box along with Excel built-in functions, as if I'm re-engineering the software in some way.

In this chapter, I tell you what you need to know to start creating custom functions, and I provide lots of examples.

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 one I've written, but it demonstrates some key concepts related to functions. I explain how this function works later, in the “Analyzing the custom function” section.

When you create custom functions that will be used in a worksheet formula, make sure that the code resides in a normal VBA module. If you place your custom functions in a code module for a UserForm, a Sheet, or ThisWorkbook, they won't work in your formulas. Your formulas will return a #NAME? error.

Using the function in a worksheet

When you enter a formula that uses the RemoveVowels function, Excel executes the code to get the value. Here's an example of how you'd use the function in a formula:

=RemoveVowels(A1)

See Figure 10-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 pretty much 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))

FIGURE 10-1: Using a custom function in a worksheet formula.

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

In the example shown in Figure 10-2, the string entered in response to the InputBox function was Excel 2010 Power Programming With VBA. The MsgBox function displays the text without vowels.

FIGURE 10-2: Using a custom function in a VBA procedure.

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

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

Notice that I use the function name as a variable here. When a function ends, it always returns the current value of the variable that corresponds to the function's name.

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 within 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 VBA'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 the 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, I used a string variable (TempString) to store the vowel-less string as it's being constructed. Then, before the procedure ends, I assigned the contents of TempString to the function's name. This version also uses a Select Case construct rather than an If-Then construct.

Both versions of this function are available on the companion CD-ROM. The file is named remove vowels.xlsm.

Function Procedures

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

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) Represents any valid Function procedure name, which must follow the same rules as a variable name.

• arglist: (Optional) Represents 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) Is the data type returned by the Function procedure.

• instructions: (Optional) Are any number of valid VBA instructions.

• Exit Function: (Optional) Is a statement that forces an immediate exit from the Function procedure prior to its completion.

• End Function: (Required) Is 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 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 within 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 like J21 as a function name, you can't use the function in a worksheet formula.

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

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.

From a procedure

You can call custom functions from a VBA procedure the same way that you call built-in functions. For example, after you define a function called SumArray, you can enter a statement like the following:

Total = SumArray(MyArray)

This statement executes the SumArray function with MyArray as its argument, returns the function's result, and assigns it to the Total variable.

You also can use the Run method of the Application object. Here's an example:

Total = Application.Run (“SumArray”, “MyArray”)

The first argument for the Run method is the function name. Subsequent arguments represent the argument(s) for the function. The arguments for the Run method can be literal strings (as shown above), numbers, or variables.

In a worksheet formula

Using custom functions in a worksheet formula is like using built-in functions except that you must ensure that Excel can locate the Function procedure. If the Function procedure is in the same workbook, you don't have to do anything special. If it's in a different workbook, you may have to tell Excel where to find it.

You can do so in three ways:

• Precede the function name with a file reference. For example, if you want to use a function called CountNames that's defined in an open workbook named Myfuncs.xlsm, you can use the following reference:

=Myfuncs.xlsm!CountNames(A1:A1000)

If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically.

• Set up a reference to the workbook. You do so by choosing the VBE Tools⇒References command. If the function is defined in a referenced workbook, you don't need to use the worksheet name. Even when the dependent workbook is assigned as a reference, the Paste Function dialog box continues to insert the workbook reference (although it's not necessary).

• Create an add-in. When you create an add-in from a workbook that has Function procedures, you don't need to use the file reference when you use one of the functions in a formula. The add-in must be installed, however. I discuss add-ins in Chapter 21.

You'll notice that unlike Sub procedures, your Function procedures don't appear in the Macro dialog box when you issue the Developer⇒Code⇒Macros command. In addition, you can't choose a function when you issue the VBE Run⇒Sub/UserForm command (or press F5) if the cursor is located in a Function procedure. (You get the Macro dialog box that lets you choose a macro to run.) As a result, you need to do a bit of extra up-front work to test your functions while you're developing them. One approach is to set up a simple procedure that calls the function. If the function is designed to be used in worksheet formulas, you'll want to enter a simple formula to test it.

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. Figure 10-3 shows the New Formatting Rule dialog box, specifying a custom function in a formula.

FIGURE 10-3: Using a custom VBA function for conditional formatting.

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 purposes. Figure 10-4 shows an example.

FIGURE 10-4: Calling a Function procedure from the Immediate window.

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.

If your formula uses a custom worksheet function and it returns #VALUE!, your function has an error. The error may be caused by logical errors in your code or by passing incorrect arguments to the function. See “Debugging Functions,” later in this chapter.

Function Examples

In this section, I present a series of examples that demonstrate how to use arguments effectively with functions. By the way, this discussion also applies 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.

A workbook that contains these functions is available on the companion CD-ROM. The file is named no argument.xlsm.

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 name 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 (assuming that it's listed properly in the Registry):

=User()

When you use a function with no arguments in a worksheet formula, you must include a set of empty parentheses. This requirement isn't necessary if you call the function in a VBA procedure, although including the empty parentheses does make it clear that you're calling a function.

To use this function in another procedure, you can assign it to a variable, use it in an expression, or use it as an argument for another function.

The following example calls the User function and uses the return value as an argument for the MsgBox statement. The concatenation operator (&) joins the literal string with the result of the User function.

Sub ShowUser()

MsgBox “Your name is “ & User()

End Sub

The User function demonstrates how you can create a wrapper function that simply 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

Here's another example of a function that doesn't take an argument. I used to use Excel's RAND function to quickly fill a range of cells with values. But I didn't like the fact that the random numbers changed whenever the worksheet was recalculated. So I remedied this issue by converting the formulas to values.

Then I realized that I could create a custom function that returned random numbers that didn't change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1. The custom function is as 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:

Monthly Sales	Commission Rate
0–$9,999	8.0%
$10,000–$19,999	10.5%
$20,000–$39,999	12.0%
$40,000+	14.0%

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 into 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 1000 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 exact 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 rather crude. Following is an enhanced version that displays formatted values and keeps looping until the user clicks No (see Figure 10-5).

FIGURE 10-5: Using a function to display the result of a calculation.

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”))

‘ 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, comma, and two decimal places).

In both of these 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.

I 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 1000 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, eh? I 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)

All these commission-related procedures are available on the companion CD-ROM in a file named commission functions.xlsm.

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

Notice 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 absolutely no advantages over the Excel SUM function.

This example, named array argument.xlsm, is available on the companion CD-ROM.

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

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

=User()

=User(False)

=User(True)

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. In other words, the argument for the IsMissing function must always be a Variant data type.

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

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

This function is available on the companion CD-ROM. The filename is draw.xlsm.

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.

See Chapter 3 for more details on array formulas.

It's important to understand that the array returned by the Array function isn't the same as a normal array that's made up of elements of the Variant data type. In other words, a variant array isn't the same as an array of variants.

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

Notice that I 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 within 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, it returns the month name that corresponds to the argument value.

This procedure uses the Mod operator to determine the month value. The Mod operator returns the remainder after dividing the first operand by the second. Keep in mind that the AllNames array is zero-based and that indices range from 0 to 11. In the statement that uses the Mod operator, 1 is subtracted from the function's argument. Therefore, an argument of 13 returns 0 (corresponding to Jan), and an argument of 24 returns 11 (corresponding to Dec).

You can use this function in a number of ways, as illustrated in Figure 10-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)}

FIGURE 10-6: Different ways of passing an array or a single value to a worksheet.

Range F3 contains this (nonarray) formula:

=MonthNames(3)

To enter an array formula, you must press Ctrl+Shift+Enter (and don't enter the curly braces).

The lower bound of an array, created using the Array function, is determined by the lower bound specified with the Option Base statement at the top of the module. If there is no Option Base statement, the default lower bound is 0.

A workbook that demonstrates the MonthNames function is available on the companion CD-ROM. The file is named month names.xslm.

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

Notice that I also changed the data type for the function's return value. Because the function can now return something other than a string, I changed the data type to Variant.

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.

ParamArray can apply only to the last argument in the procedure's argument list. It's always a Variant data type, and it's always an optional argument (although you don't use the Optional keyword).

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 contains a non-value, or if you use a literal value for an argument.

Emulating Excel's SUM function

In this section, I present a custom function called MySum. Unlike the SimpleSum function listed in the previous 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, in fact, very 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 10-1) handles all these argument types.

A workbook containing the MySum function is available on the companion CD-ROM. The file is named mysum function.xlsm.

Listing 10-1: MySum Function

Function MySum(ParamArray args() As Variant) As Variant

‘ Emulates Excel's SUM function

‘ Variable declarations

Dim i As Variant

Dim TempRange As Range, cell As Range

Dim ECode As String

Dim m, n

MySum = 0

‘ Process each argument

For i = 0 To UBound(args)

‘ Skip missing arguments

If Not IsMissing(args(i)) Then

‘ What type of argument is it?

Select Case TypeName(args(i))

Case “Range”

‘ Create temp range to handle full row or column ranges

Set TempRange = Intersect(args(i).Parent.UsedRange, _

args(i))

For Each cell In TempRange

If IsError(cell) Then

MySum = cell ‘ return the error

Exit Function

End If

If cell = True Or cell = False Then

MySum = MySum + 0

Else

If IsNumeric(cell) Or IsDate(cell) Then _

MySum = MySum + cell

End If

Next cell

Case “Variant()”

n = args(i)

For m = LBound(n) To UBound(n)

MySum = MySum(MySum, n(m)) ‘recursive call

Next m

Case “Null” ‘ignore it

Case “Error” ‘return the error

MySum = args(i)

Exit Function

Case “Boolean”

‘ Check for literal TRUE and compensate

If args(i) = “True” Then MySum = MySum + 1

Case “Date”

MySum = MySum + args(i)

Case Else

MySum = MySum + args(i)

End Select

End If

Next i

End Function

Figure 10-7 shows a workbook with various formulas that use SUM and MySum. As you can see, the functions return identical results.

FIGURE 10-7: Comparing SUM with MySum.

If you're interested in learning how this 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 “Debugging Functions,” later in this chapter.) Try this for several different argument types, and you'll soon have a good feel for how this 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 VBA's 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 is 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. (VBA's 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. 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 recalculates instantly. After I replace the SUM functions with MySum functions, it takes about eight 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.

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.

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

Beware of Calendar changes. Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

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

FIGURE 10-8: The Extended Date functions used in formulas.

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: A four-digit year in the range 0100 to 9999

• m: A month number (1–12)

• d: 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, it rolls over into the next year or month. For example, if you specify a month of 13, it's interpreted as January of the next year.

The VBA code for the Extended Data functions is available on the companion CD-ROM. The filename is extended date function.xlsm. The CD also contains some documentation for these functions in a Word document named extended date functions help.docx.

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!). Luckily, 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, or message boxes will appear for each formula that is evaluated, which is a repetition that 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 the Debugger.

• Set a breakpoint in the function and then step through the function. You then can access all the 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 of 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 10-9 shows the result when the function has an argument of Tucson Arizona.

FIGURE 10-9: Use the Immediate window to display results while a function is running.

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. Figure 10-10 shows both of these dialog boxes.

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.

FIGURE 10-10: The Insert Function dialog box makes it easy to enter a function into a formula, and the Function Arguments dialog box prompts for the 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.

Custom Function procedures defined with the Private keyword don't appear in the Insert Function dialog box. If you develop a function that's intended to be used only in your other VBA procedures, you should declare it by using the Private keyword. However, declaring the function as Private doesn't prevent it from being used in a worksheet formula. It just prevents the function from displaying in the Insert Function dialog box.

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 ‘Lookup & Reference

Arg1Desc = “The range that contains the values”

Arg2Desc = “(Optional) If False or missing, a new cell is not “

Arg2Desc = Arg2Desc & “selected when recalculated. If True, a “

Arg2Desc = Arg2Desc & “new cell is selected when recalculated.”

Application.MacroOptions _

Macro:=FuncName, _

Description:=FuncDesc, _

Category:=FuncCat, _

ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc) End Sub

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

The ability to provide argument descriptions is new to Excel 2010. If the workbook is opened in an early version of Excel, however, the arguments won't display the descriptions.

Figure 10-11 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 example, if you don't need the arguments to have descriptions, just omit the ArgumentDescriptions argument.

For information on creating a custom help topic accessible from the Insert Function dialog box, refer to Chapter 24.

FIGURE 10-11: The Insert Function and Function Arguments dialog boxes for a custom function.

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 10-1 lists the category numbers that you can use for the Category argument for the MacroOptions method. Notice that 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.

Table 10-1: Function Categories

Category Number	Category Name
0	All (no specific category)
1	Financial
2	Date & Time
3	Math & Trig
4	Statistical
5	Lookup & Reference
6	Database
7	Text
8	Logical
9	Information
12	Macro Control
13	DDE/External
14	User Defined
15	Engineering
16	Cube
17	Compatibility*

*The Compatibility category is new to Excel 2010.

You can also create custom function categories. Instead of using a number for the Category argument for MacroOptions, use a text string. The statement that follows creates a new function category named VBA Functions, and assigns the Commission function to this category:

Application.MacroOptions Macro:=”Commission”, Category:=”VBA Functions”

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.

If you don't provide a description for your custom function, the Insert Function dialog box displays the following text: No help available.

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

1. Create your function in the 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 functions won't be in the list.

4. Type the name of your function in the Macro Name box.

5. Click the Options button to display the Macro Options dialog box.

6. Enter the function description in the Description box (see Figure 10-12).

The Shortcut Key field is irrelevant for functions.

FIGURE 10-12: Provide a function description in the Macro Options dialog box.

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.

When you use the Insert Function dialog box to enter a function, the Function Arguments dialog box is displayed after you click OK. For built-in functions, the Function Arguments dialog box displays a description for each of the function's arguments. Unfortunately, you can't provide such descriptions for custom function arguments.

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.

In addition, you can use the functions in formulas without a filename qualifier. Assume that you have a custom function named ZapSpaces, and that it's 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:C12)

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:C12)

I discuss add-ins in Chapter 21.

A potential problem with using add-ins to store custom functions is that your workbook is dependent on the add-in file. If you need to share your workbook with a colleague, you also need to share a copy of the add-in that contains the functions.

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 a 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 won't work with Excel 2007.

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()

This example is available on the companion CD-ROM. The filename is windows directory.xlsm, and the API function declaration is compatible with Excel 2007 and later.

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.

When you work with API calls, system crashes during testing aren't uncommon, so save your work often.

Detecting the Shift key

Here's another example: 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 that you're interested in.

The following code demonstrates how to detect whether the Shift key is pressed when the Button_Click event handler procedure is executed. Notice that I 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.

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

A workbook named key press.xlsm on the companion CD-ROM demonstrates how to detect the following keys (as well as any combinations): Ctrl, Shift, and Alt. The declarations in this file aren't compatibile with 64-bit Excel. The API function declaration in this workbook is compatible with Excel 2007 and later.

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 really understanding the details. In reality (at least the reality that I've seen), most Excel programmers take a cookbook approach to API functions. The Internet has dozens of examples that you can copy and paste and that work quite reliably.

Chapter 11 has several additional examples of using Windows API functions.

The companion CD-ROM includes a file named win32api.txt, which is a text file that contains Windows API declarations and constants. You can open this file with a text editor and copy the appropriate declarations to a VBA module.