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 VBAs built-in functions that return a String value. Table 4.7 summarizes most 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.

Format(expression, format)

The expression, as a Variant, in the specified format.

Format$(expression, format) The expression, as a String, 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.

Replace(expression, find,replace) The expression with every instance of find replaced by replace.

Function

What It Returns

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 4.3 shows

a procedure that uses some of these string functions.

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, _

End Function continues

0 0

Post a comment