User Defined Data Type-2

Introduction.

This is the second post on User-Defined Data Type. The Link to the first post on this topic is here.

The topic of User-Defined Data Type came up when we used Two-Dimensional Arrays of Variant data types storing different value types (String, Integer, Double) in each element of the Array.  The Variant data type has the ability to change its data types automatically, as and when a particular type of value is received in the variable or its individual array element.  Instead of using a single Variant Variable with two dimensional Array, we can use four singly dimensioned Variables of different data types, as an alternative method.  Most of the time these methods are more than sufficient for simple data processing tasks.

But, learning something new is always interesting in programming.  The User-Defined Data Type is an interesting VBA feature.  We will explore and learn how to use it in our programs. 

How to Define a User-Defined Data Type

The steps go something like this:

1. Define a new Data Type, with composite data types,  consisting of existing built-in variable types:  Integer, Long Integer, Double, String, etc.   The User-defined data type must be defined within the Type. . . End Type structure at the Module level.  The data type declaration must be at the beginning of the Standard Module.  The following sample declaration defines the data type myRecord and has two data elements: RecordID as Long Integer type and Description as String type.
   

   Public Type myRecord
   
   RecID as Long
   Description as String
   
   End Type
   

   The Scope of the Type declaration is Public by default.  Public/Private declaration is Optional.  You can declare it as Private, in that case, the availability of a Variable declaration is (like Dim AbcRec as myRecord) only within the Module, where the Type is declared.  The default scope (Public) enables the availability of the declared type within all Modules of this Project and to other Projects when this database is referenced in other Projects.  Let us start 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.

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

3. 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, addressing individual variable elements always starts with the <Type Name> followed by the <Element Name>, and both names are separated with a <dot>. The Desc, Quantity, UnitPrice, and TotalPrice elements are addressed as individual Properties of mySales.

4. Let us make the above code a little bit flexible and clean, by placing the Variable elements within the With…End With structure.  The InputBox() function will allow us to enter data directly from the keyboard, into each element of the Sales Record.

   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 on one Record and print them out on the Debug Window.  Before running the Code open Debug Window (Ctrl+G) to view the output.

   Arrays of User-Defined Type

5. Next, we will define an Array of mySales Variable to enter information for five different items.  Pass the Array of User-Defined Variable with multiple elements of data to SalesPrint() Function as Parameter. The SalesPrint() function will calculate and update the TotalPrice element, before printing the Array values in the Debug Window. Keep the Debug Window open. The sample VBA Code of the programs 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 is like any other array definition.  Dimension two more variables j and strLabel.  Variable j is used as a control variable in the For…Next loop.  strLabel is used to construct and store a label, like (1), (2), etc. to use in the InputBox() prompt.  This is an indicator to identify the current record number when we enter data into each record.

   We have used meaningful names for the Array Elements (Desc, Quantity, UnitPrice rather than using array index numbers like Sales(0,0) for Description or Sales(0,1) for Quantity, etc.). The MySales(j).TotalPrice is assigned 0.  This element's value will be calculated and assigned in the SalesPrint() function.  We will pass this Array as ByRef Parameter to the SalesPrint() Function.

   The SalesPrint() Function.

6. 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 reference in the PSales variable.  Here also we have defined two local variables, j as Integer and strLabel as String. In the next line, we are printing a header line in the Debug Window in preparation for displaying Sales record details under proper headings.

   When a comma is used to separate each item they are printed on 14 column zones on the same line.  We have used an empty item with space as the second item on the print line to print the Quantity item on the 28th column so that the Item Description can have more than 14 characters long.

7. Next, we are using a For . . . Next loop control structure is to access each record from memory, using the control variable j’s current cycle value of the loop as an array index number.  This will run from 0 to 4 (5 times).

8. The First-line within the For … Next loop creates a label to give the sequence number, in the form of (1), (2), and so on to identify the records in the order in which they are entered into memory using the InputBox() function.

9. The next statement puts the root level name of the User-Defined Type PSales within the With . . . End With Structure in order to address its Properties (.Desc, .Quantity, etc.) easily rather than repeating the upper-level Object name PSales as we did in the calling program.

10. The next executable line calculates the Total Price value and assigns it to the TotalPrice element.

11. Next line prints the current record to the Debug Window.  This process repeats within the For . . . Next loop and print all items in the Array. 

By this time I hope you understood the usefulness of this feature.  If you explore a little further with this method you can save these records from memory into an Access Table.  This type of Variable declaration is made for a particular task and the same data type may not be useful for general-purpose tasks as we do with built-in Variables.  Its data elements properties like Desc, Quantity, UnitPrice, etc. may not be useful for other purposes.

There are some interesting ways we can use the User-defined dataType and we will continue this discussion on this topic next week.

• 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,  is selected by 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 it happens, by providing your E-mail address.

Coming back to our VBA lessons, last week we have briefly touched on the topic of passing Arrays as Parameter to Function ByRef method.  We were able to work with the singly dimensioned Array in its original location within the called function, to sort the values in descending order.  For this example, we have loaded the array with values for only five elements, but the array can have many rows and columns of values.

The Re-Dimension (ReDim) Statement.

An Array can re-dimension, for more rows or fewer rows later on in the program more than once, if we cannot determine the length of the array in advance. In that case, you should not specify the number of elements in advance in the initial Dimension 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:

The important point to note here is that the ReDimension should take place in the calling program itself, if the need arises in the called function, before passing the Array to the called program.  Even though the Array is passed to the called function ByRef and we are able to work with the passed variable directly, we cannot re-dimension the array to increase/decrease the number of elements in the array, from within the called function.

Two-Dimension Array as Function Argument.

