User Defined Data Type-2

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:

1. @@@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.

2. 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 Type
   

   By 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 Type
   

   The 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         75000 
   

   Unlike 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.

• Macros and Temporary Variables
• Product Group Sequence with Auto-Numbers
• RunSql Action in Macro and VBA
• External Reference in Conditional Formatting
• Overlaying Sub-Forms in Real-time

Read More

Passing Two Dimensional Array to Function

Introduction.

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS, has been selected by the https://blog.feedspot.com panelist as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

You can find some of the top-ranked Blogs on a variety of subjects, RSS Feeds, YouTube Channels,  top News Websites, and others.  Subscribe to Blogs/News Feeds, or topics of any other area of interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when they happen, by providing your email address.

Returning to our VBA lessons, last week we briefly discussed passing arrays as parameters to a Function using the ByRef method. This allowed us to work directly with the original array inside the called function and sort its values in descending order.

In our example, we used an array containing values for only five elements, but in practice, an array can hold many rows and columns of data.

The Re-Dimension (ReDim) Statement.

An array can be re-dimensioned multiple times during program execution to increase or decrease the number of rows if its size cannot be determined in advance. In such cases, you should omit the element count in the initial Dim statement.

Example:

'Cannot Re-dimension pre-defined Arrays
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'Re-dimension this Array later for required  
'Number of elements, not known in advance. 
.
Dim Products() as String
.
'Re-Dimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
'
'OR
'In this case Array Index Number Range 1 to 5
ReDim Product(1 to 5) As String

'later on in the program
'all the values assigned to first 5 elements will be lost.
.
ReDim Products(7) As String 
.
or
.
ReDim Products(Ubound(Products)+2) As String
.
'To preserve the values already assigned to first 5 elements
.
ReDim Preserve Products(7) As String

Note: An important point to note is that any re-dimensioning of the array should be done in the calling procedure itself—before passing it to the called function if a size change is required. Although arrays are passed by reference and the called function can work directly with the passed variable, it cannot re-dimension the array to increase or decrease its number of elements from within the called function.

Two-Dimensional Array as Function Argument.

Now that we are aware of the limitations of passing arrays to a called function, let’s try passing a two-dimensional array containing Sales data to a function and printing its values in the Debug Window. Each record in the Sales data array will contain the following values:

1. Product Name - Text

2. Quantity  - Integer

3. Unit Price – Double

4. Total Value  - Double (will be calculated in the called function)

The sales records contain fields with different data types. Normally, to pass these values individually to a called function, we would need four separate array variables with different data types — for example:

1. String for Product Name

2. Integer for Quantity

3. Double for Unit Price

4. Double for Total Price

Each field’s values would be stored in its own array variable and passed separately to the function.

However, we will do this differently. Instead of using four separate arrays, we will use a single variable: a two-dimensional array of the Variant data type, with four rows (each row representing one sales record) and four columns (each column representing a field). This way, we can pass all four fields of each record together as one variable to the called function.

The Variant Data Type.

We are not storing the column names anywhere within the array. It is simply assumed that:

• The first column holds the Product Name

• The second column holds the Quantity

• The third column holds the Unit Price

• The fourth column holds the Total Price

Since we are using a Variant data type for the array, each individual cell in the array can hold different data types. A Variant variable automatically adapts its data type to match the type of value assigned to it. This flexibility allows us to store text, integers, and floating-point numbers together in the same array.

Before writing the complete function, let’s first look at the Dim statement used to define the two-dimensional array, and how the sales values are assigned to each element of the array.

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
'
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example, we have only four records (rows) in our table, and each record contains four fields (columns). Every cell in this two-dimensional array is identified by a pair of numbers—the row index and the column index—separated by a comma. The index number on the left represents the row index, and the number on the right represents the column index. Since our array has 4 rows and 4 columns, both index ranges go from 0 to 3.

Here is how the columns are arranged:

• Column 0: Product Name

• Column 1: Quantity

• Column 2: Unit Price

• Column 3: Total Value (to be calculated and assigned later)

We can pass the entire array to a function as a ByRef parameter, allowing the function to work directly with the original data.

