Creating a userdefined data type

A new data type can be defined using the Type statement. In example below a user-defined type is required to represent an employee record. Notice how multiple variables have been combined into a single variable - each part is known as a 'field' (like a property of an object). When the definition is complete, an End Type statement is required. Variables of the new data type may then be used in exactly the same way as a normal base type variable, that is, using the Dim statement. The syntax creating a user-defined data type is:

Type Name

FieldNamel As ... FieldName2 As. Etc. End Type

An example of creating a user-defined data type and variable of that type:

'this example defines a new data type called Employee Record

Type EmployeeRecord FirstName As String * 10

'First field, called FirstName is fixed length 10 characters LastName As String * 20 'LastName is fixed length 20 characters TelNumber As String * 12 'TelNumber, fixed length 12 characters Salary As Currency 'Fourth item in record is called Salary

StartDate As Date 'Last item is called StartDate

End Type

'The next part shows how to use the new data type - i.e. declaring variables of that type to use in the macro

Dim anEmployee As EmployeeRecord 'declaration of variable anEmployee.FirstName = "John"

'assignment - i.e. put John into the FirstName field

The above fragment illustrates how a data type called EmployeeRecord is defined and how a new variable (anEmployee) of this data type can be created. A component of this variable has been assigned for use in the program, e.g. anEmployee.FirstName = "John".

Listing 5.2 gives an example of a VBA macro using a user defined type.

Listing 5.2 User-defined data type example

10 11 12

'Course Name 'Unit Name 'Number of Students

Type Course

CourseName As String Unit As String NosOfStudents As Integer End Type

Sub getCourseDetails()

Dim MyCourse As Course —— MyCourse.CourseName = "BSc BIT" MyCourse.Unit = "Artificial Intelligence" MyCourse.NosOfStudents = 85 MsgBox "I am on the " & _

MyCourse.CourseName & " course. I am taking the" _ & MyCourse.Unit & "Unit, there are " & _ MyCourse.NosOfStudents & " students taking this unit!" End Sub

Components or fields of the new data type

User-defined data type variable declared and values assigned to its fields

Lines 12 to 14 should be typed as one continuous line

Fields used in MsgBox string

Microsoft Excel


I am on the BSc BIT course. I am taking the A

rtificial Intelligence Unit, there are 85 students taking this unit!

{- OK i|

Figure 5. 4 Screenshot of user-defined type example

0 0

Post a comment