Working with Strings and Dates: VBA Tips and Tricks

Article
09/12/2006

David Shank
Microsoft Corporation

August 3, 2000

As you develop solutions, you'll find that you need to perform a number of operations repeatedly—parsing a file path, for example, or calculating the interval between two dates. Once you understand which functions are available to you and how they work, you can begin to build a collection of custom procedures that you can use over and over to solve common problems.

Two of the most common things you do as a developer writing Visual Basic for Applications (VBA) code is work with strings and work with dates. VBA provides powerful functions for this, but it is not always easy or intuitive to understand how to use them. In this month's column, I'll illustrate how to make the most of powerful built-in VBA functions to work with strings and dates.

Working with Strings

Here are some common questions about working with strings, and some custom procedures and code samples that illustrate the answers to those questions.

What's the best way to determine the length of a string?

You use the Len function to calculate the length of a string:

Dim lngLen As Long
lngLen = Len(strText)

VBA always stores the length of a string as a long integer at the beginning of the string. The Len function retrieves this value, and is therefore quite fast. For example, if you need to determine whether a string is a zero-length string (""), rather than comparing the string in question to a zero-length string, you can simply check whether the length of the string is equal to 0:

If Len(strText) > 0 Then
   ' Perform some operation here.
End If

How do I determine whether a string contains a specified substring?

You can search strings using the InStr or the InStrRev functions. The InStr function compares two strings, and if the second string is contained within the first, it returns the position at which the substring begins. If the InStr function doesn't find the substring, it returns 0. Once you know the starting position of a substring, you can use other VBA functions, such as the Mid function, to get the substring itself.

' Use InStr to search a string to determine whether  
' a substring exists. In the following example InStr
' returns 4, which is the character position where the
' substring begins:
Dim intSubStringLoc As Integer
intSubStringLoc = InStr("My dog has fleas", "dog")

The InStr function uses an optional start argument to specify the position to begin searching.

' In the following example, InStr returns 0 because 
' the search starts at character position 5, which 
' has the effect of changing the search string to 
'  "og has fleas":
Dim intSubStringLoc As Integer
intSubStringLoc = InStr(5, "My dog has fleas", "dog")

If you omit this argument, the InStr function starts searching at the first character in the string.

The InStrRev function begins searching at the end of the string, rather than at the beginning. As with the InStr function, you can specify a start position. The InStrRev will then search backward through the string, beginning at that starting point. If you know that the substring you're looking for falls at the end of the string, the InStrRev function may be a better choice than the InStr function. For example, you would use the InStrRev function to return a file name from a file path. The following procedure call will return "Coverletter.doc":

Dim strFileName As String
StrFileName = GetNameFromPath("C:\Letters\New\Coverletter.doc")

