Chapter 9

Using Built-in Functions

VBA comes with a large number of built-in functions that perform commonly needed operations—everything from determining whether a file exists to returning the current date and converting data from one format to another. (For example, you can use a function to convert numeric data into a text string.)

This chapter demonstrates what functions are, what they do, and how to use them. Along the way, you'll get to know some of the key functions built into VBA—including functions that convert data from one data type to another, functions that manage file operations, functions that do math, and many others.

You can also create custom functions of your own to supplement VBA's built-in functions. The next chapter tells you how to build your own when VBA's functions don't meet your needs.

In this chapter you will learn to do the following:

• Understand what functions are and what they do
• Use functions
• Use key VBA functions
• Convert data from one type to another
• Manipulate strings and dates

What Is a Function?

A function is a type of procedure. A function differs from a subroutine (subprocedure) in that a function always returns a value and a subroutine doesn't. And in common practice, a function almost always takes one or more arguments. Although subroutines can be written to take arguments, most programmers don't write their code this way.

So, to sum up, here are the key difference between functions and subroutines:

You'll often use functions that are built into VBA. Typically, you feed information into a built-in function by sending it arguments. The built-in function then processes that info and returns a value for you to use. But you can also create your own functions in the Code window if you wish.

Built-in functions are so essential to VBA that you've already used several in examples in this book. However, we'll now explore them more fully. For example, in Chapter 7, “Using Array Variables,” you used the Rnd function to generate random numbers to fill an array named intArray, and the Int function to turn the random numbers into integers:

intArray(i) = Int(Rnd * 10)

