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 someone reading your code might not know immediately which value corresponds to which argument—although 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, month, and day are all required Integer arguments.

The following statement supplies the arguments positionally without their names:

MsgBox DateSerial(2019, 12, 31)

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

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

To illustrate the third alternative, the following statement supplies the arguments, with their names, out of order:

MsgBox DateSerial(Day:=31, Year:=2016, 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.

Using Functions to Convert Data

In VBA you don't often have to convert data types, 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 even in VBA where you will need to forcibly convert one variable type into another.

Say that you're 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 would first want to 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 issue 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 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 used much, 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)

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 go the other way: convert a numeric value to a string with the Str function. But you should use the newer CStr function rather than the now deprecated 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". This result as you can see is quite distinct from adding these two numbers together mathematically.

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

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, 2019:

Format(#4/1/2019#, "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 a 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 want to build a string containing a person's name and address from individual strings containing items of that information. You also want 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, thereby building a tab-delimited list containing the names and addresses. This list can then be easily converted into a table with columns that 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

Sometimes you'll need to use only part of a string in your macros. How would you extract only the first three characters of the names of a list of cities to create a location code for each?

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. It is necessarily a bit more complicated; but it is quite flexible.

The Mid function returns a specified number of characters from inside a 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.

You'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.

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 subfolder Out.

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 first character of the In string is the thirteenth 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, thereby 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. Recall the telephone-number example where your program wanted no spaces, but you permitted the user to input spaces in them without penalty.

Data can contain appended or prepended spaces. What's more, 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 codes, one or two of them 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 unwanted 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.

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 VBA label is a word on its own line in the code that ends with a colon, as you can see. 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.

• 1. 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.
  2. If the length of strPassword is zero, the End statement in line 6 terminates the procedure.
  3. If the password passes that test, line 7 checks to find out if its length is less than six characters; if so, the procedure displays a message box alerting the user to the problem and then redirects execution to the BadPassword label.
  4. If the password is six 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 (short for 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 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.

Too many programmers would respond to a user input of dog with an error message like this: “Please ensure that only the first letter is capitalized. Let's try this again, you poor sod.”

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(strPet, "dog", vbTextCompare) = 0 Then _
    MsgBox "We do not accept dogs."

Another approach would be to just LCase the input and compare it to a lowercase test string:

If LCase(strPet)= "dog" Then _
    MsgBox "We do not accept dogs."

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 good 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 vb UseSystem (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)

Calculating Time Intervals Using the DateDiff Function

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 vb UseSystem (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, 2019:

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

Checking Whether a File Exists Using the Dir Function

Often when managing files, you'll need to first check whether a particular file already exists on the hard drive. 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.

If you're about to open a file, you might 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.

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:

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