| A string expression is an expression that returns a value that has a String data type. String expressions can use as operands string literals (one or more characters enclosed in double quotation marks), variables declared as String, or any of VBA's built-in functions that return a String value. Table 4.7 summarizes all of the VBA functions that deal with strings. Table 4.7. VBA's String Functions | Function | What It Returns | | Asc( string ) | The ANSI character code of the first letter in string . | | Chr( charcode ) | The character, as a Variant , that corresponds to the ANSI code given by charcode . | | Chr$( charcode ) | The character, as a String , that corresponds to the ANSI code given by charcode . | | CStr( expression ) | Converts expression to a String value. | | InStr( start,string1,string2 ) | The character position of the first occurrence of string2 in string1 , starting at start . | | InStrB( start,string1,string2 ) | The byte position of the first occurrence of string2 in string1 , starting at start . | | InStrRev( string1,string2, start ) | The character position of the final occurrence of string2 in string1 , starting at start . | | LCase( string ) | string converted to lowercase, as a Variant . | | LCase$( string ) | string converted to lowercase, as a String . | | Left( string,length ) | The leftmost length characters from string , as a Variant . | | Left$( string,length ) | The leftmost length characters from string , as a String . | | LeftB( string ) | The leftmost length bytes from string , as a Variant . | | LeftB$( string ) | The leftmost length bytes from string , as a String . | | Len( string ) | The number of characters in string . | | LenB( string ) | The number of bytes in string . | | LTrim( string ) | A string, as a Variant , without the leading spaces in string . | | LTrim$( string ) | A string, as a String , without the leading spaces in string . | | Mid( string,start,length ) | length characters, as a Variant , from string beginning at start . | | Mid$( string,start,length ) | length characters, as a String , from string beginning at start . | | MidB( string,start,length ) | length bytes, as a Variant , from string beginning at start . | | MidB$( string,start,length ) | length bytes, as a String , from string beginning at start . | | Right( string ) | The rightmost length characters from string , as a Variant . | | Right$( string ) | The rightmost length characters from string , as a String . | | RightB( string ) | The rightmost length bytes from string , as a Variant . | | RightB$( string ) | The rightmost length bytes from string , as a String . | | RTrim( string ) | A string, as a Variant , without the trailing spaces in string . | | RTrim$( string ) | A string, as a String , without the trailing spaces in string . | | Trim( string ) | A string, as a Variant , without the leading and trailing spaces in string . | | Trim$( string ) | A string, as a String , without the leading and trailing spaces in string . | | Space( number ) | A string, as a Variant , with number spaces. | | Space$( number ) | A string, as a String , with number spaces. | | Str( number ) | The string representation, as a Variant , of number . | | Str$( number ) | The string representation, as a String , of number . | | StrComp( string2,string2,compare ) | A value indicating the result of comparing string1 and string2 . | | StrConv( string, conversion ) | The string converted into another format, as specified by conversion (such as vbUpperCase , vbLowerCase , and vbProperCase ). | | String( number,character ) | character , as a Variant , repeated number times. | | String$( number,character ) | character , as a String , repeated number times. | | UCase( string ) | string converted to uppercase, as a Variant . | | UCase$( string ) | string converted to uppercase, as a String . | | Val( string ) | All the numbers contained in string , up to the first non-numeric character. | Listing 4.3 shows a procedure that uses some of these string functions.  | Note the use of the underscore (_) in Listing 4.3. This is VBA's code continuation character ”it's useful for breaking up long statements into multiple lines for easier reading. One caveat, though: Make sure you add a space before the underscore, or VBA will generate an error. |
Listing 4.3. A Procedure that Uses a Few String Functions Function ExtractLastName(fullName As String) As String Dim spacePos As Integer spacePos = InStr(fullName, " ") ExtractLastName = Mid$(fullName, _ spacePos + 1, _ Len(fullName) - spacePos) End Function Sub TestIt() MsgBox ExtractLastName("Millicent Peeved") End Sub The purpose of this procedure is to take a name (first and last, separated by a space, as shown in the TestIt procedure) and extract the last name. The full name is brought into the function as the fullName argument. After declaring an Integer variable named spacePos , the procedure uses the InStr function to check fullName and find out the position of the space that separates the first and last names . The result is stored in spacePos : spacePos = InStr(fullName, " ") The real meat of the function is provided by the Mid$ string function, which uses the following syntax to extract a substring from a larger string: Mid$( string,start,length ) string The string from which you want to extract the characters. In the ExtractLastName function, this parameter is the fullName variable. start The starting point of the string you want to extract. In ExtractLastName , this parameter is the position of the space, plus 1 (in other words, spacePos + 1 ). length The length of the string you want to extract. In the ExtractLastName function, this is the length of the full string ” Len(fullName) ”minus the position of the space. |