Rnd is one of the rare functions that does not have to take one or more arguments. (Rnd can take one optional argument, but the previous example doesn't use it.)

The Int function, on the other hand, requires an argument—the number or expression that it's turning into an integer. The argument in this example is supplied by the expression Rnd * 10. Here the Rnd function returns a value that the Int function uses; the Int function then returns a value to the procedure, which uses it to populate a subscript in the array.

An argument is a piece of information that gets passed to a function. (Arguments are also passed to methods and other commands.) You can tell when arguments are optional in Help descriptions because they're shown enclosed within brackets. When they are optional, you can either provide or omit the arguments displayed in the brackets. For example, the full Help syntax for the Rnd function looks like this:

Rnd([number]) As Single

The brackets indicate that the number argument is optional, and the As Single part of the syntax denotes that the value returned by the function will be of the Single data type.

Different functions return different data types suited to their job: Many functions return a Variant, but yes/no functions, such as the IsNumeric function used in Chapter 7, return a Boolean value, either True or False. When necessary, VBA may even sometimes convert the result of a function to a different data type needed by another function in the expression.

If any pair of brackets contains two arguments, you have to use both of them at once (blessedly, this is quite rare). For example, the MsgBox function displays a message box. The syntax for the MsgBox function is as follows:

MsgBox(prompt[, buttons] [, title][, helpfile, context])

Here, prompt is the only required argument: buttons, title, helpfile, and context are all optional. But notice that helpfile and context are enclosed within a single set of brackets instead of each having its own pair, meaning that you need to use either both of these arguments or neither of them; you cannot use one without the other. Chapter 13, “Getting User Input with Message Boxes and Input Boxes,” shows you how to use the MsgBox function in your code.

Using Functions

To use a function, you call it (or invoke it) from a procedure—either a subprocedure (Sub) or from another function (Function).

To call a function, you can use a call statement, either with the optional Call keyword or by just using the name of the function. Using the Call keyword allows you to search through all calls in your project by searching for “call” (call followed by a space). However, using the Call keyword is overkill for everyday functions; programmers rarely use it.

The syntax for the Call statement is as follows:

[Call] name [argumentlist]

Here, name is a required String argument giving the name of the function or procedure to call, and argumentlist is an optional argument providing a comma-delimited list of the variables, arrays, or expressions to pass to the function or procedure. When calling a function, you'll almost always need to pass arguments (except for those few functions that take no arguments).

The brackets around the Call keyword indicate that it is optional. If you do use this keyword, you need to enclose the argumentlist argument in parentheses. In most cases, it's easier to read the code if you don't use the Call keyword when calling a function.

For example, the following statement calls the MsgBox function, supplying the required argument prompt (in this example, it's the string Hello, World!):

MsgBox "Hello, World!"

You could use the Call keyword instead, as shown in the following statement, but there's little advantage in doing so:

Call MsgBox "Hello, World!"

Note that the MsgBox function is one of the few with which you can omit the parentheses around the argument list.

You can assign to a variable the result returned by a function. For example, consider the following code fragment. The first two of the following statements declare the String variables strExample and strLeft10. The third statement assigns a string of text to strExample. The fourth statement uses the Left function to return the leftmost 10 characters from strExample and assign them to strLeft10, which the fifth statement then displays in a message box (see Figure 9.1):

Dim strExample As String
Dim strLeft10 As String
strExample = "Technology is interesting."
strLeft10 = Left(strExample, 10)
MsgBox strLeft10

FIGURE 9.1 Using the Left function to take the left part of a string—in this case, the first 10 characters of the string

If you prefer, you can assign the result of each function to a variable, as in this next example. Here the first string variable, str1, is assigned the leftmost 13 characters from the string This is Pride and Patriotism. So after its code line executes, str1 holds the value This is Pride. Then str2 is assigned the rightmost 5 characters from str1, resulting in Pride.

Dim str1 As String
Dim str2 As String

str1 = Left("This is Pride and Patriotism", 13)
str2 = Right(str1, 5)

MsgBox str2

However, after you become accustomed to working with functions, you can collapse them in various ways in your code. Instead of assigning the result of a function to a variable, you can insert it directly in your code or pass it (as an argument) to another function. This is a common shortcut. Take a look at the following statement. It does the same thing as the previous example but collapses the code into one line, avoiding the use of variables altogether:

MsgBox Right(Left("This is Pride and Patriotism", 13), 5)

This statement uses three functions: the MsgBox function, the Left function, and the Right function. (The Right function is the counterpart of the Left function and returns the specified number of characters from the right side of the specified string.)

When you have multiple sets of parentheses in a VBA statement, the code is executed starting from the innermost pair of parentheses and working outward. This is the same way that nested parentheses are handled in math.

So, in the previous example the Left function is evaluated first, returning the leftmost 13 characters from the string: This is Pride (the spaces are characters too). VBA passes this new string to the Right function, which in this case returns the rightmost five characters from it: Pride. VBA then passes this second new string to the MsgBox function, which displays it in a message box.

Passing Arguments to a Function

When a function takes more than one argument, you can pass the arguments to it in any of three ways:

• By supplying the argument values, without their names, positionally (in the order in which the function expects them)
• By supplying the arguments, with their names, in the order in which the function expects them
• By supplying the arguments, with their names, in any order you choose

The first method, supplying the arguments positionally without using their names, is usually the quickest way to proceed. The only disadvantage to doing so is that anyone reading your code may not know immediately which value corresponds to which argument—though they can look this up without trouble. To omit an optional argument, you place a comma where it would appear in the sequence of arguments.

It does take extra time to type in argument names, but it makes your code easier to read. And when you omit an argument from a named argument list, you don't need to use the comma to indicate that you're skipping it.

There's no advantage to using named arguments out of order over using them in order unless you happen to find doing so easier.

For example, the DateSerial function returns a Variant/Date containing the date for the given year, month, and day. The syntax for DateSerial is as follows:

DateSerial(year, month, day)

Here, year is a required Integer argument supplying the year, month is a required Integer argument supplying the month, and day is a required Integer argument supplying the day.

The following statement supplies the arguments positionally without their names:

MsgBox DateSerial(2010, 12, 31)

This statement is equivalent but supplies the arguments positionally with their names:

MsgBox DateSerial(Year:=2010, Month:=12, Day:=31)

The following statement supplies the arguments, with their names, out of order:

MsgBox DateSerial(Day:=31, Year:=2010, Month:=12)

All three of these statements work fine and achieve the same result. You'll cause a problem only if you list out-of-order arguments that you're supplying without names (positionally), if you name some arguments and don't name others, or if you omit required arguments. Figure 9.2 shows one of the errors you may encounter. In this case, I left out the required month argument.

FIGURE 9.2 An “Argument not optional” error occurs when you omit a required argument.

Using Functions to Convert Data

Most data-type conversion isn't frequently needed in VBA, but you might as well at least understand what it does. Some computer languages are pretty strict about requiring explicit data typing (sometimes called strong data typing). And there are a few specialized situations where you will need to convert one variable type into another. For example, you might be using the InputBox command to get some information from the user. The user is typing on a keyboard, so all the data they input will be characters (text string) data. But if your macro needs to do any math with this input, such as using the + command to add numbers, you must first convert the string data into numeric variables (or use the default Variant type). To convert a string to an integer number, you could use the Cint command. This same problem arises if you are importing data from another source, such as a database that stores everything as a string variable.

VBA provides a full set of simple functions for converting data from one data type to another. Table 9.1 lists VBA's functions for simple data conversion.

For example, the following statements declare the untyped variable varMyInput and the Integer variable intMyVar and then display an input box prompting the user to enter an integer. In the third statement, the user's input is assigned to varMyInput, which automatically becomes a Variant/String. The fourth statement uses the CInt function to convert varMyInput to an integer, assigning the result to intMyVar. The fifth statement compares intMyVar to 10, converts the result to Boolean by using the CBool function, and displays the result (True or False) in a message box.

Dim varMyInput
Dim intMyVar As Integer
varMyInput = InputBox("Enter an integer:", "10 Is True, Other Numbers Are False")
intMyVar = CInt(varMyInput)
MsgBox CBool(intMyVar = 10)

Recall that a Boolean variable is only either True or False. So in the final line of this example, you're saying in effect, “If the value in the variable intMyVar is 10, the Boolean result will be True. If the value is anything other than 10, the result will be False.”

VBA also has a set of functions that manipulate data in more complicated ways. Only two of these more complex manipulation functions—Format and Chr—are used much in VBA programming, so we'll explore them in depth in this chapter.

Table 9.2 lists VBA's functions for more complex data manipulation.

Using the Asc Function to Return a Character Code

This function isn't used much. Asc tells you which numeric value has been assigned to a particular letter according to the ANSI character code that's used in Windows. A character code is a list of numbers by which computers refer to letters of the alphabet. For example, the character code used in Windows for a capital A is 65 and for a capital B is 66; a lowercase a is 97, and a lowercase b is 98.

The syntax for the Asc function is straightforward:

Asc(string)

Here, string is any string expression. For example, Asc("A") returns 65.

The following statements use the Asc function to return the character code for the first character of the current selection in the active document and display that code in a message box:

strThisCharacter = Asc(Selection.Text)
MsgBox strThisCharacter, vbOKOnly, "Character Code"

Using the Val Function to Extract a Number from the Start of a String

The Val function, like Asc, is not much used. But for completeness, I've included it. The Val function converts the numbers contained in a text string into a numeric value. Val follows these rules:

• It reads only numbers in a string.
• It starts at the beginning of the string and reads only as far as the string contains characters that it recognizes as numbers (digits).
• It ignores tabs, line feeds, and blank spaces.
• It recognizes the period as a decimal separator, but not the comma.

This means that if you feed Val a string consisting of tabbed columns of numbers, such as the second line here, it will read them as a single number (in this case, 445634.994711):

Item#   Price   Available   On Order   Ordered
 4456   34.99       4          7         11

If, however, you feed it something containing a mix of numbers and letters, Val will read only the numbers and strings recognized as numeric expressions (for example, Val("4E5") returns 400000 because it reads the expression as exponentiation). For example, if fed the address shown in the next example, Val returns 8661, ignoring the other numbers in the string (because it stops at the L of Laurel, the first character that isn't a number, a tab, a line feed, or a space):

8661 Laurel Avenue Suite 3806, Oakland, CA 94610

The syntax for Val is straightforward:

Val(string)

Here, string is a required argument consisting of any string expression.

The following statement uses Val to return the numeric variable StreetNumber from the string Address1:

StreetNumber = Val(Address1)

Dim StrVar As String
StrVar = "12,000"
MsgBox "Val = " & Val(StrVar) & " CInt = " & CInt(StrVar)

Using the Str Function to Convert a Number into a String

Just as you can use CInt to convert a text string into a numeric value as described in the previous section, you can also convert a numeric value to a string with the Str function. But you should use the newer CStr function rather than the Str function, for the same reasons that CInt is superior to the older Val command.

You'll need to convert a number to a string when you want to concatenate the information contained in a value with a string. Concatenation means appending one string to another, as in "123" & "654", which results in the text "123654".

Concatenation cannot be accomplished by simply using the + operator because VBA would attempt to perform the mathematical operation addition rather than the string operation you want: concatenation.

A text string is just that: text. It's one or more alphanumeric characters, such as “55”—and that's quite different from the number 55. You can't concatenate “55” and 55. They're not the same kind of data at all.

Here's an example. Suppose you've declared a String variable named strYourAge and a numeric variable named intAge. You can't use a strYourAge + intAge statement to concatenate them because they're different data types. You first need to create a string from the intAge variable and then concatenate that string with the strYourAge string. (Alternatively, you can use the & operator to concatenate the two variables.)

To convert a value to a string, use the CInt function. The syntax for the CInt function is this:

CInt(number)

Here, number is a variable containing a numeric expression (such as an Integer data type, a Long data type, or a Double data type).

The following short procedure provides an example of converting a value to a string:

Sub Age()
    Dim intAge As Integer, strYourAge As String
    intAge = InputBox("Enter your age:", "Age")
    strYourAge = "Your age is " & Clnt(intAge) & "."
    MsgBox strYourAge, vbOKOnly + vbInformation, "Age"
End Sub

Dim intAge As Integer
Dim strYourAge As String

Using the Format Function to Format an Expression

The Format function is a powerful tool for changing numbers, dates and times, and strings into a format that you prefer.

The syntax for the Format function is as follows:

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

These are the components of the syntax:

• expression is any valid expression.
• format is an optional argument specifying a named format expression or a user-defined format expression. More on this in a moment.
• firstdayofweek is an optional constant specifying the day that starts the week (for date information): The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; uses the system setting).
• firstweekofyear is an optional constant specifying the week considered first in the year (again, for date information), as shown in Table 9.3.

You can define your own formats for the Format function as described in the following sections if none of the predefined numeric formats (described next) suit your needs.

USING PREDEFINED NUMERIC FORMATS

Table 9.4 lists the predefined numeric formats that you can use with the Format function.

For example, the following statement returns $123.45:

Format("12345", "Currency")

CREATING A NUMERIC FORMAT

If none of the predefined numeric formats suit your needs, you can create your own numeric formats by using your choice of a combination of the characters listed in Table 9.5.

For example, the following statement returns a currency formatted with four decimal places:

Format("123456", "$00.0000")

CREATING A DATE OR TIME FORMAT

Similarly, you can create your own date and time formats by mixing and matching the characters listed in Table 9.6.

For example, the following statement returns Saturday, April 01, 2010:

Format(#4/1/2010#, "dddddd")

CREATING A STRING FORMAT

The Format function also lets you create custom string formats using the options shown in Table 9.7.

For example, the following statement assigns to strUser a string consisting of four spaces if there is no input in the input box:

strUser = Format(InputBox("Enter your name:"), "@@@@")

Using the Chr Function and Constants to Enter Special Characters in a String

To insert special characters (such as a carriage return or a tab) into a string, specify the built-in constant (for those special characters that have built-in constants defined) or enter the appropriate character code using the Chr function. The syntax for the Chr function is straightforward:

Chr(charactercode)

Here, charactercode is a number that identifies the special character you want to add.

Table 9.8 lists the most useful character codes and character constants.

Here's a practical example exploiting the Chr function. Say you wanted to build a string containing a person's name and address from individual strings containing items of that information. You also wanted the individual items separated by tabs in the resulting string so that you could insert the string into a document and then easily convert it into a table.

To do this, you could use the following code:

Sub FormatTabular()

Dim i As Integer
Dim strFirstName As String

Dim strLastName As String
Dim strAddress As String
Dim strCity As String
Dim strState As String
Dim strAllInfo As String

strFirstName = "Phil"
strLastName = "Mortuqye"
strAddress = "12 Batwing Dr."
strCity = "Tulsa"
strState = "OK"

    strAllInfo = strFirstName & vbTab & strLastName _
        & vbTab & strAddress & vbTab & strCity _
        & vbTab & strState & vbCr

    Selection.TypeText strAllInfo
End Sub

String variables are assigned to the string strAllInfo by concatenating the strings strFirstName, strLastName, and so on with tabs—vbTab characters—between them. The final character added to the built string is vbCr (a carriage-return character), which creates a new paragraph.

The final line enters the strAllInfo string into the current document, thus building a tab-delimited list containing the names and addresses. This list can then be easily converted into a table whose columns each contain one item of information: The first column contains the strFirstName string, the second column the strLastName string, and so on.

Using Functions to Manipulate Strings

String variables are often useful for holding text. You can use them to store any quantity of text, from a character or two up to a large number of pages from a Word document or other text document. You can also use strings to store specialized information, such as filenames and folder names. Once you've stored text in a string, you can manipulate it according to your needs.

Table 9.9 lists VBA's built-in functions for manipulating strings. Because many of these functions are useful, and some are complex, you'll find detailed examples after the table.

Using the Left, Right, and Mid Functions to Return Part of a String

Frequently, you'll need to use only part of a string in your macros. For example, you might want to take only the first three characters of the name of a city to create a location code.

VBA provides several functions for returning from strings the characters you need:

• The Left function returns a specified number of characters from the left end of the string.
• The Right function returns a specified number of characters from the right end of the string.
• The Mid function returns a specified number of characters starting from a specified location inside a string.

USING THE LEFT FUNCTION

The Left function returns the specified number of characters from the left end of a string. The syntax for the Left function is as follows:

Left(string, length)

Here, the string argument is any string expression—that is, any expression that returns a sequence of contiguous characters. Left returns Null if string contains no data. The length argument is a numeric expression specifying the number of characters to return. length can be a straightforward number (such as 4, or 7, or 11) or it can be an expression that results in a number. For example, if the length of a word were stored in the variable named LenWord and you wanted to return two characters fewer than LenWord, you could specify the expression LenWord - 2 as the length argument; to return three characters more than LenWord, you could specify LenWord + 3 as the length argument.

One way to use the Left function would be to separate the area code from a telephone number that was provided as an unseparated 10-digit number from a database. In the following statements, the telephone number is stored in the String variable strPhone, which the code assumes was created earlier:

Dim strArea As String
strArea = Left(strPhone, 3)

These statements create the variable Area and fill it with the leftmost three characters of the variable strPhone.

USING THE RIGHT FUNCTION

The Right function is the mirror image of the Left function. Right returns a specified number of characters from the right end of a string. The syntax for the Right function is as follows:

Right(string, length)

Again, the string argument is any string expression, and length is a numeric expression specifying the number of characters to return. And, again, Right returns Null if string contains no data, and length can be a number or an expression that results in a number.

To continue the previous example, you could use the Right function to separate the last seven digits of the phone number stored in the string strPhone from the area code:

Dim strLocalNumber As String
strLocalNumber = Right(strPhone, 7)

These statements create the variable strLocalNumber and fill it with the rightmost seven characters from the variable strPhone.

USING THE MID FUNCTION

The Left and Right functions extract a substring from the left or right side of a string. The Mid function fetches a substring out of the middle of a string.

The Mid function returns the specified number of characters from inside the given string. You specify a starting position in the string and the number of characters (to the right of the starting position) that you want extracted.

The syntax for the Mid function is as follows:

Mid(string, start[, length])

Here are the elements of the syntax:

• As in Left and Right, the string argument is any string expression. Mid returns Null if string contains no data.
• start is a numeric value specifying the character position in string at which to start the length selection. If start is larger than the number of characters in string, VBA returns a zero-length string. In code, an empty string is typed as two quotation marks with nothing inside: strState = "".
• length is an optional numeric expression specifying the number of characters to return. If you omit length or use a length argument greater than the number of characters in string, VBA returns all the characters from the start position to the end of string. length can be an ordinary literal number or an expression that results in a number.

Using the phone-number example, you could employ Mid to pluck the local exchange code out from within a 10-digit phone number (for instance, extract the 555 from 5105551212), like this:

Dim strPhone As String

strPhone = "5105551212"
MsgBox Mid(strPhone, 4, 3)

This statement displays three characters in the variable strPhone, starting at the fourth character.

We've seen how to extract a substring using Mid. But this function has another use as well. You can also use Mid to find the location of a character within a string. In the following snippet, the Do Until… Loop walks backward through the string strFilename (which contains the FullName property of the template attached to the active document in Word) until it reaches the first backslash (), storing the resulting character position in the Integer variable intLen. The message box then displays that part of strFilename to the right of the backslash (determined by subtracting intLen from the length of strFilename)—the name of the attached template without its path:

Dim strFilename As String, intLen As Integer
strFilename = ActiveDocument.AttachedTemplate.FullName
MsgBox strFilename

intLen = Len(strFilename)
Do Until Mid(strFilename, intLen, 1) = ""
    intLen = intLen - 1
Loop
MsgBox Right(strFilename, Len(strFilename) - intLen)

This example is more illustrative than realistic for two reasons: First, you can get the name of the template more easily by just using the Name property rather than the FullName property. Second, there's a function called InStrRev (discussed next) that returns the position of one string within another by walking backward through it.

Using InStr and InStrRev to Find a String within Another String

You can use the Mid function to find an individual character within a string, but what if you need to find a set of characters within a string? The InStr function is designed to find one string within another string. For example, you could check, say, the current paragraph to see if it contained a particular word. If it did, you could take action accordingly—for instance, replacing that word with another word or selecting the paragraph for inclusion in another document. Maybe your company has changed its name and you need to do a search and replace in a large number of document templates. Or something.

The InStrRev function is the counterpart of the InStr function, working in a similar way but in the reverse direction.

The syntax for InStr is as follows:

InStr([start, ]string1, string2[, compare])

Here are the arguments:

• start is an optional argument specifying the starting position in the first string, string1. If you omit start, VBA starts the search at the first character in string1 (which is usually where you want to start). However, you do need to use start when you use the compare argument to specify the type of string comparison to perform.
• string1 is a required argument specifying the string expression in which to search for string2.
• string2 is a required argument specifying the string expression for which to search in string1.
• compare is an optional argument specifying the type of string comparison you want to perform. Text can be compared two ways: a binary comparison, which is case sensitive, or a textual comparison, which is not case sensitive. The default is a binary comparison, which you can specify by using the constant vbBinaryCompare or the value 0 for compare. Although specifying this value isn't necessary (because it's the default), you might want to include it to make your code ultra-clear. To specify a textual, case-insensitive comparison, use the constant vbTextCompare or the value 1 for compare.

Another way to use InStr is to find the location of a certain string within another string so that you can then change that substring. You might want to do this if you needed to move a file from its current position in a particular folder or subfolder to another folder that had a similar subfolder structure. For instance, suppose you work with documents stored in a variety of subfolders beneath a folder named In (such as z:DocumentsIn), and after you're finished with them, you save them in corresponding subfolders beneath a folder named Out (z:DocumentsOut). The short procedure shown in Listing 9.1 automatically saves the documents in the Out subfolder.

1.  Sub Save_in_Out_Folder()
2.      Dim strOName As String, strNName As String, _
            intToChange As Integer
3.      strOName = ActiveDocument.FullName
4.      intToChange = InStr(strOName, "In")
5.      strNName = Left(strOName, intToChange - 1) & "Out" _
            & Right(strOName, Len(strOName) - intToChange - 3)
6.      ActiveDocument.SaveAs strNName
7.  End Sub

The code in Listing 9.1 works as follows:

• Line 1 begins the procedure, and line 7 ends it.
• Line 2 declares the String variable strOName (as in original name), the String variable strNName (as in new name), and the Integer variable intToChange. Line 3 then assigns strOName the FullName property of the ActiveDocument object: the full name of the active document, including the path to the document (for example, z:DocumentsInLettersMy Letter.docm).
• Line 4 assigns to the variable intToChange the value of the InStr function that finds the string In in the variable strOName. Using the example path from the previous paragraph, intToChange will be assigned the value 13 because the 1st character of the In string is the 13th character in the strOName string.
• Line 5 assigns to the variable strNName the new filename created in the main part of the statement. This breaks down as follows:
  • Left(strOName, intToChange - 1) takes the left section of the strOName string, returning the number of characters specified by intToChange - 1—the number stored in intToChange minus one.
  • & "Out" adds to the partial string specified in the previous bullet item (to continue the previous example, z:Documents) the characters Out, which effectively replace the In characters, thus changing the directory name (z:DocumentsOut).
  • & Right(strOName, Len(strOName) - intToChange - 3) completes the partial string by adding the right section of the strOName string, starting from after the In string (LettersMy Letter.docm), giving z:DocumentsOutLettersMy Letter.docm. The number of characters to take from the right section is determined by subtracting the value stored in intToChange from the length of strOName and then subtracting 3 from the result. Here, the value 3 comes from the length of the string In; because the intToChange value stores the character number of the first backslash, you need count only the I, the n, and the second backslash to reach its end.
• Line 6 saves the document using the name in the strNName variable.

The syntax for InStrRev is similar to that of InStr:

InStrRev(stringcheck, stringmatch[, start[, compare]])

These are the arguments:

• stringcheck is a required String argument specifying the string in which to search for stringmatch.
• stringmatch is a required String argument specifying the string for which to search.
• start is an optional numeric argument specifying the starting position for the search. If you omit start, VBA starts at the last character of stringcheck.
• compare (as for InStr) is an optional argument specifying how to search: vbTextCompare for text, vbBinaryCompare for a binary comparison.

Using LTrim, RTrim, and Trim to Remove Spaces from a String

Often you'll need to trim strings before concatenating them to avoid ending up with extra spaces in inappropriate places, such as in the middle of eight-character filenames.

Data can contain appended or prepended spaces. And always remember that users might randomly type spaces in various ways when entering data. You never know. Your programming (and databases), however, need data in a predictable format (so the data can easily be searched, sorted, and otherwise manipulated).

For example, if 500 users entered their zip code, some might type a space before entering the digits. Any such entries would be placed at the start of a list after the list was alphabetically sorted (the space character is seen as “lower” than ordinary characters by a sorting function). So the sort would produce an inaccurate result. It's easy, though, to use the Trim functions to get rid of spaces.

As you saw in Table 9.9, VBA provides three functions specifically for trimming leading spaces and trailing spaces from strings:

• LTrim removes leading spaces from the specified string.
• RTrim removes trailing spaces from the specified string.
• Trim removes both leading and trailing spaces from the specified string.

The syntax for the LTrim, RTrim, and Trim functions is straightforward:

LTrim(string)
RTrim(string)
Trim(string)

In each case, string is any string expression.

You could use the Trim function to remove both leading and trailing spaces from a string derived from the current selection in the active document in Word. The first line in this next code example declares strUntrimmed and strTrimmed as String variables. The second line assigns the data in the current selection to the strUntrimmed string. The third line assigns the trimmed version of the strUntrimmed string to the strTrimmed string:

Dim strUntrimmed As String, strTrimmed As String
strUntrimmed = Selection.Text
strTrimmed = Trim(strUntrimmed)

Using Len to Check the Length of a String

To find out how long a string is, use the Len function. The syntax for the Len function is straightforward:

Len(string)

Here, string is any valid string expression. (If string is Null, Len also returns Null.)

One use for Len is to make sure a user's entry in an input box or in a text box on a form is of a suitable length. A United States phone number must be 10 digits, for instance.

The CheckPassword procedure shown in Listing 9.2 uses Len to make sure a password the user enters is long enough to be difficult to guess, but not too long.

 1.  Sub CheckPassword()
 2.      Dim strPassword As String
 3.  BadPassword:
 4.      strPassword = InputBox _
             ("Enter the password to protect this item from changes:" _
             , "Enter Password")
 5.      If Len(strPassword) = 0 Then
 6.          End
 7.      ElseIf Len(strPassword) < 6 Then
 8.          MsgBox "The password you chose is too short." _
                & vbCr & vbCr & _
                "Choose a password between 6 and 15 characters in length.", _
                 vbOKOnly + vbCritical, "Unsuitable Password"
 9.          GoTo BadPassword
10.      ElseIf Len(strPassword) > 15 Then
11.          MsgBox "The password you chose is too long." _
                 & vbCr & vbCr & _
                 "Choose a password between 6 and 15 characters in length.", _
                  vbOKOnly + vbCritical, "Unsuitable Password"
12.          GoTo BadPassword
13.      End If
14.  End Sub

Listing 9.2 ensures that a password contains between 6 and 15 characters (inclusive). Here's how the code works:

• Line 2 declares a String variable named strPassword.
• Line 3 contains the label BadPassword, to which the GoTo statements in line 9 and line 12 redirect execution if the password fails either of the checks. Labels are locations within code that you might need to jump to during execution. A label is a word on its own line in the code that ends with a colon. Labels are discussed in Chapter 11, “Making Decisions in Your Code.”
• Line 4 assigns to strPassword the result of an input box that invites the user to enter the password for the item.
• Lines 5 through 13 then use an If statement to check that the password is an appropriate length. First, line 5 checks strPassword for zero length, which would mean that the user clicked either the Cancel button or the close button on the input box or clicked the OK button with no text entered in the input box. If the length of strPassword is zero, the End statement in line 6 terminates the procedure. If the password passes that test, line 7 checks to find out if its length is less than 6 characters; if so, the procedure displays a message box alerting the user to the problem and then redirects execution to the BadPassword label. If the password is 6 or more characters long, line 10 checks to see if it's more than 15 characters long; if it is, the user is shown another message box and another trip to the BadPassword label.

Using StrConv, LCase, and UCase to Change the Case of a String

If you need to change the case of a string, use the StrConv (whose name comes from string conversion), LCase, and UCase functions. Of these, the easiest to use is StrConv, which can convert a string to a variety of different formats varying from straightforward uppercase, lowercase, or propercase (as VBA refers to initial capitals, also known as title case) to the Japanese hiragana and katakana phonetic characters.

USING STRCONV

The StrConv function has the following syntax:

StrConv(string, conversion)

Here, the string argument is any string expression, and the conversion argument is a constant or value specifying the type of conversion required. The most useful conversion constants and values are shown in Table 9.10.

For example, suppose you received from a database program a string called strCustomerName containing a person's name. You could use StrConv to make sure that it was in title case by using a statement such as this:

strProperCustomerName = StrConv(strCustomerName, vbProperCase)

USING LCASE AND UCASE

If you don't feel like using StrConv, you can alternatively use the LCase and UCase functions, which convert a string to lowercase and uppercase, respectively.

LCase and UCase have the following syntax:

LCase(string)
UCase(string)

Here, string is any string expression.

For example, the following statement lowercases the string MyString and assigns it to MyLowerString:

MyLowerString = LCase(MyString)

Using the StrComp Function to Compare Apples to Apples

As you've seen already, you can compare one item to another item by simply using the = operator:

If 1 = 1 Then MsgBox "One is one."

This straightforward comparison with the = operator also works with two strings, as shown in the second line here:

strPet = InputBox("Is your pet a dog or a cat?", "Pet")
If strPet = "Dog" Then MsgBox "We do not accept dogs."

The problem with this code as written is that the strings need to match exactly in capitalization for VBA to consider them equal. If the user enters dog or DOG (not to mention dOG, doG, dOg, or DoG) rather than Dog, the condition isn't met. Again, permit your users a variety of correct responses—don't enforce pointless capitalization and punctuation rules.

To accept variations of capitalization, you could use the Or operator to hedge your bets:

If Pet = "Dog" Or Pet = "dog" Or Pet = "DOG" Or Pet = "dogs" _
    Or Pet = "Dogs" or Pet = "DOGS" Then MsgBox _
    "We do not accept dogs. "

As you can see, such code rapidly becomes clumsy, even omitting some variations such as dOG. Or you could change the case of one or both strings involved to make sure their case matched, but it's simpler to just use the StrComp function, which is designed to permit you to ignore case. The syntax for StrComp is as follows:

StrComp(string1, string2 [, compare])

Here, string1 and string2 are required String arguments specifying the strings to compare, and compare is an optional argument specifying textual comparison (vbTextCompare) or binary comparison (vbBinaryCompare).

The following statement uses StrComp to settle the pet question once and for all:

If StrComp(Pet, "dog", vbTextCompare) = True Then _
    MsgBox "We do not accept dogs."

Using VBA's Mathematical Functions

VBA provides a solid suite of functions for standard mathematical operations. Table 9.11 lists these functions with examples.

Using VBA's Date and Time Functions

VBA provides a full complement of date and time functions, as listed in Table 9.12. The table provides brief examples of working with the functions. The sections after the table provide longer examples showing how to use some of the more complex functions.

Using the DatePart Function to Parse Dates

The DatePart function lets you take a date and separate it into its components. You can often achieve the same results by using other date functions, but DatePart is a great tool to have in your VBA toolbox.

The syntax for DatePart is as follows:

DatePart(Interval, Date[,FirstDayOfWeek[, FirstWeekOfYear]])

The components of the syntax are as follows:

• Interval is a required String expression giving the unit in which you want to measure the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute (because m is for month), and s for second.
• Date is a required Variant/Date giving the date you want to examine.
• FirstDayOfWeek is an optional constant specifying the day that starts the week (for date information). The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; this uses the system setting).
• FirstWeekOfYear is an optional constant specifying the week considered first in the year. Table 9.13 shows the options for this constant.

For example, the following statement assigns the current year to the variable dteThisYear:

dteThisYear = DatePart("yyyy", Date)

Using the DateDiff Function to Figure Out a Time Interval

The DateDiff function returns the interval (the number of days, weeks, hours, and so on) between two specified dates. The syntax for DateDiff is as follows:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Here are the components of the syntax:

• interval is a required String expression giving the unit in which you want to measure the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute (because m is for month), and s for second.
• date1 and date2 are the dates between which you're calculating the interval.
• firstdayofweek is an optional constant specifying the day that starts the week (for date information). The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; this uses the system setting).
• firstweekofyear is an optional constant specifying the week considered first in the year. Table 9.13 shows the options for this constant.

