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. Types can only have attributes of different dimensions.
Depends on your needs, you can create an object class and associate attributes and methods with it or use the type object more commonly if you only have attributes to attach. 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.
|Homer||Simpson||40||US||Springfield||742 Evergreen Terrace||D’oh||TBDfirstname.lastname@example.org||Works at nuclear power plant|
|Marge||Simpson||36||US||Springfield||742 Evergreen Terrace||Homeeeer||TBDemail@example.com||Housewife|
|Bart||Simpson||10||US||Springfield||742 Evergreen Terrace||El Barto||TBDfirstname.lastname@example.org||Schoolchild|
|Maggie||Simpson||1||US||Springfield||742 Evergreen Terrace||Areuh||TBDemail@example.com||Baby|
|Lisa||Simpson||8||US||Springfield||742 Evergreen Terrace||TBDfirstname.lastname@example.org||Schoolchild|
|Moe||Szyslak||?||US||Springfield||57 Walnut Street||TBDemail@example.com||Barman at Moe’s Tavern|
|Charles Montgomery||Burns||104||US||Springfield||1000 Mammon Street||Excellent||TBDfirstname.lastname@example.org||Springfield nuclear power plant owner|
|Apu||Nahasapeemapetilon||39||US||Springfield||TBD||TBDemail@example.com||Owner/operator of the Kwik-E-Mart|
|Ned||Flanders||39||US||Springfield||740 Evergreen Terrace||TBDfirstname.lastname@example.org||Vendor at the Palais du Gaucher|
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. As for a loop, 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 = "email@example.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 :
|Homer Simpson lives in 742 Evergreen Terrace and is 40.|
|Marge Simpson lives in 742 Evergreen Terrace and is 36.|
|Bart Simpson lives in 742 Evergreen Terrace and is 10.|
|Maggie Simpson lives in 742 Evergreen Terrace and is 1.|
|Lisa Simpson lives in 742 Evergreen Terrace and is 8.|
|Moe Szyslak lives in 57 Walnut Street and is ?.|
|Charles Montgomery Burns lives in 1000 Mammon Street and is 104.|
|Apu Nahasapeemapetilon lives in TBD and is 39.|
|Ned Flanders lives in 740 Evergreen Terrace and is 39.|
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.