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

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.

The Asc function returns the character code for the first character of a string. Character codes are the numbers by which computers refer to letters. For example, the character code 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"

The Val function converts the numbers contained in a string into a numeric value. Val follows these rules:

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)

Just as you can convert a string to a numeric value as in the previous section, you can also convert a numeric value to a string. You'll need to do this when you want to concatenate the information contained in a value with a string—if you try to do this simply by using the + operator, VBA attempts to perform the mathematical operation addition rather than the string operation you want: concatenation.

For 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 Str function. The syntax for the Str function is this:

Str(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" & Str(intAge) & "."
    MsgBox strYourAge, vbOKOnly + vbInformation, "Age"
End S

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

The syntax for the Format function is as follows:

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

These are the components of the syntax:

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) suits your needs.

Format("12345", "Currency")

Format("123456", "$00.0000")

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

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

To add special characters (such as a carriage return or a tab) to 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 character to add.

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

Here's a practical example. 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.

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

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

Left(string, length)

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

Right(string, length)

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

Mid(string, start[, length])

Dim strPhone As String

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

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)

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.

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:

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:

The syntax for InStrRev is similar to that of InStr:

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

These are the arguments:

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. And always remember that users might randomly type spaces in various ways when entering data. Programs and databases, however, expect a particular format for their data (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 it was alphabetically sorted (the space character is seen as "lower" than ordinary characters by a sorting function). 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:

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)

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 of a dialog box 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:

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.

StrConv(string, conversion)

strProperCustomerName = StrConv(strCustomerName, vbProperCase)

LCase(string)
UCase(string)

MyLowerString = LCase(MyString)

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

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:

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

dteThisYear = DatePart("yyyy", Date)

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:

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

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:

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

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

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.

Here's how the code works:

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