Introduction.
This is the second post on User-Defined Data Types (UDTs).
You can find the link to the first post on this topic [here].
The need for User-Defined Data Types arose when we used two-dimensional arrays of the Variant data type to store different kinds of values (such as String, Integer, and Double) in each element of the array. The Variant data type can automatically adjust its type depending on the value assigned to it.
Instead of using a single Variant variable with a multi-dimensional array, we could also use several separate single-dimensional variables of different data types as an alternative. For most simple data-processing tasks, these approaches are more than sufficient.
However, learning new techniques is always exciting in programming.
The User-Defined Data Type is one such interesting feature in VBA, and in this post, we will explore how to create and use it in our programs.
How to Define a User-Defined Data Type
The steps go something like this:
@@@You can define a new data type that combines multiple built-in variable types—such as Integer, Long, Double, and String—into a single structure. This User-Defined Data Type (UDT) must be declared within a Type ... End Type block at the module level, typically at the beginning of a Standard Module, before any procedures.
The following example defines a UDT named myRecord with two elements: RecordID (of type Long) and Description (of type String):
Public Type myRecord RecID as Long Description as String End TypeBy default, a User-Defined Type (UDT) has Public scope, though explicitly declaring it as Public or Private is optional. If declared as Private, the UDT is accessible only within the same module where it is defined. In that case, variables such as Dim AbcRec As myRecord can be declared and used only inside that module.
When declared with the default Public scope, the UDT becomes available to all modules within the current project, and even to other projects that reference this database.
Let’s begin with a simple example:
Type Sales Desc As String Quantity As Long UnitPrice As Double TotalPrice As Double End TypeThe dataType name is Sales.
As you can see in the Sales data type, we have used built-in data Types String, Long Integer, and Double for different data elements.
Using the User-Defined variable in the program starts with dimensioning a Variable of Type Sales, like any other variable.
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
Result printed in the Debug Window:
iPhone 8 Plus 1 75000 75000Unlike built-in variables, the elements of a User-Defined Type are always accessed by specifying the Type name, followed by a dot, and then the element name (for example: mySales.Desc). In this way, the Desc, Quantity, UnitPrice, and TotalPrice elements are treated as individual properties of mySales.
To make the code cleaner and more flexible, we can place these element references inside a With… End with structure. This allows us to enter data directly from the keyboard into each element of the Sales record using the InputBox() function.
Public Function typeTest()
Dim mySales As Sales
With mySales
.Desc = InputBox("Item Description: ")
.Quantity = InputBox("Item Quantity: ")
.UnitPrice = InputBox("Item Unit Price: ")
.TotalPrice = .Quantity * .UnitPrice
End With
'Print the values on Debug Window
With mySales
Debug.Print .Desc, .Quantity, .UnitPrice, .TotalPrice
End With
End Function
The modified code will get information from one Record and print it out on the Debug Window. Before running the Code, open the Debug Window (Ctrl+G) to view the output.
Arrays of User-Defined Type
Next, we will define an array of mySales variables to store information for five different items. We will then pass this array of user-defined variables—each containing multiple data elements—to the SalesPrint() function as a parameter. The SalesPrint() function will calculate and update the TotalPrice element for each item before printing the array values in the Debug Window.
(Keep the Debug Window open while running the program.)
The sample VBA code for this program is given below:
Public Function SalesRecord()
Dim mySales(5) As Sales
Dim j As Integer, strLabel As String
For j = 0 To UBound(mySales) - 1
strLabel = "(" & j + 1 & ") "
With mySales(j)
.Desc = InputBox(strLabel & "Item Description:")
.Quantity = InputBox(strLabel & "Quantity:")
.UnitPrice = InputBox(strLabel & "UnitPrice:")
.TotalPrice = 0
End With
Next
Call SalesPrint(mySales())
End Function
Check the Dim statement—it works like any other array definition. We also declare two additional variables: j and strLabel. The variable j serves as the control variable in the For…Next loop, while strLabel is used to construct and store a label such as (1), (2), etc., which appears in the InputBox() prompt. This label helps identify the current record number as we enter data into each record.
We have chosen meaningful names for the array elements—Desc, Quantity, and UnitPrice—instead of using array index numbers like Sales(0,0) for Description or Sales(0,1) for Quantity. The 'MySales(j).TotalPrice' element is initially assigned 0; its value will be calculated and updated in the SalesPrint() function. The array is passed to SalesPrint() as a ByRef parameter.
The SalesPrint() Function.
The SalesPrint() function Code is given below:Public Function SalesPrint(ByRef PSales() As Sales) Dim j As Integer, strLabel As String Debug.Print "Description", " ", "Quantity", "UnitPrice", "Total Price" For j = 0 To UBound(PSales) - 1 strLabel = "(" & j + 1 & ") " With PSales(j)
'calculate TotalPrice
.TotalPrice = .Quantity * .UnitPrice 'print the values in debug window Debug.Print strLabel & .Desc, " ", .Quantity, .UnitPrice, .TotalPrice End With Next End Function
The SalesPrint() function receives the Sales Record array by reference through the PSales variable. Within the function, two local variables are defined: j as an Integer and strLabel as a String. The function begins by printing a header line in the Debug Window, setting up proper column headings for displaying the sales record details.
When printing the data, commas are used to separate each item, placing them in 14-column zones on the same line. To allow the Item Description to occupy more space, an empty string is inserted as the second item on the print line. This effectively moves the Quantity value to the 28th column while keeping the layout neat and aligned.
Next, the For... next loop is used to access each record in memory, with the control variable j serving as the array index. The loop runs from 0 to 4, processing all five records.
The first line inside the loop constructs a label in the format (1), (2), and so on, to indicate the sequence of records as they were entered using the InputBox() function.
The next statement uses a With... End with structure on the PSales(j) record. This allows direct access to its elements (Desc, Quantity, etc.) without repeatedly referencing the top-level array name.
Within the With block, the TotalPrice element is calculated and assigned its value. The following line then prints the current record to the Debug Window. This process repeats for each record, ultimately displaying all items in the array.
By now, you should have a good understanding of the usefulness of User-Defined Types. With a little further exploration, you can even save these records from memory into an Access table.
Keep in mind that a User-Defined Type is usually designed for a specific task, and its structure may not be suitable for general-purpose use like built-in variables. For example, elements such as Desc, Quantity, UnitPrice, and so on, may not be relevant outside the context for which the type was created.
There are, however, many interesting ways to leverage User-Defined Types, and we will continue this discussion in next week’s post.
