Introduction.
Last week, we learned how to define a User-Defined Data Type (UDT) and how to use it in programs. If you’ve just landed on this page, it’s recommended that you first go through the earlier post “User-Defined Data Type–2” before proceeding further.
A user-defined type is declared within the [Private/Public] Type… End Type structure. Immediately after the Type keyword, you assign a name to the data type. The actual data-holding variables are defined as individual elements inside the structure, typically using built-in variable types such as String, Integer, Long, Double, or Variant.
In addition to built-in variable types, you can also use other user-defined types as elements. These act as child elements within a user-defined type — and that is what we explore here.
Combining Different sets of User-Defined Types.
First, we will declare several User-Defined Types (UDTs) to represent different categories of information for employee records — such as Home Address, Qualification, and Experience.
Each of these groups will be defined separately, with its own related data elements. Later, we will combine these groups, along with other individual fields, under a single UDT named Employee.
The following layout shows how these individual groups of employee information are structured separately, before being organized together under the common Employee user-defined type.
| 1. | Qualification |
| ||||||
| 2. | Experience |
| ||||||
| 3. | Address and Date of Birth |
| ||||||
| 4. | Employee Details |
|
Declaring the Data Types
First, let us declare the Data Types:
Option Compare Database
Public Type Qualification
Q_Desc1 As String
Q_Desc2 As String
End Type
Public Type Experience
X_Desc1 As String
X_Desc2 As String
End Type
Public Type BioData
Address1 As String
Address2 As String
City As String
State As String
PIN As String
BirthDate As Date
End Type
Public Type Employee
E_Name As String * 45
E_Desig As String * 25
E_JoinDate As Date
E_PerfScore(1 To 12) As Double
E_Salary As Double
End Type
BioData Re-Defined with Qualification and Experience.
The Qualification and Experience user-defined types are now incorporated as child elements within the BioData type.
After this modification, the structure of the BioData type will look like the code shown below.
Public Type BioData Address1 As String Address2 As String City As String State As String PIN As String BirthDate As Date Q_Desc As Qualification X_Exp As Experience End Type
The Qualification user-defined type contains two elements of the String data type. Similarly, the Experience type also includes two String elements.
The BioData type consists of address details and date of birth as its own elements. In addition, the Qualification and Experience types are embedded as child elements within the BioData type.
Employee Data Type combined with modified BioData Type
We will now define the BioData data type as a child element of the Employee data type. The BioData element will contain two sub-elements: Qualification and Experience.
The Employee Data Type with BioData Type as a child element.
Public Type Employee E_Name As String * 45 E_Desig As String * 25 E_JoinDate As Date E_PerfScore(1 To 12) As Double E_Salary As Double B_BioData As BioData
End Type
Now you can see how all these elements fit together to form a complete Employee record.
The Employee data type consists of four elements:
E_Name – A String type, with a maximum length of 45 characters.
Designation – A String type, with a maximum length of 25 characters.
JoinDate – A Date type field to store the date of joining.
PerformanceScore – An array of 12 elements (numeric values) to store the employee’s monthly performance evaluation scores, recorded by the management on a scale of 10.
Note: The string length specifications (like 45 or 25) are arbitrary. You may keep them as they are or simply declare the fields as String without specifying a length.
The Qualification and Experience data types were declared first, and then inserted as child elements of the BioData data type.
The BioData data type itself was declared above the Employee type, before being inserted into it as a child element.
Caution.
Ensure that you are not placing a Type declaration within itself, as this would create a circular reference and is not allowed.
Now that we are ready to work with this complex data structure, the first question that naturally comes to mind is:
How do we reference each element to assign values to it?
Sample Test Program for Employee Data Type.
We will now write a small program to test the Employee data type and assign values to each element of this nested complex data structure.
However, it is not as complicated as it may sound. If you find it difficult to follow, try creating simpler examples on your own based on your current level of understanding.
The program code is given below.
Observe carefully how each element is referenced when assigning values to it.
Public Function EmplTypeTest() Dim Emp As Employee Emp.E_Name = "John" Emp.E_Desig = "Manager" Emp.E_JoinDate = #01/01/2018# Emp.E_PerfScore(Month(Date) - 1) = 4.5 Emp.E_Salary = 40000 'BioData Emp.B_BioData.Address1 = "115/8" Emp.B_BioData.Address2 = "Olencrest," Emp.B_BioData.City = "Columbus" Emp.B_BioData.State = "Ohio" Emp.B_BioData.PIN = "43536" Emp.B_BioData.BirthDate = #9/29/1979# 'Qualifications Emp.B_BioData.Q_Desc.Q_Desc1 = "Degree in Computer Science" Emp.B_BioData.Q_Desc.Q_Desc2 = "PG Degree in Computer Science" 'Experience Emp.B_BioData.X_Exp.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." Emp.B_BioData.X_Exp.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
Call ListEmp(Emp) End Function
As you can see in the code above, each element is referenced using its fully qualified name, clearly indicating its hierarchical position within the Employee data structure.
The same code is shown again below, but this time using full object references enclosed within With ... End With statements, where the XXXX part represents the hierarchical object names.
Public Function EmplTypeTest0()
Dim Emp As Employee
With Emp
.E_Name = "John"
.E_Desig = "Manager"
.E_JoinDate = #01/01/2018#
.E_PerfScore(Month(Date) - 1) = 4.5
.E_Salary = 40000
End With
'BioData
With Emp.B_BioData
.Address1 = "115/8"
.Address2 = "Olencrest,"
.City = "Columbus"
.State = "Ohio"
.PIN = "43536"
.BirthDate = #9/29/1979#
End With
'Qualifications
With Emp.B_BioData.Q_Desc
.Q_Desc1 = "Degree in Computer Science"
.Q_Desc2 = "PG Degree in Computer Science"
End With
'Experience
With Emp.B_BioData.X_Exp
.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
End With
Call ListEmp(Emp)
End Function
Referencing individual Element Variables of Employee Data Type.
Check how the With... End With statements are structured, and how the object references are given in the correct hierarchical order to reach each element variable.
In the revised version of the code shown below, the With... End With blocks are nested, and each block uses only the nearest parent object name to access its data elements.
Public Function EmplTypeTestA() Dim Emp As Employee With Emp .E_Name = "John" .E_Desig = "Manager" .E_JoinDate = #01/01/2018# .E_PerfScore(Month(Date) - 1) = 4.5 .E_Salary = 40000 'B_BioData With Emp.B_BioData .Address1 = "115/8" .Address2 = "Olencrest," .City = "Columbus" .State = "Ohio" .PIN = "43536" .BirthDate = #9/29/1979# 'Qualifications With .Q_Desc .Q_Desc1 = "Degree in Computer Science" .Q_Desc2 = "PG Degree in Computer Science" End With 'Experience With .X_Exp .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise." End With
End With ‘ Emp.B_BioData End With ‘ Emp Call ListEmp(Emp) End Function
You can use any one of the three programs shown above—or run all of them one by one—before executing the following printing program, which will display the data in the Debug Window.
Public Function ListEmp(ByRef EmpList As Employee)
With EmpList
Debug.Print "Name: ", , .E_Name
Debug.Print "Designation: ", , .E_Desig
Debug.Print "Join Date: ", , .E_JoinDate
Debug.Print "Performance Score July: ", .E_PerfScore(8)
Debug.Print "Salary: ", , .E_Salary
Debug.Print "Address1: ", , .B_BioData.Address1
Debug.Print "Address2: ", , .B_BioData.Address2
Debug.Print "City: ", , .B_BioData.City
Debug.Print "State: ", , .B_BioData.State
Debug.Print "PIN: ", , .B_BioData.PIN
Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
End With
End Function
Sample Output on Debug Window.
Sample Output displayed on the Debug Window is given below:
Name: John Designation: Manager Join Date: 01-01-2018 Performance Score August: 4.5 Salary: 40000 Address1: 115/8 Address2: Olencrest, City: Columbus State: Ohio PIN: 43536 Qualification1: Degree in Computer Science Qualification2: PG Degree in Computer Science Experience1: From Jan-2010 onwards Working as Project Manager, with XYZ Company. Experience2: From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise.
Input Values through the Keyboard.
If you would like to try out an array example, then copy-paste the following two programs into a Standard Module and run the first code.
Public Function EmplTypeTestB()
Dim Emp(1 To 3) As Employee
Dim j As Integer, strlabel As String
For j = 1 To 3
With Emp(j)
strlabel = "( " & j & " )"
.E_Name = InputBox(strlabel & "Name:")
.E_Desig = InputBox(strlabel & "Designation:")
.E_JoinDate = InputBox(strlabel & "Join Date:")
.E_PerfScore(Month(Date) - 1) = InputBox(strlabel & "Performance Score:")
.E_Salary = InputBox(strlabel & "Salary:")
'B_BioData
With Emp(j).B_BioData
.Address1 = InputBox(strlabel & "Address1:")
.Address2 = InputBox(strlabel & "Address2:")
.City = InputBox(strlabel & "City:")
.State = InputBox(strlabel & "State:")
.PIN = InputBox(strlabel & "PIN:")
.BirthDate = InputBox(strlabel & "Birth Date:")
'Qualifications
With .Q_Desc
.Q_Desc1 = InputBox(strlabel & "Qualification-1:")
.Q_Desc2 = InputBox(strlabel & "Qualification-2:")
End With
'Experience
With .X_Exp
.X_Desc1 = InputBox(strlabel & "Experience-1:")
.X_Desc2 = InputBox(strlabel & "Experience-2:")
End With
End With
End With
Next
Call ListEmp2(Emp)
End Function
The InputBox() function allows you to enter the details of three employees directly from the keyboard, based on the prompts displayed for specific values. In the final statement, Call ListEmp2(Emp) runs the following code using the employee records array and prints the output in the Debug Window. Make sure the Debug Window is open (Ctrl+G).
Public Function typeTest() Dim mySales As Sales mySales.Desc = "iPhone 8 Plus" mySales.Quantity = 1 mySales.UnitPrice = 75000# mySales.TotalPrice = mySales.Quantity * mySales.UnitPrice Debug.Print mySales.Desc, mySales.Quantity, mySales.UnitPrice, mySales.TotalPrice End Function
Printing Program.
Public Function ListEmp2(ByRef EmpList() As Employee)
Dim j As Integer, strlabel As String
Dim lower As Integer
Dim upper As Integer
lower = LBound(EmpList)
upper = UBound(EmpList)
For j = lower To upper
With EmpList(j)
Debug.Print
Debug.Print "=== Employee: " & .E_Name & " Listing ==="
Debug.Print "Name: ", , .E_Name
Debug.Print "Designation: ", , .E_Desig
Debug.Print "Join Date: ", , .E_JoinDate
Debug.Print "Performance Score " & MonthName(Month(Date) - 1) & ": ", .E_PerfScore(8)
Debug.Print "Salary: ", , .E_Salary
Debug.Print "Address1: ", , .B_BioData.Address1
Debug.Print "Address2: ", , .B_BioData.Address2
Debug.Print "City: ", , .B_BioData.City
Debug.Print "State: ", , .B_BioData.State
Debug.Print "PIN: ", , .B_BioData.PIN
Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
End With
Next
End Function
I hope you will experiment with User-Defined Types (UDTs) in your projects and explore their strengths and limitations.
The main drawback of UDTs is the lack of a built-in mechanism to validate the data assigned to their elements. For example, if a future date is entered into the Date of Birth element, there is no built-in validation to alert the user that the value is invalid. Similarly, if a negative value is entered into a Salary field, it will be accepted without any error. Wherever data validation is required, you must write separate validation code each time you use a UDT.
A better approach is to use Class Modules. In a Class Module, you can define each data element as a property and include validation logic within its Set property procedures to ensure data integrity before accepting the values. You can also create Functions or Subroutines within the Class Object to handle common operations on the data, and simply call them from your programs. All of this logic remains encapsulated within the Class Module itself, so you don’t need to rewrite it separately every time.
In the next section, we will learn how to use Class Modules to define structured data and use them effectively in programs.
Download FileCopy2007.zip