If you are new to two-dimensional arrays, it can feel a bit confusing at first to understand how the values are arranged and how to refer to each cell. This becomes even trickier when performing calculations across multiple cells within the same row.

Fortunately, there is a better way to handle this complexity—by using User-Defined Variables. Yes, you can actually define your own variable type, in addition to the built-in types provided by VBA.

We will explore this concept in detail next week. Once you get familiar with it, you’ll find it much easier and more intuitive than juggling rows and columns. The best part is that this method scales effortlessly—you can work with 5 rows, 500 rows, or even 5000 rows using the same statements in your function.

Create the Product List Data.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

VBA Code Line by Line

We have defined the Products variable as a Variant data type with 4 rows and 4 columns, so it can hold values of different data types in each cell.

In the next line, we declare three more variables:

• j and k as control variables for the For…Next loops

• strIdx as a String variable for building text to display the index numbers of each cell in the InputBox() prompt.

We then set up two nested For … Next loops to control the index numbers of the rows and columns. The outer loop (j) controls the row index, while the inner loop (k) controls the column index.

Inside the inner loop, we use a Select Case…End Select structure to determine which field is being processed based on the current value of k:

• When k = 0, the InputBox() function prompts for the Product Name and assigns it to Products(j, 0).

• When k = 1, it prompts for the Quantity and assigns the value to Products(j, 1).

• When k = 2, it prompts for the Unit Price and assigns it to Products(j, 2).

• When k = 3, it initializes Products(j, 3) = 0 (this will later hold the calculated Total Price).

The outer loop repeats this process four times (for j = 0 to 3), and for each row, the inner loop runs four times (for k = 0 to 3), collecting values from the user for each cell in the current row.

The Output Function ProdPrint().

When the control exits the loop, the ProductPrint() function is called, passing the Products variable as a parameter.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function receives the memory reference (address) of the Products array through the ByRef method.  If the ByVal or ByRef keyword is not specified explicitly before the parameter variable, it assumes ByRef as the default. 

As in the earlier program, two integer variables, j and k, are declared as control variables for the outer and inner For…Next loops. These loops are required to navigate the array using its row and column index numbers. The loop starts at 0, and to determine the end value dynamically, we use the Ubound() (Upper Boundary) function on the array dimension. In the previous example, we directly used the values 0 to 3, but here we use Ubound() to make the program flexible. This ensures that if the array size changes later through ReDim statements, the loop still calculates the correct number of rows and columns automatically.

Usage UBound() Function to get Two-Dimensional Index Numbers.

UBound(Array,1)

The Ubound(List, 1) function returns the number of rows in the array, which is 4. However, array row indexes start from 0 in memory, so the valid index numbers are 0 to 3. The second argument (1) in the Ubound() function specifies that we want the upper bound of the first dimension (rows) of the array. Because the row index starts at 0, we subtract 1 from the total number of rows (4 − 1) when using these indexes in a For…Next loop.

UBound(Array,2)

The UBound(List, 2) function returns the number of columns in the array. The second parameter is optional—if omitted, UBound will return only the upper bound of the first dimension (rows). For single-dimensional arrays, this second parameter is never used.

The statement immediately following the first For…Next loop — List(j, 3) = List(j, 1) * List(j, 2) — calculates the total price of each item and stores it in the rightmost cell. These updated values are then printed in the Debug window during the next For…Next loop, which outputs the complete sales record for each item.

Controlling the Print-Head

Placing a comma at the end of a 'Debug.Print' statement aligns the next item in the 14th column on the same line, following the previously printed item.

An empty 'Debug.Print' statement, placed immediately after the inner Next statement (without a trailing comma), moves the print cursor back to the first column of the next line. This ensures the output for the next sales record begins at the correct position.

If a semicolon (;) is placed at the end of a 'Debug.Print' statement, the print cursor advances to the very next character position, without leaving any space between the printed items.

Next week, we will explore user-defined variables that can hold mixed data types. With them, we can assign meaningful names to each element of a collection instead of relying on array positions as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element’s logical meaning based solely on its index.

• Filter by Character and Sort
• Proper Function of Excel in Access
• Writing VBA Code with VBA
• Positioning Pop-up Forms
• Control Setfocus on Tab-Page Click

Read More