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.

Forest of Saint-Germain-En-Laye, France – Photo taken by me

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.

NameSurnameAgeCountryCityAddressQuotePhone NumberMailStatus
HomerSimpson40USSpringfield742 Evergreen TerraceD’oh TBDhomer.sim@simpsons.simWorks at nuclear power plant
MargeSimpson36USSpringfield742 Evergreen TerraceHomeeeerTBDmarge.sim@simpsons.comHousewife
BartSimpson10USSpringfield742 Evergreen TerraceEl Barto TBDbart.sim@simpsons.comSchoolchild
MaggieSimpson1USSpringfield742 Evergreen TerraceAreuhTBDmag.sim@simpsons.comBaby
LisaSimpson8USSpringfield742 Evergreen Terrace TBDlisa.sim@simpsons.comSchoolchild
MoeSzyslak?USSpringfield57 Walnut Street TBDmoe.sim@simpsons.comBarman at Moe’s Tavern
Charles MontgomeryBurns104USSpringfield1000 Mammon StreetExcellentTBDcharles.sim@simpsons.comSpringfield nuclear power plant owner
Apu Nahasapeemapetilon39USSpringfieldTBD TBDapu.sim@simpsons.comOwner/operator of the Kwik-E-Mart
NedFlanders39USSpringfield740 Evergreen Terrace TBDned.sim@simpsons.comVendor at the Palais du Gaucher
MilhouseVan Houten10USSpringfieldTBD TBDmilh.sim@simpsons.comSchoolchild

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 ByteBooleanIntegerLongCurrencySingleDouble, Decimal (not currently supported), DateString (for variable-length strings), String length (for fixed-length strings), ObjectVariant, 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

    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
    Dim i,k As Integer
    Dim Population(10) As Person
    'Assign a value to each element of the type
    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 & "."
        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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s