Function GetNameFromPath(strPath As String) As String
    Dim strTemp As String
    Dim intPos As Integer
    ' If string contains a "\" then return that portion
    ' of the string after the last "\":
    If InStr(strPath, "\") > 0 Then
        intPos = InStrRev(strPath, "\") + 1
        strTemp = Mid$(strPath, intPos)
        GetNameFromPath = strTemp
    Else
        ' Invalid path submitted so return 0.
        GetNameFromPath = 0
    End If
End Function

Both functions return the same value for the start position of the substring. For example, calling either the InStr or InStrRev function to search the string "C:\Temp" for the substring "C:\" returns 1. However, if the substring appears more than once, and you haven't specified a value for the start argument, the InStr function returns the position of the first instance, and the InStrRev function returns the position of the last instance.

The following procedure counts the occurrences of a particular character or group of characters in a string. To call the procedure, you pass in the string, the substring that you're looking for, and a constant indicating whether the search should be case-sensitive. The procedure then uses the InStr function to search for the specified text and return the value of the position at which it first occurs; for example, if it's the third character in the string, the InStr function returns 3. This value is stored in a temporary variable, so that the value can be maintained after the next call to the InStr function. The procedure increments the counter variable, which keeps track of the number of occurrences found, then sets the starting position for the next call to the InStr function. The new starting position is the position at which the search text was found, plus the length of the search string. By setting the start position in this manner, you ensure that you don't locate the same substring twice when you're searching for text that's more than one character in length.

Function CountOccurrences(strText As String, _
                          strFind As String, _
                          Optional lngCompare _
                          As VbCompareMethod) As Long

   ' Count occurrences of a particular character or characters.
   ' If lngCompare argument is omitted, procedure performs binary comparison.
   
   Dim lngPos       As Long
   Dim lngTemp      As Long
   Dim lngCount     As Long
   
   ' Specify a starting position. We don't need it the first
   ' time through the loop, but we'll need it on subsequent passes.
   lngPos = 1
   ' Execute the loop at least once.
   Do
      ' Store position at which strFind first occurs.
      lngPos = InStr(lngPos, strText, strFind, lngCompare)
      ' Store position in a temporary variable.
      lngTemp = lngPos
      ' Check that strFind has been found.
      If lngPos > 0 Then
         ' Increment counter variable.
         lngCount = lngCount + 1
         ' Define a new starting position.
         lngPos = lngPos + Len(strFind)
      End If
   ' Loop until last occurrence has been found.
   Loop Until lngPos = 0
   ' Return the number of occurrences found.
   CountOccurrences = lngCount
End Function

Calling this function from the Immediate window in the Visual Basic Environment (VBE), as follows, returns 3:

? CountOccurrences("This is a test", "t", vbTextCompare)

How do I determine the number of words in a string?

The Split function converts a string into an array of strings. By default, it uses the space character as a delimiter. You can specify a different delimiter by passing in the delimiter argument. If you pass in a sentence to the Split function, each element of the array contains a word. For example, if you pass this string:

"This is a test"

you'll get an array that contains the following four elements:

"This"
"is"
"a"
"test"

The following example counts the number of words in a string. To determine the number of words, you simply need to determine the number of elements in the array. You can do this by subtracting the lower bound from the upper bound and adding 1.

Function CountWords(strText As String) As Long
   ' This procedure counts the number of words in a string.

   Dim astrWords() As String

   astrWords = Split(strText)
   ' Count number of elements in array -- this will be the
   ' number of words.
   CountWords = UBound(astrWords) - LBound(astrWords) + 1
End Function

How do I remove extra spaces from a string?

The following procedure uses the Split and Join functions to trim extra space characters from a string. It splits the passed-in string into an array. Wherever there is more than one space within the string, the corresponding array element is a zero-length string. By finding and removing these zero-length string elements, you can remove the extra white space from the string.

To remove zero-length string elements from the array, the procedure must copy the non-zero-length string elements into a second array. The procedure then uses the Join function to concatenate the second array into a whole string.

Because the second array isn't created by the Split function, you need to size it manually. It's easy to do, however—you can size it initially to be the same size as the first array, then resize it after you've copied in the non-zero-length strings.

Function TrimSpace(strInput As String) As String
   ' This procedure trims extra space from any part of
   ' a string.

   Dim astrInput()     As String
   Dim astrText()      As String
   Dim strElement      As String
   Dim lngCount        As Long
   Dim lngIncr         As Long
   
   ' Split passed-in string.
   astrInput = Split(strInput)
   
   ' Resize second array to be same size.
   ReDim astrText(UBound(astrInput))
   
   ' Initialize counter variable for second array.
   lngIncr = LBound(astrInput)
   ' Loop through split array, looking for
   ' non-zero-length strings.
   For lngCount = LBound(astrInput) To UBound(astrInput)
      strElement = astrInput(lngCount)
      If Len(strElement) > 0 Then
         ' Store in second array.
         astrText(lngIncr) = strElement
         lngIncr = lngIncr + 1
      End If
   Next
   ' Resize new array.
   ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

   ' Join new array to return string.
   TrimSpace = Join(astrText)
End Function

To test the TrimSpace procedure, try calling it from the Immediate window with a string like the following:

? TrimSpace("  This   is    a    test  ")
' This function returns, "This is a test"

How do I replace a substring with another substring?

You can use the Replace function to find and replace all occurrences of a substring within a string. The Replace function takes six arguments: the string to be searched, the text to find within the string, the replacement text, which character to start at, how many occurrences to replace, and a constant indicating the string-comparison method. You don't even have to write a loop to use the Replace function; it automatically replaces all the appropriate text for you with one call.

For example, suppose you want to change the criteria for a SQL statement based on some condition in your application. Rather than re-creating the SQL statement, you can use the Replace function to replace just the criteria portion of the string, as in the following code fragment:

strSQL = "SELECT * FROM Products WHERE ProductName Like 'M*' ORDER BY ProductName;"
strFind = "'M*'"
strReplace = "'T*'"
   
strNewString = Replace(strSQL, strFind, strReplace)
Running this code results in strNewString having the following value:
SELECT * FROM Products WHERE ProductName Like 'T*' ORDER BY ProductName;

The procedure shown below takes three arguments: a string to be searched, the word to find within the string, and the replacement text. When you call this procedure, you can include wildcard characters in the string that you pass for the strFind argument. For example, you might call the ReplaceWord procedure with these parameters:

StrNewString = ReplaceWord("There will be a test today", "t*t", "party")

The procedure splits the strText argument into an array, then uses the Like operator to compare each element of the array to strFind, replacing the elements that match the wildcard specification.

Function ReplaceWord(strText As String, _
                     strFind As String, _
                     strReplace As String) As String
                    
   ' This function searches a string for a word and replaces it.
   ' You can use a wildcard mask to specify the search string.
   
   Dim astrText()    As String
   Dim lngCount      As Long
   
   ' Split the string at specified delimiter.
   astrText = Split(strText)
   
   ' Loop through array, performing comparison
   ' against wildcard mask.
   For lngCount = LBound(astrText) To UBound(astrText)
      If astrText(lngCount) Like strFind Then
         ' If array element satisfies wildcard search,
         ' replace it.
         astrText(lngCount) = strReplace
      End If
   Next
   ' Join string, using same delimiter.
   ReplaceWord = Join(astrText)
End Function

Working with Dates and Times

Working with dates and times can be straightforward, and VBA provides some great built-in functions to make this task easier. However, there are traps for the unwary that can make coding time frustrating.

The following questions about working with dates highlight some of those traps, and show you how to avoid them to create custom functions that you can use in many of your custom Office solutions.

How do I determine the current date or the current time?

VBA provides three functions you can use to determine exactly when it is: the Now, Date, and Time functions. The Now function returns both the date and time portions of a Date variable. For example, the Now function returns a value such as this:

08/01/2000 10:18:33 AM

The Date function returns the current date, without the time.

08/01/2000

The Time function returns the current time, without the date.

10:18:33 AM

You can use predefined formats to format a date by calling the FormatDateTime function. The following procedure formats a date by using both built-in formats:

' Print date using built-in formats.
Debug.Print FormatDateTime(Date, vbGeneralDate)
' Returns: 08/01/2000

Debug.Print FormatDateTime(Date, vbLongDate)
' Returns: Tuesday, August 01, 2000

Debug.Print FormatDateTime(Date, vbShortDate)
' Returns: 08/01/2000

Debug.Print FormatDateTime(Time, vbLongTime)
' Returns: 10:26:31 AM

Debug.Print FormatDateTime(Time, vbShortTime)
' Returns: 10:27

The first argument for the FormatDateTime function can use the Date or Time function, as appropriate, or the Now function in all cases.

You can also use the Format function to create custom date or time formats:

' Print date using built-in formats.
Debug.Print Format$(Now, "ddd, mmm d, yyyy")
' Returns: Tue, Aug 1, 2000

Debug.Print Format$(Now, "mmm d, H:MM am/pm")
' Returns: Aug 1, 10:31 am

Why does VBA store the wrong values in my date variables?

When you work with date literals in your code, you need to indicate to VBA that a value is a date. If you don't, VBA may think you're performing subtraction or floating-point division.

For example, in the following code, the value that VBA assigns to the Date variable is not April 5, 1999, but 4 divided by 5 divided by 99. Because you're assigning it to a Date variable, VBA converts the number to a date:

Dim dteDate As Date
dteDate = 4 / 5 / 99
Debug.Print dteDate
' Returns: 12:11:45 AM

To avoid this problem, you must include delimiters around the date. The preferred date delimiter for VBA is the number sign (#). You can also use double quotation marks, as you would for a string—but doing so requires VBA to perform an extra step to convert the string to a date. In the following example, using the # date delimiter returns the correct value:

Dim dteDate As Date
dteDate = #4/5/99#
Debug.Print dteDate
' Returns: 04/05/1999

How do I work with the parts of a date or time?

To work with a date in code, you sometimes need to break it down into its day, month, and year. You can then perform calculations on one element, and reassemble the date. You use the Day, Month, and Year functions to break a date into its components. Each of these functions takes a date and returns the day, month, or year portion, respectively:

Dim dteDate As Date
dteDate = #4/5/99#
Debug.Print Day(dteDate)
' Returns: 4

Debug.Print Month(dteDate)
' Returns: 5

Debug.Print Year(dteDate)
' Returns: 1999

You use the DateSerial function to reassemble a date from its components. This function takes arguments representing a year, a month, and a day and returns a Date value that contains the reassembled date.

Often you can break apart a date, perform a calculation on it, and reassemble it all in one step. For example, to find the first day of the month, given any date, you can write a function similar to the following one:

Function FirstOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the first day of a month, given a date.
   ' If no date is passed in, the function uses the current date.
   
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If
   
   ' Find the first day of this month.
   FirstOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)
End Function

Calling this procedure with the dteDate argument #2/23/00# returns "2/1/2000".

The following procedure uses the same technique to return the last day of a month, given a date:

Function LastOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the last day of a month, given a date.
   ' If no date is passed in, the function uses the current date.
   
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If
   
   ' Find the first day of the next month, then subtract one day.
   LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

VBA also provides functions that you can use to disassemble and reassemble a time value in the same manner. The Hour, Minute, and Second functions return portions of a time value; the TimeSerial function takes an hour, minute, and second value and returns a complete time value.

You can get other information about a date as well—such as which quarter or week it falls in, or which day of the week it is. The Weekday function takes a date and returns a constant indicating the day of the week on which that date falls.

The following procedure takes a date and returns True if the date falls on a workday—that is, Monday through Friday—and False if it falls on a weekend.

Function IsWorkday(Optional dteDate As Date) As Boolean
   ' This function determines whether a date
   ' falls on a weekday.
   
   ' If no date passed in, use today's date.
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If
   
   ' Determine where in week the date falls.
   Select Case Weekday(dteDate)
      Case vbMonday To vbFriday
         IsWorkday = True
      Case Else
         IsWorkday = False
   End Select
End Function

In addition to the individual functions that return part of a date—Year, Month, Day, and Weekday—VBA includes the DatePart function, which can return any part of a date. Although it may seem redundant, the DatePart function gives you slightly more control over the values you return, because it gives you the option to specify the first day of the week and the first day of the year. For this reason, it can be useful when you're writing code that may run on systems in other countries. In addition, the DatePart function is the only way to return information about the quarter into which a date falls.

How do I add and subtract dates?

You use the DateAdd function to add a value to a date. (Note that adding integers to a Date variable is equivalent to adding days, because the integer portion of a Date variable represents the number of days that have passed since December 30, 1899.)

By using the DateAdd function, you can add any interval to a given date: years, months, days, weeks, or quarters. The following procedure finds the anniversary of a given date. If the anniversary has already occurred this year, the procedure returns the date of the anniversary in the next year.

Function Anniversary(dteDate As Date) As Date
   ' This function finds the next anniversary of a date.
   ' If the date has already passed for this year, it returns
   ' the date on which the anniversary occurs in the following year.
   
   Dim dteThisYear As Date
   
   ' Find corresponding date this year.
   dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))
   ' Determine whether it's already passed.
   If dteThisYear < Date Then
      Anniversary = DateAdd("yyyy", 1, dteThisYear)
   Else
      Anniversary = dteThisYear
   End If