Now, that we are aware of the limitation of the array when passed to the called function, we will try to pass a two-dimensional array of Sales data to a function and print the values in the Debug Window.  The Sales data array has the following values in a record:

1. Product Name - Text
2. Quantity  - Integer
3. Unit Price – Double
4. Total Value  - Double (will be calculated in the called function)

The sales record shows that the data fields have different data types.  To pass values for each field separately to the called function, we need four different array Variables with different data types (1. The String data type for Product-Name, 2. Integer for Quantity, 3. Unit Price & 4. Total Price with Double precision number) and load each field data into separate Array Variables.

We are going to do it differently here.  We will be using only one Variable to pass all four field values to the called function.  We will define a single Variable as a two-dimensional Array of Variant data types, with four rows (each row represents a single record) and four columns (each column is a field).

The Variant Data Type.

We are not storing the above column names anywhere in the array and it is assumed that the first column is Product name, the next column to the right of the first one is Quantity, the next column is Unit Price and the last column is Total Price.  Since the Variant Data Type variable has a peculiar behavior, the data type of the cell changes automatically to match the data type assigned to it.

Before writing it as a complete function, we will look at the dimension statement 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 (or four rows or lines of data) in the Table.  There are four fields (four columns) in each record.  Each cell is numbered with two numbers (row index number, column index number), separated by a comma.  The left-side number is the row index number of the column and the number to the right side of the comma is the column index number.  Both number range is 0 to 3 (4 rows and 4 columns).  The first column (column index 0) is Product Name, 2nd Column  (column index 1) Quantity, 3rd Column (index number 2) Unit Price and the last one is (index number 3) Total Value, which will be calculated and assigned later.

The entire array of these values can be passed to a function as the ByRef parameter and we can work with the array directly from within the called function.  If you are new to two-dimensional arrays, it will be a little confusing at first to comprehend the arrangement of values and how to address each cell to work with it.   This becomes more difficult when there are calculations involving cells of the same row. 

We have a better way to deal with this problem with User-Defined Variables.  Yes, you heard me correctly, we can define our own Variable Type,  besides the built-in variables with default data types.  We will explore this topic further next week and I am sure you will be happier with this new idea, after struggling with these rows and columns set up.  Believe me, this is a very powerful feature once you are familiar with these kinds of data arrangements.  You can work with 5 rows, 500 rows, or 5000 rows with the same statements in the 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 for assigning values of different data types in them.   The next line sets up three more variables: j & k as a control variable for For … Next loops, the variable stridx for building a string to display the index numbers of cells when displayed in the InputBox() function Prompt text.

Two nested For … Next loops are set up to control the Variable index numbers of rows and column values.  The outer loop controls the row number and the inner loop with the k control variable is used for the column index number.

Next, we used the Select Case ... End Select statements to run several other statements depending on the value in the control variable j. If the value in variable k=0 (and j=0) then the Inputbox() function runs below the Case 0 tests and gets the Product Name and assigns it to the Products(0,0) cell.  When k=1 then the InputBox() gets the value of the Quantity and assigns it to the Products(0,1) cell. When k=2 gets Unit Price and in the next step assigns Products(0,3)=0. The outer loop with the control variable runs only once with zero value as the row index number.

This action repeats 3 more times for the outer For…Next loop to control the row index number and each time the inner For … Next loop runs four times to control the column numbers to get values from the User for each cell for the row number in the j control variable.

The Output Function ProdPrint().

When control comes out of the loop the ProductPrint() Function is called by passing the Products variable as a parameter to the function.

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 takes the Products Array's location address (ByRef) method. If you omit the ByVal or ByRef keyword before the Parameter variable it assumes that Variable List holds the location reference of the Products (parameter passed ByRef).

As in the earlier program, two integer variables j & k  are defined as control variables for outer and inner For … Next loops.  We need these For … Next loops to control the index numbers (rows & columns) to access each element of the array.  The starting value of the Loop is 0 but to calculate the end value we have used another function Ubound() (get Upper Boundary) value of the Array dimension.  In the first program, we have written the control value as 0 to 3.  Here also we could do that, but here we have used the Ubound() function to find the row and column numbers. This will calculate the Array size correctly if the Array size is changed through ReDim statements.

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

UBound(Array,1)

The Ubound(List, 1) gets the number of rows to value, which is 4. But the row index numbers start from 0 in memory so we have used index numbers 0 to 3 in the For … Next loop. The second value 1 in the bracket of the Ubound() function asks for the number of rows in the array.  Since the row index number starts from 0 we are subtracting 1 from the number of row values (4-1).

UBound(Array,2)

The UBound(List, 2) gets the number of column values.  The second parameter value is optional, if it is omitted, it will only get the row value.  If the variable is a singly dimensioned array, then the second value is never used.

The statement immediately after the first For … Next loop ‘List(j, 3) = List(j, 1) * List(j, 2)’ calculates the Total Price of each item and assigns it to the rightmost cell before printing the values of the Sales record item within the next For…Next loop, on the debug window.

Controlling the Print-Head

The comma at the end of the Debug.Print statement positions the next item in the 14th column on the same line, after printing the earlier item.

The empty Debug.Print statement, immediately after the inner Next statement without a comma at the end brings the print control back to the first column of the next line, positions correctly to start printing the next Sales Record.

If we place a semi-colon (;) at the end of the Debug.Print statement the print-head positions to the next character position, without leaving any space between the items printed.

Next week we will explore the User-Defined Variable with the mixed data type. We can give appropriate names for each element of the array rather than assuming names as we did in the above examples. I am sure it will be very interesting to compare the difficulty we had in memorizing each array element's logical name according to its array position.

• 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