| Microsoft Office 2000/Visual Basic Programmer's Guide | |
To convert text in a string from one case to another, you can use the VBA StrConv function. The StrConv function converts a string to lowercase, uppercase, or proper case (initial capital letters). It takes a string and a constant that specifies how to convert the string. For example, the following code fragment converts a string to proper case:
Debug.Print StrConv("washington, oregon, and california", vbProperCase)
Running this code prints the following text to the Immediate window:
Washington, Oregon, And California
Note The StrConv function performs other string conversions as well. For example, it converts a string from Unicode to ANSI, or vice versa. For more information about the StrConv function, search the Visual Basic Reference Help index for "StrConv function."
Most likely, you'll be about three-fourths satisfied with this result—you probably want "washington," "oregon," and "california" to be capitalized, but not "and." The word "and" is a minor word that isn't capitalized according to grammatical convention, unless it's the first word in the sentence. Unfortunately, VBA doesn't know which words to convert and which to leave alone, so it converts everything.
If you want VBA to omit the minor words, you can define those words in a file or a table, and perform a comparison against the file or table when you convert each word. The following procedure, ConvertToProperCase, does just that—it takes a string, splits it into individual words, compares each word against a list in a text file, and converts all non-minor words to proper case.
The ConvertToProperCase procedure calls another procedure, the GetMinorWords procedure. This procedure reads a text file containing a list of minor words and returns an array of strings containing each word in the text file. The ConvertToProperCase procedure then uses the Filter function to compare each word in the string to be converted against the list of words contained in the array of minor words. If a word doesn't appear in the list, then it's converted to proper case. If it does appear, it's converted to lowercase.
Function ConvertToProperCase(strText As String) As String
' This function takes a string and converts it to proper
' case, except for any minor words.
Dim astrText() As String
Dim astrWords() As String
Dim astrMatches() As String
Dim lngCount As Long
' Return array containing minor words.
astrWords = GetMinorWords
' Split string into array.
astrText = Split(strText)
' Check each word in passed-in string against array
' of minor words.
For lngCount = LBound(astrText) To UBound(astrText)
' Filter function returns array containing matches found.
' If no matches are found, upper bound of array is less than
' lower bound. Store result returned by Filter function in a
' String array, then compare upper bound with lower bound.
astrMatches = Filter(astrWords, astrText(lngCount))
If UBound(astrMatches) < Lbound(astrMatches) Then
' If word in string does not match any word in array
' of minor words, convert word to proper case.
astrText(lngCount) = StrConv(astrText(lngCount), vbProperCase)
Else
' If it does match, convert it to lowercase.
astrText(lngCount) = StrConv(astrText(lngCount), vbLowerCase)
End If
Next
' Join the string.
ConvertToProperCase = Join(astrText)
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.
The ConvertToProperCase procedure calls the GetMinorWords procedure, which opens the text file that contains the list of minor words, gets a string containing all the words in the list, splits the string into an array, and returns the array. GetMinorWords calls another procedure, the GetLikelyDelimiter procedure, which finds the first likely delimiter character in the text file. Both of these procedures, as well as the text file that contains the list of minor words, appear 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.
Note In order to call the ConvertToProperCase procedure, you must set a reference to the Microsoft Scripting Runtime object library. For more information about this object library, see "Working with Files" later in this chapter.