Structured Data Types with Excel VBA

Strings

A string represents a sequence of characters, each of which has the type Char.
A sequence of characters assigned to a string variable must be enclosed in quotation marks.

Dim str As String
str = "This is a string"

A string variable can be declared as a variable-length string or as a fixed-length string. In the following example, the variable LastName is declared as a variable-length string, and the variable State is declared as a fixed-length string consisting of two characters. If the variable State is assigned a string expression that consists of more than two characters, the extra characters on the right will be truncated upon assignment.

Dim LastName As String
Dim State As String * 2

Note that in VBA there is only one string operation — concatenation. It is used to combine several strings into one. The concatenation operation is denoted by the ampersand symbol (&) or the plus symbol (+). To avoid confusion, concatenation with the & sign is generally used. When two strings are combined, the second string is appended directly to the end of the first. The result is a larger string that fully contains both of the original strings.

In the following example, the variable s is assigned the value « Visual Basic for Applications ».

Dim s As String
s = "Visual Basic " & "for Applications"

The keyword Empty returns a reference to an empty string. The same effect can be achieved by placing a pair of quotation marks («  »). For example, the following statement will display a message box with the text « Equivalent ».

If Empty = "" Then MsgBox "Equivalent"

Enumerated Type

The enumerated type provides a convenient way to work with constants and allows associating constant values with their names.

[Public | Private] Enum TypeName
    ElementName [= Expression]
    ElementName [= Expression]
    ...
End Enum

• TypeName — the name of the enumerated type.
• ElementName — the name of the constant. By default, the first constant has the value 0, the second has the value 1, and so on. Using the Expression parameter, you can assign arbitrary values to constants.
• Expression — the value of the constant.

In the following example , values are defined using an enumerated type to identify the sides of a coin.

Using an enumerated type in the coin toss example

Enum Coin
    Head = 1
    Tail = -1
End Enum

Sub Attemp()
    Dim r As Integer
    Randomize
    r = 2 * Int(2 * Rnd()) - 1
    Select Case r
        Case Head
            MsgBox "Heads"
        Case Tail
            MsgBox "Tails"
    End Select
End Sub