Excel VBA: User Defined Data Types

Choosing the right data structures as we plan our program is especially important. Molding our data to be structured to our specific requirements makes our program more readable, maintainable, and efficient. What can we do beyond integer, string, and date?

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.

What is a 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:

Type Vehicle

   VIN as Long

   Make as String

   Model as String

   Year as Integer

End Type

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:

Debug.Print vehLeasedCar.Model

Why use User-Defined Data Types?

I can think of two main reasons to make use of user-defined data types:

1. They are suitable for records arrangement in a readable format
2. They are very efficient to process, even more than collections

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

End Type

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)

    With employee

        Debug.Print .Name, .City, .DOB, .Age, .Score

    End With

End Sub

Storing Many Records in an Array

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)

        End With

Next i

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)

        With employees(i)

            If (.Name = strName) Then

                GetScoreOfEmployee = .Score

                Exit For

            End If

        End With

    Next i

End Function

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"

Else

   MsgBox strName & "'s score: " & intScore, vbOKOnly + vbInformation, "Employee Score Result"

End If

By the way, if you are not familiar with the InputBox function, I have a detailed Blog post about it for you here. Similarly, the MsgBox function is explained here.

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.