For example, the following statement returns the number of weeks between June 3, 2009, and September 30, 2009:

MsgBox DateDiff("ww", "6/3/2009", "9/30/2009")

Using the DateAdd Function to Add or Subtract Time from a Date

The DateAdd function lets you easily add an interval of time to, or subtract an interval of time from, a specified date, returning the resulting date. The syntax for DateAdd is as follows:

DateAdd(interval, number, date)

Here are the components of the syntax:

• interval is a required String expression giving the unit of measurement for the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute, and s for second.
• number is a required numeric expression giving the number of intervals to add (a positive number) or to subtract (a negative number). If number isn't already of the data type Long, VBA rounds it to the nearest whole number before evaluating the function.
• date is a required Variant/Date or literal date giving the starting date.

For example, the following statement returns the date 10 weeks from May 27, 2010:

DateAdd("ww", 10, #5/27/2009#)

Using File-Management Functions

The following sections demonstrate how to use a couple of key VBA file-management functions: the Dir function, which you use to find out whether a file exists, and the CurDir function, which returns the current path.

Using the Dir Function to Check Whether a File Exists

Often when managing files, you'll need to first check whether a particular file already exists. For instance, if you're about to save a file, you may want to make sure the save operation won't overwrite an existing file—a file with the same name in the same location on the hard drive.

Or if you're about to open a file, you may want to see if that file exists before you use the Open method; otherwise, VBA will give an error.

To test whether a file exists, you can use a straightforward procedure such as the one shown in Listing 9.3.

 1.  Sub Does_File_Exist()
 2.      Dim strTestFile As String, strNameToTest As String, _
             strMsg As String
 3.      strNameToTest = InputBox("Enter the file name and path:")
 4.      If strNameToTest = "" Then End
 5.      strTestFile = Dir(strNameToTest)
 6.      If Len(strTestFile) = 0 Then
 7.          strMsg = "The file " & strNameToTest & _
                 " does not exist."
 8.      Else
 9.          strMsg = "The file " & strNameToTest & " exists. "
10.      End If
11.      MsgBox strMsg, vbOKOnly + vbInformation, _
            "File-Existence Check"
12.  End Sub

This procedure in Listing 9.3 uses the Dir function to check whether a file exists and displays a message box indicating whether it does or doesn't. Figure 9.3 shows examples of the message box. This message box is for demonstration purposes only. In a real-world macro you'd likely use the result of the test to branch (execute different code blocks) based on whether the file exists. Branching is covered in Chapter 11.

FIGURE 9.3 You can use the Dir function to check whether a file exists so that you don't accidentally overwrite it or cause an error by trying to open a nonexistent file.

Here's how the code works:

• Line 2 declares the string variables strTestFile, strNameToTest, and strMsg.
• Line 3 then displays an input box prompting the user to enter a filename and path; VBA assigns the result of the input box to strNameToTest.
• Line 4 compares strNameToTest to a blank string (which means the user clicked the Cancel button in the input box or clicked the OK button without entering any text in the text box) and uses an End statement to end the procedure if it gets a match.
• Line 5 assigns to strTestFile the result of running the Dir function on the strNameToTest string. If Dir finds a match for strNameToTest, strTestFile will contain the name of the matching file; otherwise, it will contain an empty string.
• Line 6 begins an If… Then statement by testing the length of the strTestFile string. If the length is 0, the statement in line 7 assigns to strMsg text saying that the file doesn't exist; otherwise, VBA branches to the Else statement in line 8 and runs the statement in line 9, assigning text to strMsg saying that the file does exist. Line 10 ends the If statement.
• Line 11 displays a message box containing strMsg. Line 12 ends the procedure.

Returning the Current Path

You can find out the current path (the location on the hard drive to which the host application is currently pointed) on either the current drive or a specified drive by using the CurDir function. Often, you'll need to change the current path (using the ChDir function) to make sure the user is saving files in, or opening files from, a suitable location.

To return the current path, use CurDir without an argument:

CurDir

To return the current path for a specified drive, enter the drive letter as an argument. For example, to return the current path on drive D, use this statement:

CurDir("D")

The Bottom Line

Understand what functions are and what they do. A function is a unit of code, a procedure, that performs a task and returns a value.

You can write your own functions by writing code between Function and End Function in the VBA Editor. Chapter 10, “Creating Your Own Functions,” explores how to write such custom functions. But in addition to functions you might write, there are many functions already prewritten in VBA—ready for you to call them from your macros to perform various tasks.

Master It A function is quite similar to a subroutine, but there is a significant difference. What is it?

Use functions. In a macro, you can call a built-in function by merely typing in its name and providing any required arguments.

Master It You can combine multiple functions in a single line of code. The MsgBox function displays a message box containing whatever data you request. The only required argument for this function is the prompt. The Now function returns the current date and time. Write a line of code that calls the MsgBox function and uses the Now function as its argument.

Use key VBA functions. VBA offers the services of hundreds of built-in functions. You'll find yourself using some of them over and over. They are key to programming.

Master It What built-in function is used quite often to display information in a dialog box to the user while a procedure runs?

Convert data from one type to another. It's sometimes necessary to change a value from one data type to another. Perhaps you used an input box to ask the user to type in a String variable, but then you need to change it into an Integer type so you can do some math with it. (You can't add pieces of text to each other.)

Master It What built-in function would you use to convert a string such as "12" (which, in reality, is two text characters, the digits 1 and 2) into an Integer data type, the actual number 12, that you can manipulate mathematically?

Manipulate strings and dates. VBA includes a full set of functions to manage text and date data.

Master It Which built-in function would you use to remove any leading and trailing space characters from a string? For example, you want to turn

" this      "

into

"this"