Listing An Example of Useful String Functions

Sub UsefulStringFunctions() Dim sTestWord As String sTestWord = "filename"

' Len demonstration

Debug.Print sTestWord & " is " & Len(sTestWord) & _ " characters long."

' Mid & concatenation demonstration

Debug.Print Mid(sTestWord, 3, 1) & Right(sTestWord, 3)

' Left demonstration Debug.Print Left(sTestWord, 4)

' Right demonstration Debug.Print Right(sTestWord, 4)

' Trim demonstration sTestWord = " padded "

Debug.Print ">" & sTestWord & "<"

Debug.Print ">" & LTrim(sTestWord) & "<"

Debug.Print ">" & RTrim(sTestWord) & "<"

Debug.Print ">" & Trim(sTestWord) & "<"

' StrConv demonstration sTestWord = "The moon over Minneapolis is big and bright." Debug.Print StrConv(sTestWord, vbLowerCase) Debug.Print StrConv(sTestWord, vbUpperCase) Debug.Print StrConv(sTestWord, vbProperCase)

' Split demonstration sTestWord = "One, Two, Three, 4, Five, Six" DemoSplit sTestWord End Sub

Sub DemoSp1it(sCSV As String) Dim vaValues As Variant Dim nIndex As Integer

' Split the values vaValues = Sp1it(sCSV, ",")

' Loop through the values

For nIndex = 0 To UBound(vaValues)

Debug.Print "Item (" & nIndex & ") is " & vaValues(nlndex)

Next End Sub

SubUsefulStringFunctions produces the following output:

filename is 8 characters long.

lame file name

the moon over minneapolis is big and bright.

THE MOON OVER MINNEAPOLIS IS BIG AND BRIGHT.

The Moon Over Minneapolis Is Big And Bright.

Item (2) is Three

Between Table 15.1 and Listing 15.7 the purpose and use of the functions are pretty self-evident. However, Split is such a powerful function that it deserves a little more detail. Split is extremely handy when you are working with delimited files that you open using the VBA Open statement (rather than the OpenText method of the Workbooks object). The syntax of Split is as follows.

Sp1it(Expression, [Delimiter], [Limit], [Compare]) The parameters of Split are explained in the following list.

Expression Expression is a required parameter. It is the string that you would like to split apart. Expression should be a string consisting of subparts and delimiters such as one line from a delimited text file.

Delimiter Delimiter is an optional parameter. Set the Delimiter parameter equal to the character used to delimit the string given by Expression. By default, Split assumes that Expression is space delimited.

Limit Limit is an optional parameter that can be used to specify how many subparts to return.

Compare Compare is an optional parameter that can be used to specify the type of comparison to use when comparing substrings. Use one of the following: vbUseCompareOption, vbUseBina-ryCompare, vbUseTextCompare, or vbUseDatabaseCompare.

0 0

Post a comment