Organize Code with User-Defined Types in VBA

VBA data structures such as arrays, collections, and enumerations can help organize large and complex sets of data. When drilling down to specifics within data, it can be somewhat difficult to work with these structures. VBA offers another data structure, User-Defined Types, which provide a more intuitive and user friendly means of writing and accessing data.

Overview

A User-Defined Type (also commonly referred to as a Custom Type, VBA Type, or the acronym UDT) is a data structure that can be used to group many variables within a single identifier. User-Defined Types can be thought of as a cross between an Enumeration and a traditional variable. Both User-Defined Types and Enumerations can store multiple values, both use the dot operator and descriptive identifiers. Unlike Enumerations, User-Defined Types behave more like variables: they can store any data type (not just numeric long values), and associated values can be modified at any point within a script.

A User-Defined Type can also be seen as closely related to a class, summarized below.

Sample File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

VBA Class vs. VBA User-Defined Type

If you’re following this series from start to finish, you haven’t seen our tutorials on building VBA classes. Regardless, it’s worth comparing the features between User-Defined Types and Classes. A Class permits the storage of many variables (properties) and subroutines or functions (methods) in a single object. While a User-Defined Type does permit the storage of many variables, it cannot be associated with any custom functions or subroutines.

Why use a User-Defined Type at all when a Class packs more features? It’s a matter of preference and clarity in code. A Class requires its own module, and having many Classes may be slightly unwieldy and difficult to keep track of. Some advantages of User-Defined Types over Classes are:

  • User-Defined Types are initiated immediately after they are declared (classes need to be initiated in a separate “set” statement).
  • User-Defined Types are defined at the top of modules where they are accessed – this makes it easy to keep track of a type’s scope and the subroutines/functions which it impacts.
  • User-Defined Types do not need individual Get or Let statements for assigning or calling values.

In summary, while a Class is more robust with its ability to encapsulate members, the ease of use of a User-Defined Type makes it a better alternative for basic storing values.

Using User-Defined Types

While VBA’s built-in data types are easily accessible and can be used to declare variables with no setup, there is one extra step to take when working with User-Defined Types. VBA needs to be provided with a basic framework of a User-Defined Type’s structure, commonly referred to as a definition.

Defining and Declaring User-Defined Types

VBA User-Defined Types must be defined in the declarations section of a module. The definition must include the UDT identifier (name), as well as the identifier (name) of each element which will be used for storage. This gives VBA the framework for the general structure, but no instance of the User-Defined Type variable is initiated at this point.

The actual declaration of a User-Defined Type variable must be done within a subroutine or function, or within a module’s declaration area (for global access).

The following snippet a declaration of a basic User-Defined Type of type Student_Data.

Assigning and Accessing Values in a User-Defined Type

Within a procedure, use the dot operator between the UDT identifier and specific element. Upon entering the dot operator, VBA’s intellisense makes an appearance and displays all elements of the User-Defined Type.

Intellisense will display a list of elements for User-Defined Types.
Intellisense will display the elements of User-Defined Type.

The following assigns the value “Clippy” to the name element of User-Defined Type studentOne.

There are no surprises when it comes to pulling a value from a User-Defined Type. Like all other data structures and variables, User-Defined Types can be printed on a spreadsheet, message box, or used for further functionality in a script. The following will print the value assigned to studentThree.name on cell A4.

One real strength of a User-Defined Type is the ability to create multiple instances of the data type, similar to any built-in data types. The following example puts it to practice, with variable studentOne declared as a global “Student_Data” User-Defined Type, and variables studentTwo and studentThree are declared as local “Student_Data” User-Defined Types within their a module. Each instance will store its own set of values.

Building an Array with a User-Defined Type

Similar to Excel’s built-in data types, User-Defined Types can be built into arrays. Building on the previous example, we can circumvent the unwieldy approach of writing a line for each individual value by incorporating an array into the User-Defined Type. The following procedure builds an array using the User-Defined Type “Student_Data”.

This approach has a few advantages over the previous “brute force” approach. The array can be declared in one line, and there’s no need to track multiple variable names. While the example data used above is manageable with just three students, things could get quite cumbersome with 25 or more students. This is particularly useful when printing the values, as a simple For loop can cycle through each element in the array. While values were explicitly assigned in this example, assignment could also be streamlined through a loop reading from another data source, or a user input form.

Other Notes

  • Even though For Each loops are an excellent means of cycling through arrays, a For Each loop cannot be used to cycle through arrays storing User-Defined Types — a basic For loop is needed.

2 Comments

    1. Yes, it is possible. You can treat the user-defined type as if it were an object. Using the studentOne variable in the example file, the following would work with a with-end-with statement.

      With studentOne
      Sheet1.Cells(2, 1).Value = .name
      Sheet1.Cells(2, 2).Value = .grade
      Sheet1.Cells(2, 3).Value = .average
      End With

Leave a Reply