In my last week’s Blog post I elaborated on data types and their use in VBA.
Beyond using the readily available native data types we’re all familiar with, such as Integer, Date, String and Boolean, we can define our own, user-defined data type.
Simply put, a user-defined data type is a group of variables of native data types.
Here’s an example of grouping together several variables to define a new data type representing (or holding) information about a vehicle:
VIN as Long
Make as String
Model as String
Year as Integer
That’s it! As simple as that.
We can now dimension variables of type Vehicle:
Dim vehLeasedCar as Vehicle
We now have a kind of a hierarchy: a variable of type Vehicle containing sub-variables of different (native) types: Long, String and Integer in this example.
We can think of the sub-variables as properties of the Vehicle-type variable and use the “dot” to address each of these sub-variables. Let’s assign some values to our vehLeasedCar variable:
vehLeasedCar.VIN = 4656418
vehLeasedCar.Make = “Ford”
vehLeasedCar.Model = “Taurus”
vehLeasedCar.Year = 2001
In the same way, we can read the values of our variable (or its sub-variables). Let’s print out our vehicle’s model:
I can think of two main reasons to make use of user-defined data types:
Think of a process that needs to store and manipulate 5,000 vehicles.
One way would be to have a two-dimensional array store a table-like structure of the vehicles. This is valid (and you know how much I love arrays), and even performant. However, it would not be that clear by viewing the code what we are doing. What do you find more telling in your code: arrCars(i,j) or arrCars(i).Model?
Let’s define another data type to store information about an employee:
Private Type TEmployeeRecord
Name As String
DOB As Date
Age As Integer
City As String
Score As Integer
Consider how readable and self-explanatory the following Sub is, tasked with printing out all data of an employee, passed over as a TEmployeeRecord type variable:
Sub PrintEmployeeReport(employee As TEmployeeRecord)
Debug.Print .Name, .City, .DOB, .Age, .Score
Of course, storing a single record, as in the above examples, is not very helpful. We typically need to store many records of data in our program.
For this, we can combine our own defined data type (record structure) with a one-dimensional array.
To illustrate this in an example, let’s pick up a list of employees with some data on them from an Excel table and arrange it in an array of employees’ records. Our table look like this:
We first use a 2-dimensional array as an interim structure to quickly read the table from the Worksheet:
Dim arrEmployees() As Variant
arrEmployees = ThisWorkbook.Worksheets("Scores").Range("ScoresTable[#Data]").Value
Next, we loop our array and transfer each employee (“row”) in the array into a new, 1-dimentional array, holding employees’ records:
Dim employees(5) As TEmployeeRecord
Dim i As Integer
For i = LBound(arrEmployees, 1) To UBound(arrEmployees, 1)
With employees(i - 1)
.Name = arrEmployees(i, 1)
.DOB = arrEmployees(i, 2)
.Age = arrEmployees(i, 3)
.City = arrEmployees(i, 4)
.Score = arrEmployees(i, 5)
With that, we have an efficient array of employees to work with.
We can now write a function to return the score of an employee, given a pointer to the array of employees we prepared and the employee name to look for.
Function GetScoreOfEmployee(strName As String, employees() As TEmployeeRecord) As Integer
Dim i As Integer
GetScoreOfEmployee = 0
For i = LBound(employees) To UBound(employees)
If (.Name = strName) Then
GetScoreOfEmployee = .Score
See how elegant and clear this function is? We’re looping the employees array, checking each employee name to match strName we’re looking for, returning his score upon a successful match.
Here’s how we can make use of the above function to ask the user for a name of an employee and get his score (we’ll store his score in the intScore variable). We’re making use of the employees array we have populated earlier with our employees:
Dim strName As String
Dim intScore As Integer
strName = InputBox("Name of employee:", "Query Employee Form")
intScore = GetScoreOfEmployee(strName, employees)
If (intScore = 0) Then
MsgBox "Employee not found", vbOKOnly + vbInformation, "Employee Error"
MsgBox strName & "'s score: " & intScore, vbOKOnly + vbInformation, "Employee Score Result"
Unfortunately, Excel VBA doesn’t allow us to add user-defined type variables to a collection, therefore we’re missing out on a potentially very useful and efficient data structure. One can argue that if a collection is our best structure to maintain our records, we can implement our records as objects defined in a Class Module instead of a user-defined data type. Yes, I have a series of Blog posts about objects and Class Modules starting right here.
The above examples are featured in my flagship on-line course: Computer Programming with Excel VBA, in case they seemed familiar to you 😉.
Hey, a small request from me to you: please share this Blog post so that we can help more colleagues with Excel VBA.
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.