End Function

You use the DateDiff function to find the interval between two dates. The interval returned can be in any of several units of time: days, weeks, months, years, hours, and so on.

The following example uses the DateDiff function to return the day number (the date as a number between 1 and 365, representing where a date falls in the year) for a particular day of the year. The procedure determines the last day of the last year by using the DateSerial function, then subtracts that date from the date that was passed in to the procedure.

Function DayOfYear(Optional dteDate As Date) As Long

   ' This function takes a date as an argument and returns
   ' the day number for that year. If the dteDate argument is
   ' omitted, the function uses the current date.
   
   ' If the dteDate argument has not been passed, dteDate is
   ' initialized to 0 (or December 30, 1899, the date
   ' equivalent of 0).
   If CLng(dteDate) = 0 Then
      ' Use today's date.
      dteDate = Date
   End If
   
   ' Calculate the number of days that have passed since
   ' December 31 of the previous year.
   DayOfYear = Abs(DateDiff("d", dteDate, _
      DateSerial(Year(dteDate) - 1, 12, 31)))
End Function

Calling this procedure with the value of #10/23/00# returns "297."

How do I calculate a person's age?

You can use the DateAdd and DateDiff functions to calculate the time that has elapsed between two dates. With a little additional work, you can present that time in the desired format. For example, the following procedure calculates a person's age in years, taking into account whether his or her birthday has already occurred in the current year.

