Type Declartion

'
'Type Statement
'
'Used at module level to define a user-defined data type containing one or more elements.
'
'Syntax
'
'[Private | Public] Type varname
'elementname [([subscripts])] As type
'[elementname [([subscripts])] As type]
'. . .
'
'End Type
'
'The Type statement syntax has these parts:
'
'Part Description
'Public Optional. Used to declare user-defined types that are available to all procedures in all modules in all projects.
'Private Optional. Used to declare user-defined types that are available only within the module where the declaration is made.
'varname Required. Name of the user-defined type; follows standard variable naming conventions.
'elementname Required. Name of an element of the user-defined type. Element names also follow standard variable naming conventions, except that keywords can be used.
'subscripts When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.
'type 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.

Option Explicit
'Type Declartion(User Define Type) at module level only
Type Address
    NameNumber As String
    Street As String
    Town As String
    Country As String
    PostCode As String
End Type

Type Contact
    Title As String
    FirstName As String
    LastName As String
    DateofBirth As Date
   
    HomeAddress As Address
    WorkAddress As Address
End Type

Private Type Student

    Name As String
    Hindi As Integer
    Eng As Integer
    Math As Integer
    Science As Integer
    Behaviour As Behaviours
End Type

Private Enum Behaviours
    Good
    Bad
    Naughty
    Ugly
    Sweet
End Enum

Private Sub TestStudentType()

Dim NewStudent As Student

NewStudent.Name = "Ridhi"
NewStudent.Hindi = 45
NewStudent.Math = 63
NewStudent.Eng = 85
NewStudent.Science = 96
NewStudent.Behaviour = Good
NewStudent.Behaviour = Bad

MsgBox NewStudent.Behaviour     ' Enum output are in numbers start from 0, use SELECT CASE _
                                        to display as string value

Worksheets("Type Declaration").Activate

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

ActiveCell.value = NewStudent.Name
ActiveCell.Offset(0, 1).value = NewStudent.Hindi
ActiveCell.Offset(0, 1).value = NewStudent.Eng
ActiveCell.Offset(0, 1).value = NewStudent.Math
ActiveCell.Offset(0, 1).value = NewStudent.Science

End Sub

Sub TestContact()

Dim C As Contact

C.FirstName = "Candy"
C.HomeAddress.NameNumber = "90"

MsgBox C.FirstName
MsgBox C.HomeAddress.NameNumber
End Sub