Type

A user-defined type (or data structure) lets you create a single data type that can represent more than one built-in data type (UDTs).

The VBA programmer can use the Type statement to define a data type. You create a user-defined type using the Type statement. The Type statement can be used only at the module level.

The types in VBA are similar to structures in C language.

A Type is a collection of variables (or fields). This combines multiple data types into a single data type. It is a powerful tool, which can easily adapt to the object you are analyzing.

When using the type statement, you do not need to declare a class module. However, with a VBA class module, you don’t only attach variables to your class, you can also create methods in the class.

The VBA Type can’t have procedures or functions.

The use of types is less efficient and optimal than the use of class modules. If you have the choice, I would always advise you to use to create your class modules. As seen in the articles on dictionary items, it is easier to handle a class object than a data type as an item.

If you intend to store your objects in order to make them easily accessible afterwards, open so for creating a class.

For a better understanding of the type statement in the dictionary, we will illustrate the uses through the following dataset which represents several characters of the television show “The Simpsons”. The goal here is to build custom types to store the values of each character in memory.

Create a Type Statement in VBA

How can you set a Type statement?

To define a custom type, you can simply declare a type outside of any procedure at the start of the module.

Inside the Type, you can declare all the attributes you need before the words End Type.

In the example below, we declare a type named Person, having several attributes with different dimensions : string, date, integer, byte, boolean, long, currency, single, object, variant.

You can use a type statement as public or private. These specifications are optional to use, but the variable name and at least one element name are required.

Data type of the element; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String length (for fixed-length strings), Object, Variant, another user-defined type, or an object type.

'These lines are to be placed at the head of the module

Type Person
'Define a data type Person (only in a module level)
'You can also declare the type as Public or Private

    Name As String
    Surname As String
    Age As String
    Country As String
    City As String
    Address As String
    Quote As String
    PhoneNumber As String
    Mail As String
    Status As String
    DateBirth As Date
    Salary As Integer

End Type

Create an object of the Type Statement named Person

Once the type has been defined, you can, inside a procedure, declare a type variable of your previous creation. This variable accepts all the attributes declared earlier.

Sub CreateHomerAsPerson()
'Define Homer as aPerson with the data type

    'Declarations
    Dim Homer As Person  ' Declare a variable as Person
 
    'Assign a value to an element of the type
    Homer.Name = "Homer"
    Homer.Surname = "Simpsons"
    Homer.Age = 40
    Homer.Country = "US"
    Homer.City = "Springfield"
    Homer.Address = "742 Evergreen Terrace"
    Homer.Quote = "D'oh"
    Homer.PhoneNumber = "TBD"
    Homer.Mail = "homer.sim@simpsons.com"
    Homer.Status = "Works at nuclear power plant"

    'Display data
    MsgBox Homer.Name & " " &  Homer.Surname & " lives in " & Homer.Address _ 
         & " and is " & Homer.Age & "."
      

End Sub

Create an Array of the Type Statement

You can also declare an array of your custom type. It allows you to store each object in a collection.

In the macro example below, we declare a collection of ten people, in the population range.

Note that, we did not use the base 1 option at the start of the module, so the first item in the table is element zero.

The data for each person being distributed by column in a given file, we loop on each line to store the information in our custom type table named person.

Sub CreatePopulation()
'Create an array of a define type Person
    
    'Declarations
    Dim i,k As Integer
    Dim Population(10) As Person
   
    'Assign a value to each element of the type
    k=1
    For i = 2 To 10
        
        j = 0 'the Population array begin at 0

        Population(j).Name = Cells(i, 1).Value2
        Population(j).Surname = Cells(i, 2).Value2
        Population(j).Age = Cells(i, 3).Value2
        Population(j).Country = Cells(i, 4).Value2
        Population(j).City = Cells(i, 5).Value2
        Population(j).Address = Cells(i, 6).Value2
        Population(j).Quote = Cells(i, 7).Value2
        Population(j).PhoneNumber = Cells(i, 8).Value2
        Population(j).Mail = Cells(i, 9).Value2
        Population(j).Status = Cells(i, 10).Value2

        'Display data
       Cells(k,10).Value2 =  Population(j).Name & " " &  Population(j).Surname & _
           " lives in " & Population(j).Address & " and is " & Population(j).Age & "."
        k=k+1
        
        j = j + 1

    Next i
    
 
End Sub

The launch code will display in cells one after the other :

If you want to store several type variable in a single object, you can store the object collection in an array of the type. The custom type collection could also be stored as item of a key in a VBA dictionary.