Using the DateDiff function to determine the number of years between today and a birthdate doesn't always give a valid result, because the DateDiff function rounds to the next year. If a person's birthday hasn't yet occurred, using the DateDiff function will make the person one year older than he or she actually is.

To remedy this situation, the procedure checks to see whether the birthday has already occurred this year; if it hasn't, the procedure subtracts 1 to return the correct age.

Function CalcAge(dteBirthdate As Date) As Long

   Dim lngAge As Long
   
   ' Make sure passed-in value is a date.
   If Not IsDate(dteBirthdate) Then
      dteBirthdate = Date
   End If
   
   ' Make sure birthdate is not in the future.
   ' If it is, use today's date.
   If dteBirthdate > Date Then
      dteBirthdate = Date
   End If
   
   ' Calculate the difference in years between today and birthdate.
   lngAge = DateDiff("yyyy", dteBirthdate, Date)
   ' If birthdate has not occurred this year, subtract 1 from age.
   If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
      lngAge = lngAge - 1
   End If
   CalcAge = lngAge
End Function

Where to Get More Info

These examples should be enough to get you started building your own library of custom functions for working with strings and dates. For additional information, check out the following resources:

For more information on working with VBA in Office solutions, see Getting the Most Out of Visual Basic for Applications.
For more information on using VBA, see https://msdn.microsoft.com/office/.
As always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.

David Shank is a programmer/writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native northwesterners still living in the northwest.