User-Defined Data Type with Excel VBA

Quite often when writing programs, a data type is needed that allows the use of various types together. A record is a collection of several elements, each of which may have its own type. An element of a record is called a field. A record is a special case of a class in which no properties or methods are defined:

[Private | Public] Type TypeName
    ElementName [([Size])] As DataType
    ElementName [([Size])] As DataType
    ...
End Type

• Private — used for user-defined types that are accessible only in the module containing the definition.
• Public — used for user-defined types that are accessible to all procedures in all modules of all projects.
• TypeName — the name of the user-defined type.
• ElementName — the name of an element of the user-defined type.
• Size — the size of the element if it is an array.
• DataType — the data type of the element. Supported types: Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, another user-defined type, or an object type.

The Type statement is used only at the module level. If a Type statement appears in a class module, it must be preceded by the keyword Private.

In Examples of using some structured , the Type statement is used to define a data type encapsulating information about an employee of a certain company.

Example of a user-defined type

Type Employee
    FirstName As String
    LastName As String
    Position As String
    BirthDate As Date
End Type

Sub InitialData()
    Dim emp As Employee
    With emp
        .FirstName = "James"
        .LastName = "Bond"
        .Position = "Secret agent 007"
        .BirthDate = #05/17/80#
    End With
    With emp
        MsgBox .FirstName & vbCr & .LastName & vbCr & .Position & vbCr & _
        .BirthDate
    End With
End Sub

NOTE
In Examples of using some structured, the MsgBox statement uses this technique: line continuation with a space and the underscore _ at the end of the line. In addition, the built-in constant vbCr is specified in the MsgBox statement, which allows the required information to be displayed on a new line in the message box.

It is also possible to create an array containing elements of a user-defined type. For example, the following array consists of information about 20 employees of a company’s sales department.

Example of an array whose elements have a user-defined type

Sub InitialData()
    Dim emp(3) As Employee
    With emp(0)
        .FirstName = "James"
        .LastName = "Bond"
        .Position = "Secret agent 007"
        .BirthDate = #05/17/80#
    End With
    With emp(1)
        .FirstName = "Alice"
        .LastName = "Smith"
        .Position = "Just a secret agent"
        .BirthDate = #09/06/89#
    End With
End Sub