Working with Strings

WORKING WITH STRINGS

A string is a sequence of characters.

VBA has two types of strings:

Fixed-length strings are declared with a specified maximum number of characters. The maximum declared length is about 65526.
Variable-length strings theoretically can hold as many as two billion characters.

Each character in a string takes one byte of memory storage.

In addition, a variable length-string consumes an extra 16 bytes.

Thus to save memory, it is better to use fix-length strings if possible.

When declaring a string variable with a Dim statement, you can specify the maximum length if you know it (it's a fixed-length string) or let VBA handles it dynamically (it's a variable-length string).

For example,

Dim MyString As String * 50     'it is a fix-length string
Dim YourString As String        'it is a variable-length string

Text can be stored in a string using the assignment operator:

a = "Go"
b = "away!"

We can paste (concatenate) the strings together using the operator &:

c = a & b

The value stored in c would be "Goaway!".

A space can be inserted between the two words by pasting a space character between them:

c = a & " " & b

The result can be displayed using a message box:

MsgBox c

VBA has numerous functions that works and manipulates strings (see p.100 of the textbook for an abbreviated list).

The following shows an example where these functions can be used:

Suppose a name is assigned to a string variable:

fullName = "Doe, Jane"

The number of characters in this string (including blanks and punctuation marks) can be found using the Len function

length = Len(fullName)

Here length would be equal to 9. The position of the comma can be found using the InStr function:

commaPosition = InStr(fullName, ",")

The first argument of the InStr function specifies the string being searched and the second argument is the string sought. Since the comma is located in the fourth position, commaPosition would have a value of 4.

The Left function can then be used extract the family name from the full name:

familyName = Left(fullName, commaPosition - 1)

The Right function could be used to obtain the given name:

givenName = Right(fullName, length - commaPosition -1)

The family name and the given name can be concatenated with a few phrases to display a personalized message:

MsgBox "Dear " & givenName & " " & familyName & ", How are you?"

The resulting message would be:

Dear Jane Doe, How are you?