Working with String Expressions
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 below summarizes most of the VBA functions that deal with strings.
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. |
| Format(expression, format) | The expression, as a Variant, in the specified format. |
| Format$(expression, format) | The expression, as a String, in the specified format. |
| FormatCurrency(expression) | The expression formatted as currency. |
| FormatDateTime(expression) | The expression formatted as a date or time. |
| FormatPercent(expression) | The expression formatted as a percentage. |
| FormatCurrency(expression) | The expression formatted as currency. |
| InStr(start,string1,string2) | The character 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. |
| Len(string) | The number of characters 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. |
| Replace(expression,find,replace) | The expression with every instance of find replaced by replace. |
| Right(string) | The rightmost length characters from string, as a Variant. |
| Right$(string) | The rightmost length characters 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 nonnumeric character. |
Listing below shows a procedure that uses some of these string functions.
A Procedure That Uses a Few String FunctionsFunction 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("Kintery Morkel")
End Sub
Note: the use of the underscore (_) in Listing. 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.
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.