Microsoft Office 2000/Visual Basic Programmer's Guide

The Split Function

The Split function takes a string and converts it into an array of strings. By default, it divides the string into elements by using the space character as a delimiter, so that if you pass in a sentence, each element of the array contains a word. For example, if you pass this string to the Split function

"This is a test"

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

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

You can specify that the Split function split the string based on a different delimiter by passing in the delimiter argument.

Once you've split a string into an array, it's easy to work with the individual elements. The Split function sizes the array for you, so you don't have to worry about maintaining the array's size.

The following example uses the Split function to count the number of words in a string. The procedure takes a string and returns a long integer indicating the number of words found. Since the string is divided into elements at the space between each word, each element of the resulting array represents a word. 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

This procedure is available in the modStrings module in VBA.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.