Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for calculating Area of anything with Length and Width Values. Hope you understood the basics of an Ms-Access Custom Class Module.  If not, here is the link: Ms-Access Class Module and VBA. Please visit the page before continuing.

Class Module Objects saves a lot of Code in your Programs.  When you found something that you do repeatedly in Code think of a Class Module Object.  The repetitive code can be turned into a utility function too, but for Class Objects you must take a different approach.

For a simple task it may take more code to refine the functions of the Class Module Object, but your main module programs will be simple and all the complicated code developed and refined will remain hidden from others.

But for now, we have a simple Class Module in hand we will try how to create an Array of Objects to calculate Area of many items.

The sample VBA Code in the Standard Module creates an Array of five Objects of ClsArea Class and prints their Property Values and Method Result in the Debug Window.  Copy and Paste (or better if you type them in, to know each line better what they do) the following code into a Standard Module:

Public Sub ClassArray()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim lower As Long, upper As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
Set tmpA = Nothing 'Remove temporary object from memory.
Next


‘PRINTING SECTION
'Print the Array contents into the Debug Window
lower = LBound(CA)
upper = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = lower To upper
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
For j = lower To upper
   Set CA(j) = Nothing
Next

End Sub
 

Click somewhere in the middle of the Code and press F5 to execute the Code. Input the Values for Description, Length and Width from Keyboard for 5 different Items, one after the other.

Sample Run of the Program is given below:

Description   Length        Width         Area
Door Mat       5             8             40 
Carpet         12            23            276 
Bed Room       21            23            483 
Store Room     15            25            375 
Terrace        40            50            2000 

Let us examine the Code line by line.  In the declaration area we have declared an Object Variable tmpA of ClsArea Type. In the second line declared an empty Array CA() of ClsArea Type. The required number of elements will be Re-dimensioned later in the program. 

In the next two lines we have declared variables title of String type and j, lower and upper variables of Long Integer type.

Next, the title variable is initialized with the string ‘ClassArray’ and will be used as a Title in the InputBox() Function.  The For….Next loop is set with control variable j to run the loop five times. After the next two comment lines the Set statement instantiates (allocates memory) ClsArea Class Object in memory with the name tmpA.

Next three lines of code serves to Input values for strDesc, dblLength and dblWidth Properties of tmpA Class Object.

After the next line of comment the ClsArea Class Object (CA) is Re-dimensioned for 1 to j  times (1 to 1 times) by preserving the existing Object elements, if any (this is the first Object in the Array).  This will keep increasing, through the ReDim statement, to 1 to 2, 1 to 3, 1 to 4 and 1 to 5 by preserving the earlier object values, within the For . . . Next loop.  The Preserve key word ensures that the existing array objects are not lost.

Note: The Re-Dimension statement increase/decrease the number of object elements specified but erases the existing objects loaded into the array earlier, without the Preserve key word.

The Set statement in next line copies the tmpA Object, with it’s data into the CA(j) ClsArea Object newly created jth array element.

Next line Set tmpA = Nothing removes the temporary object from memory.

The For…Next loop repeats this action four more times to input other items into the newly instantiated temporary object tmpA, one after the other and copies the object into the CA Object Array.

Next two lines, after the comment line, finds the Object Array index range (finds the lowest and highest index numbers. 

The next Debug.Print statement prints an heading line in 14 column zones in the Debug Window.   The For . . . Next loop with the Lower and Upper bound array index number ranges runs the inner statements to access each Object from Array CA, with index number in control variable j.

The current object reference is set within With. . . End With structure, rather than repeating the Object name CA(j).strDesc, CA(j).dblLength, CA(j).dblWidth and CA(j).Area to print the Object’s Property Values and Method Area() results on the Debug Window.

Remove the comment symbol from the ‘Stop statement to create a pause in the Program on the Stop statement.  Run the code again and enter 5 item details so that you can experiment with the Array Object.   You can selectively address any of the Object Property, with the Array Index number, to edit or print any value on the Debug Window as shown below.


The statement Set CA(j) = Nothing within the For. . . Next Loop clears the array objects, one by one from Memory.

We can pass the Class Object Array as a Parameter to a program, as we did with User-Defined Data Type.

Let us create a simple Printing Subroutine, by cutting the Printing Section Code, of the main program, and placing it into the new program.

Copy the ClassArray() sub-routine Code, Paste it in the same Standard Module and change the name as ClassArray2(), as shown below.

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim lower As Long, upper As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

     tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
 Set tmpA = Nothing 'Remove temporary object from memory.
Next

‘PRINTING SECTION
'Print the Array contents into the Debug Window
lower = LBound(CA)
upper = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = lower To upper
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
For j = lower To upper
   Set CA(j) = Nothing
Next

End Sub
 

Create a new Sub-Routine as given below:
Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim lower As Long, upper As Long
Dim j As Long

End Sub

Highlight the Printing Section up to the ‘Stop statement in the ClassArray2() Program, cut the highlighted area of Code and paste it below the Dim statements in the ClassPrint() Program.  The modified version of both Codes are given below:

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "") tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0) tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0) 'Redimension Array ReDim Preserve CA(1 To j) As ClsArea Set CA(j) = tmpA 'Copy Object to Array Set tmpA = Nothing 'Remove temporary object from memory. Next Call ClassPrint(CA) ‘Pass the Object Array to print routine 'Clear array objects from memory For j = lower To upper Set CA(j) = Nothing Next End Sub



Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim lower As Long, upper As Long
Dim j As Long

'Printing Section
'Print the Array Contents into the Debug Window.
lower = LBound(clsPrint)
upper = UBound(clsPrint)

Debug.Print "Description", "Length", "Width", "Area"
For j = lower To upper
  With clsPrint(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

End Sub

Check for code changes in both programs and do corrections, wherever applicable.  Click in the middle of ClassArray2() code and press F5 to run the program. 

The Call statement in the first program passes the CA Class Object Array as ByRef Parameter to the ClassPrint() Subroutine.  The program prints the Object Properties and calls the function Area() to calculate and return the value to print.

Next week we will learn how to use our ClsArea Class module as a Base Class to create a new VBA Class Object to calculate Volume of something.

Share:

MS-Access Class Module and VBA

Last few weeks we have learned how to use User Defined Type (UDT)  by creating a complex data structure and we know UDT’s strength or weakness, more or less by now.  If you have not gone through those Articles then you may visit them. Use the following links:

In Microsoft Access there are two Types of VBA Modules.

  1. Standard Modules
  2. Class Modules

We have worked with Class Modules in Forms and Reports of Microsoft Access. Form/Report Class Module programs are mostly Event driven (Button-Clicks, Before-Update, Form/Report Current Event and so on) small routines.

More serious data processing Programs will be written in Standard Modules. Several programs, not necessarily related, can be written in one Standard Module, to do different tasks.

Class Modules are different. They are used for building Custom Objects and one Class Module is used for only one object.

Let us create a simple Class Module from scratch and learn the basics.

  1. Start Microsoft Access and Open a database or Create a new Database.  If it is a New Database then save it to the existing Trusted Location (Folder) or add the new Location to the trusted locations list.  Click Office Button –>Access Options—> Trust Center –>Trust Center Settings.  Add the database folder into the list and click OK.
  2. Open the VBA Editing Window (Alt+F11).
  3. Click on Insert Menu and select Class Module from the list. A new Class Module is inserted.

  4. In the left panel there is a Name control with the name Class1.
  5. Change the name Class1 to ClsArea. This is the name of our Custom Object.

Note: The Class Name: ClsArea become the Object Name. That means, wherever we use this object it will be like normal variable declaration: Dim xyz As ClsArea).  We did write similar statement for User-Defined Data Type declarations.

Next, we will add three Variables (for Description, Length and Width) at the top of the module, below the Option Compare Database and  Option Explicit lines.  Type in the following lines into the Class Module.

Option Compare Database
Option Explicit

Public p_Desc as String
Public p_Length as Double
Public p_Width as Double

These variables are identified as Properties of the VBA Custom Class Object.  Save the Class Module.

Note: Our Class Module Object and lines of code will not be this simple.  It will undergo changes with several lines of Code. Better be prepared to follow them step by step, without loosing track of each stage of changes. This is a simple area-calculation Class (area = length * width), that simple. It will gradually undergo changes, so that you will know why those changes become necessary.

We will write a small program in a Standard Module to test our new Class Module. Insert a Standard Module from Insert Menu.  You may  Type or Copy and Paste the following Code into the Standard Module, overwriting the existing line in the Module:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.

Set oArea = Nothing

End Function

The Dim statement declares a Variable as we do for a normal variable, like Dim Desc as String.  But, this is not an ordinary variable we are setting a reference to our Class Module Object ClsArea.  Since, it is an object a simple Dimension statement alone is not enough because it will not allocate any memory space to store values into our locally defined Object oArea Properties.

The Set statement in the next line with the New key word required, to create an instance of an Object out of ClsArea in memory, with the Object Instance Name oArea.  We can open several instances of the same Class Object in memory in this way, if needed,  (We will learn about them in the coming weeks) so that we can store values into it’s Properties (p_Desc, p_Length, p_Width).  The p_ prefix to the variables is an indicator that the Scope of variables are Private, i.e. the Variables are not visible outside the Class Module. Any valid variable name will do.

Note:We have not yet changed it to Private. We are on the way towards that change

Immediately after the key word Set the local Object name (you can pick a suitable name you prefer but it should conform to the normal variable name rules) followed by an equal sign and the key word New and the Class Module Name (ClsArea) to create an instance of the clsArea Object in memory with all it's Properties (Variables). 

There is a shortcut for this two line code. Actions of both these lines of code can be achieved with one statement as shown below :

Dim oArea As ClsArea


Set oArea = New ClsArea

Dim oArea As New ClsArea

When you type the next line oArea followed by a dot (.) separator the following display will appear to show the list of available Custom Object Properties to select from.

If it doesn't appear then go to the Options dialog from the Tools menu box and put a check mark in Auto List Members in the Editor Tab.

Before exiting the Function the last statement should be Set oArea = Nothing. This statement explicitly releases the memory occupied by the instance of the Custom Object, so that more memory is available for other programs.  This is a responsible cleaning up operation by our program.

Whatever we do with the instantiated Custom Object should be coded between the first and last Set statements.

The completed Class Testing Program Code is given below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.p_Desc = "Carpet"
oArea.p_Length = 25
oArea.p_Width = 15

Debug.Print "Description", "Length", "Width"

Debug.Print oArea.p_Desc, oArea.p_Length, oArea.p_Width

Set oArea = Nothing

End Function

Click somewhere in the middle of the Code and press F5 to run the program. Run of the program  is given below for reference.

Description   Length        Width
Carpet         25            15 

Our simple Class Module Object have few drawbacks and we are going to rectify them.

The first one is that we have declared all Variables (or Properties) with Public Scope.  Because of that they are visible to other VBA programs and may get it’s value changed directly.  The second issue is that it will accept any invalid values, like negative or zero values, which is not suitable for our Class Object.  We have to incorporate some validation checks before accepting the values into the variables.

The first problem we can solve easily by changing the variable declarations from Public to Private.  When we do that we should have some indirect method to store and retrieve values from the Private Variables. That is the purpose of the Get and Let Property Procedures.  Let us make these changes in the Class Module.

Open the Class Module ClsArea. Change the word Public to Private for all three variables.

Select Procedure from Insert Menu, type strDesc in the Name text control, select Property in the Type Option Group and Public in the Scope option group.  Click OK to insert the Property Procedures for the Private p_Desc Variable (Property).

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'return the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue ‘store the value into p_Desc
End Property

Both the Get Procedure and Let Procedure are declared as Public. Both Procedure names are same strDesc.  By default the returned data type is Variant in the Get Procedure and the Parameter data type is also inserted as Variant in the Let Procedure.  These we can change to specific type as needed, which we did and changed to String type.  The first three letters str in strDesc gives the User a hint that the Property expect a String data type value. Change the Let Property Procedure Parameter Variable Name vNewValue to strNewValue

When we insert the Property Procedure  they are always inserted with Get and Let Procedure pairs for a Variable.

Now, take a look closely the expression we have written within the Get Procedure.  Left side of the = Sign the name of the Get Procedure strDesc acts as a variable to return the value copied from the Private Variable p_Desc  to the calling program.

The Let Procedure strDesc accepts String value in parameter Variable strNewValue.  The input value is transferred  into our private variable p_Desc.

The point to note here is that there is no direct access to our private variable p_Desc to the outside world.  Transportation of values From/To the Variable (Property) p_Desc is always routed through the Get/Let Property Procedures only and subject to Validation checks(not yet implemented),  We will introduce validation checks on values input (Let Procedure) into the Property later.

The Get/Let Procedures are executed automatically depending on what we do with the Object property in an expression in VBA Programs. 

The Get Procedure is executed when we use the Property Name in an expression in the following manner:

‘ Reads the value from p_Desc to Print

Debug.Print oArea.strDesc

OR

‘ Reads the value from p_Desc and assigns it to the variable X

X = oArea.strDesc

The Let Property Procedure is run when we try to assign a value into the Property Name. Check the example expression in our Test Program below:

oArea.strDesc = “Carpet”

In earlier BASIC Language books you can see the usage of the key word LET.

LET X = 25 ‘ LET is optional

Since, it was optional the statement works without it and stopped using it at all.

Here, if you are only reading some value from a Variable and not storing anything into it directly then you can omit the Let Procedure and use only the Get Procedure.

This rule applies to Let procedure also.  You may use only the Let Procedure, if you are assigning some value into a Private Variable but not reading anything back from the same variable then omit Get Procedure.

The Get and Let Procedures will run one after the other, if our expression is something like the following:

oArea.strDesc = oArea.strDesc & “ – King Size.”

In the above expression we will Get the existing value from the Private Variable p_Desc and modify the description and store it back into the same variable.  In short in an expression if you use the Property name to the right of the equal sign (=) the Get Procedure is called and Let Procedure is run when the object property procedure name appears to the left of the equal (=) sign.

Insert two set of Property Procedures for the variables p_Length and p_Width. When you give the Procedure names in the Name control give the name dblLength and dblWidth to give a hint to the User that these Properties expects Double Precision numbers as Input.

The completed code so far with dblLength and dblWidth Property Procedures is given below for reference and to update your code.

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
  dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
  p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
  dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
  p_Width = dblNewValue
End Property

If you are through completing the above code then let us make changes to our test program, to reflect the changes we made here.  The modified sample code is given below.

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

‘Property Let procedures called here

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width"

‘Property Get Procedures called here to print
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth

Set oArea = Nothing

End Function

When you enter a dot (.) immediately after the object name oArea (oArea.) the list of Property Procedure names are displayed by the VBA intellisense and you may select the required one from the list without typing it manually.

The purpose of this Class Object is to calculate Area of something, like Area of Room, Carpet, Floor Tile or whatever material, which have values of Length and Width.  That means we need a Public Function to calculate Area of whatever item’s Length, Width and Description Values entered into the Class Object.

Here is the Code for the Public Function:

Public Function Area() As Double
   Area = Me.dblLength * Me.dblWidth
End Function

You may insert this Function from the Insert Menu by entering Area in the Name Control, selecting Function from the Type option group and Public as Scope into the ClsArea Class Module.  Complete the Function by entering the line in the middle.

We can directly address p_Length and p_Width variables (because the Function Area() is part of the Class Module) in the expression for calculating the Area.  But, we are taking the proper route and calls Get Procedures dblLength and dblWidth for calculation.  You might have noticed the reference Me.  used to qualify the dblLength, dblWidth Get Procedures, like we used to write in Form/Report Class Modules, to refer to the current Object in memory and it’s Properties.  As I stated earlier our Custom Class Object can have several Object instances opened in memory at the same time and Me key word refers to the current instance that the Function Area() belongs to.

Modify our Test Function ClassTest1() to incorporate the Area() function output as below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width", "Area"
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth, oArea.Area

Set oArea = Nothing

End Function

The change is only in the Debug.Print statements.  Run the code and check the Debug Window for result.

There are two Event Procedures required in the Custom Class Modules: Class_Initialize() and Class_Terminate()

The Class_Initialize() program is executed automatically when we instantiate an Object with the New Key Word.  This program can be used to set default values into variables or Instantiate other  Objects in memory.  One Class Object may use other Classes as child object(s) and needs to be instantiated.  This aspect we will explore further and learn how to do it later.

The Class_Terminate() program runs when we try to clear the object from memory when the Nothing key word is run in the statement Set oArea = Nothing.  When the program that uses the Class Object ends, the Instance of the Object in memory is removed by default.  But it is a good programming practice that we use Set oArea = Nothing  statement as last executable statement in our programs to clear the object from memory.

We will add the above programs in our Class Module.  Add the following Code at the end of your Class Module:

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0

   'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

If you would like to test these two sub-routines then remove the Comment symbol and make the MsgBox active.  Run your test program one more time.  You will find the Initialize message appears in the beginning (Click OK to continue)  and Terminate message appears at the end of the Test Program.

I know what you are thinking by now, like “so much code for multiplying two variables together”.  It is true in that perspective, but it is very likely that we have written code for similar problem solving issues repeatedly every time, duplicating code for validation checks and for other logical error safeguards.

Here, we are  not writing an ordinary Program but developing a Custom Object that can be used many times, or can be part of other Objects, wherever we need it without worrying about how it works, in the user point of view.  Microsoft Access have many built-in Objects/Functions that we use all the time without worrying about how it works, by setting their Properties or Parameters and get the work done.

We have one more issue to take care of, the validation checks on values input into the dblNewValue  Parameter in the Let Property Procedures of dblLength() and dblWidth(), to ensure that valid values are assigned to Object Property p_Length and p_Width

Negative or Zero Values entered are considered invalid and we have to take precautions to see that the correct value is entered by the User.  The modified Let Property Procedure Code segments are given below.  Make changes in your code accordingly.

Public Property Let dblLength(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
   Loop

   p_Length = dblNewValue
End Property


Public Property Let dblWidth(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
   Loop

   p_Width = dblNewValue
End Property

The Do While. . . Loop runs repeatedly till a valid value (greater than 0) is entered into dblNewValue by the User

We need one more validation check in the Area() Function.  If the user calls the Area() function without entering valid values for Length and Width first, then the User must be informed about it.  We will check whether p_Length and p_Width variables have valid values before running the expression for area calculation.  Here is the Code:

Public Function Area() As Double
  If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
        Area = Me.dblLength * Me.dblWidth
  Else
        Area = 0
        MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
  End If
End Function

The fully completed Code of our Class Module ClsArea is given below:

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
    strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
    p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
   dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
    Loop

    p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
   dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
    Loop

    p_Width = dblNewValue
End Property

Public Function Area() As Double

   If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
       Area = Me.dblLength * Me.dblWidth
   Else
       Area = 0
       MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
   End If

End Function

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0
 'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

You may test our Custom Class Object by entering negative or 0 values as input to dblLength, dblWidth Properties. 

In the Test Program Comment out the lines (oArea.dblLength=25 and oArea.dblWidth=15) to test the Area() function. It should display the Error message we have written within the function.

Our Area calculation Class Module is now considered complete and we have tested and found it is working correctly. You may test it further for any logical errors that I have overlooked. If you come across anything that I didn't anticipate, please share it with me.

We have tested the Class Object for only one item.  We need to calculate the area of several items (say area of 5 bed-rooms or 10 different sized Carpets and so on.  We are told that once an object is developed we can instantiate it several times in memory assigning different set of values into each instance of the Object and can work with them.

Besides that, this Object can be used as part of other Objects we develop with lesser code, because part of our new Class Object is already developed in ClsArea Class Module.

Next week we will learn how to create an Array of Custom Objects to calculate area of several items.

Share:

User-Defined Data Type-3

Last week we have learned how to define a User-Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go through the earlier Post: User-Defined Data Type-2, before proceeding further. 

User defined type declaration is done within the [Private/Public ]Type…End Type structure.  Immediately after the word Type we give a name to the data type.  Actual variables which holds the data values are defined as individual elements, mostly with built-in variable types: String, Integer, Long Integer, Double or Variant.

Besides built-in variables we can use other User-Defined Type, consisting of several elements of it’s own, as child element within a User-Defined Type.  That is what we are going to try out here.

First, we will declare some User Defined Types separately, for different category of information, like Home Address, Qualification, Experience,  for Employee Record.

We will build an employee record with the above logical group of information defined separately with it’s own data elements and place them along with the elements of Type Employee.

The layout of the group of information of Employees, defined separately, before they are organized under the User Defined Type Employee is shown below.

1.Qualification
Desc1
Desc2
2.Experience
Desc1
Desc2
3.Address and Date of Birth
Address1
Address2
City
State
PIN
BirthDate
4.Employee Details
Employee Name
Designation
Join Date
Monthly Performance Score(1 to 12)
Salary

First, let us declare the Data Types:

Option Compare Database

Public Type Qualification
    Q_Desc1 As String
    Q_Desc2 As String
End Type

Public Type Experience
     X_Desc1 As String
     X_Desc2 As String
End Type

Public Type BioData
    Address1 As String
    Address2 As String
    City As String
    State As String
    PIN As String
    BirthDate As Date
End Type

Public Type Employee
        E_Name As String * 45
        E_Desig As String * 25
        E_JoinDate As Date
        E_PerfScore(1 To 12) As Double
        E_Salary As Double
End Type

The Qualification and Experience Types are defined as child elements inside the BioData Type.  After the change the BioData Type looks like the Code given below.

Public Type BioData Address1 As String Address2 As String City As String State As String PIN As String BirthDate As Date Q_Desc As Qualification X_Exp As Experience End Type

The Qualification Data Type have two elements of String Data Type.  The Experience Type also have two elements of String data type.

The BioData type have Address details and Date of Birth as elements. Besides that Qualification and Experience Data Types are inserted into the BioData Type as it’s child elements.

Now, we will define the Bio-Data Data Type (along with Qualification and Experience) as child element of Employee Data Type. 

The Employee Data Type with BioData Type as child element.

Public Type Employee E_Name As String * 45 E_Desig As String * 25 E_JoinDate As Date E_PerfScore(1 To 12) As Double E_Salary As Double B_BioData As BioData

End Type

Got the idea how all these fits together as Employee Record.

The Employee type have four elements. The E_Name element is String type and it’s length is limited to 45 characters long.  The next element is to store the Designation of the employee and can store up to 25 characters.  The String length specification is purely arbitrary, you may use it as it is or simply say E_Name As String.  Join Date is Date type. 

Next item is an array with 12 elements to store the employee’s monthly performance evaluation score (on a scale of 10) recorded by the management.

We have declared Qualification and Experience data types first, before inserting them as elements of BioData Type.

BioData Data Type is declared above Employee type before inserting it into Employee data type.

Ensure that you are not placing a Type within itself.

Now, that we are all set to try out this complex data structure and the first question comes in one’s mind is that how to address each element to assign values into them.

We will write a small program to try out Employee Data Type and assign values into each element of the nested complex data structure.  It is not that complicated, as it sounds.  If you find it difficult to follow then try out simpler examples defined on your own level of understanding.

The program code is given below and look closely at each element as how it is addressed  to assign values into it.

Public Function EmplTypeTest() Dim Emp As Employee Emp.E_Name = "John" Emp.E_Desig = "Manager" Emp.E_JoinDate = #01/01/2018# Emp.E_PerfScore(Month(Date) - 1) = 4.5 Emp.E_Salary = 40000 'BioData Emp.B_BioData.Address1 = "115/8" Emp.B_BioData.Address2 = "Olencrest," Emp.B_BioData.City = "Columbus" Emp.B_BioData.State = "Ohio" Emp.B_BioData.PIN = "43536" Emp.B_BioData.BirthDate = #9/29/1979# 'Qualifications Emp.B_BioData.Q_Desc.Q_Desc1 = "Degree in Computer Science" Emp.B_BioData.Q_Desc.Q_Desc2 = "PG Degree in Computer Science" 'Experience Emp.B_BioData.X_Exp.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." Emp.B_BioData.X_Exp.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."

Call ListEmp(Emp) End Function

As you can see in the above code we have addressed each element with fully qualified names showing it's hierarchical position in the Employee Data Structure. The same code is given below using full object references with With XXXX....End With statements, where XXXX part represents the object hierarchical names.

Public Function EmplTypeTest0()
Dim Emp As Employee

With Emp
  .E_Name = "John"
  .E_Desig = "Manager"
  .E_JoinDate = #01/01/2018#
  .E_PerfScore(Month(Date) - 1) = 4.5
  .E_Salary = 40000
End With

        'BioData
With Emp.B_BioData
        .Address1 = "115/8"
        .Address2 = "Olencrest,"
        .City = "Columbus"
        .State = "Ohio"
        .PIN = "43536"
        .BirthDate = #9/29/1979#
End With

       'Qualifications
With Emp.B_BioData.Q_Desc
            .Q_Desc1 = "Degree in Computer Science"
            .Q_Desc2 = "PG Degree in Computer Science"
End With

        'Experience
With Emp.B_BioData.X_Exp
            .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
End With

    Call ListEmp(Emp)

End Function

Check the With...statement how the Object references are given in proper order of it’s placement to reach it's element variable. The above Code is again modified with With XXXX...End With statements in a nested form only using the Object name nearest to the data variable.

Public Function EmplTypeTestA() Dim Emp As Employee With Emp .E_Name = "John" .E_Desig = "Manager" .E_JoinDate = #01/01/2018# .E_PerfScore(Month(Date) - 1) = 4.5 .E_Salary = 40000 'B_BioData With Emp.B_BioData .Address1 = "115/8" .Address2 = "Olencrest," .City = "Columbus" .State = "Ohio" .PIN = "43536" .BirthDate = #9/29/1979# 'Qualifications With .Q_Desc .Q_Desc1 = "Degree in Computer Science" .Q_Desc2 = "PG Degree in Computer Science" End With 'Experience With .X_Exp .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise." End With

End With ‘ Emp.B_BioData End With ‘ Emp Call ListEmp(Emp) End Function

You can use any of the above three programs or all of them one by one to try out the following Printing program to list the data on to the Debug Window.

Public Function ListEmp(ByRef EmpList As Employee)
With EmpList
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score July: ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

End Function

Sample Output displayed on the Debug Window is given below:

Name:                       John                                         
Designation:                Manager                  
Join Date:                  01-01-2018 
Performance Score August:    4.5 
Salary:                      40000
Address1:                   115/8
Address2:                   Olencrest,
City:                       Columbus
State:                      Ohio
PIN:                        43536
Qualification1:             Degree in Computer Science
Qualification2:             PG Degree in Computer Science
Experience1:                From Jan-2010 onwards Working as Project Manager, with XYZ Company.
Experience2:                From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise.

If you would like to try out an array example, then copy paste the following two programs into a Standard Module and run the first code. 

Public Function EmplTypeTestB()
Dim Emp(1 To 3) As Employee
Dim j As Integer, strlabel As String

For j = 1 To 3

With Emp(j)
strlabel = "( " & j & " )"
    .E_Name = InputBox(strlabel & "Name:")
    .E_Desig = InputBox(strlabel & "Designation:")
    .E_JoinDate = InputBox(strlabel & "Join Date:")
    .E_PerfScore(Month(Date) - 1) = InputBox(strlabel & "Performance Score:")
    .E_Salary = InputBox(strlabel & "Salary:")
    
   'B_BioData
    With Emp(j).B_BioData
        .Address1 = InputBox(strlabel & "Address1:")
        .Address2 = InputBox(strlabel & "Address2:")
        .City = InputBox(strlabel & "City:")
        .State = InputBox(strlabel & "State:")
        .PIN = InputBox(strlabel & "PIN:")
        .BirthDate = InputBox(strlabel & "Birth Date:")
       
       'Qualifications
        With .Q_Desc
            .Q_Desc1 = InputBox(strlabel & "Qualification-1:")
            .Q_Desc2 = InputBox(strlabel & "Qualification-2:")
        End With
    
        'Experience
        With .X_Exp
            .X_Desc1 = InputBox(strlabel & "Experience-1:")
            .X_Desc2 = InputBox(strlabel & "Experience-2:")
        End With
    End With
End With
Next

    
    Call ListEmp2(Emp)

End Function

The Inputbox() Function will allow you to type details of three employees directly from keyboard, based on the prompt displayed asking for specific values.  In the last statement Call ListEmp2(Emp) will run the following code by passing the employee records array  and prints the output into the Debug Window. Keep the Debug Window Open (Ctrl+G).

Public Function ListEmp2(ByRef EmpList() As Employee)
Dim j As Integer, strlabel As String
Dim lower As Integer
Dim upper As Integer

lower = LBound(EmpList)
upper = UBound(EmpList)

For j = lower To upper
With EmpList(j)
Debug.Print
    Debug.Print "=== Employee: " & .E_Name & "  Listing ==="
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score " & MonthName(Month(Date) - 1) & ": ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

Next
End Function

I hope you will try out the User Defined Type in your projects and explore it’s strength and weaknesses further.

The main problem with the User Defined Type is that it doesn’t have any feature to validate the data passed to it before accepting in it’s elements.  For example, if any future date is entered into the Date of Birth element there is no built-in code to validate and inform the user that the date entered is not valid to accept in the field.  Like-wise if a negative value is entered into the Salary field it simply accepts it.  Wherever the validation check is required we have to write separate code to do that, whenever we use the User Defined Type (UDT) all the time.

A better option  is to use Class Modules. We can define individual elements in Class Module, run validation checks on each item, wherever necessary,  before accepting the data into the element.  Write Functions or Subroutines to operate on the data for common tasks and call the Functions from user programs.  All these remains as part of the package and don’t have to write separate code for it.

We will learn how to use Class Modules to define structured data and use it in programs.


Share:

User Defined Data Type-2

This is the second Post on User-Defined Data Type. The Link to the first Post on this topic you can find it HERE.

The topic of User Defined Data Type came up when we have used Two Dimensional Arrays of Variant data type storing different value types (String, Integer, Double) in each element of the Array.  The Variant data type has the ability to change it's data types automatically, as and when a particular type of value is received in the variable or it's 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. 

The steps goes 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 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 data Type 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 element is 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 Property of mySales.

  4. Let us make the above code 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 keyboard, into each element of 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 of one Record and print them out on the Debug Window.  Before running the Code open Debug Window (Ctrl+G) to view the output.

  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 the called 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 are 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.  We have dimensioned two more variables j and strLabel.  Variable j is used as 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 with zero.  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.

  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 variableHere 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 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 a space as the second item on the print line to print the Quantity item on the 28th column so that Item Description can have more than 14 characters long.

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

  8. First line within the For…Next loop creates a label to give 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. Next statement puts the root level name of the User-Defined Type PSales within a With . . . End With structure in order to address it’s Properties (.Desc, .Quantity etc.) easily rather than repeating the upper-level Object name PSales as we did in the calling program.

  10. Next executable line calculates the Total Price value and assigns it to ,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 little further with this method you can save these records from memory into an Access Table.  This type of Variable declarations are 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.  It’s 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 data Type and we will continue this discussion on this topic next week.

Share:

Passing Two Dimensional Array to Function

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

Microsoft Access Blogs

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 topic 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 the topic of passing Arrays as Parameter to Function ByRef method.  We were able to work with the singly dimensioned Array in it's 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 of values.

An Array can be re-dimensioned, for more rows or less 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:

'These Arrays cannot be redimensioned
'Array has predefined number of elements
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'This Array can be redimensioned
'Number of elements required are not known in advance. 
.
Dim Products() as String
.
'ReDimension 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
.
'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

The important point to note here is that the ReDimensioning should take place in the calling program itself, if 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.

Now, 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 on the Debug Window.  The Sales data array have the following values in a record:

  1. Product Name - Text
  2. Quantity  - Intiger
  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 each field values separately to the called function we need four different array Variables with different data types (1. String 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 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 type, with four rows (each row represents a single record) and four columns (each column is a field).

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

Before writing it as a complete function we will  look at the dimension statement and how the sales values are assigned into 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.  Left-side number is the row index number of the column and the number to the right side of comma is the column index number.  Both number range is 0 to 3 (4 rows and 4 columns).  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 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 little confusing at first to comprehend the arrangement of values and as how to address each cell to work with it.   This become 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 in 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 kind of data arrangements.  You can work with 5 rows, 500 rows or 5000 rows with the same statements in the function.

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

We have defined the Products variable as Variant data type with 4 rows, 4 columns for assigning values of different data types in them.   Next line is setting up three more variables: j & k as control variable for For…Next loops, 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 k control variable  is used for column index number.

Next we have used the Select Case…..End Select statements runs several other statements depending on value in the control variable j. If value in variable k=0 (and j=0) then the Inputbox() function runs below the Case 0 test and gets the Product Name and assigns it to Products(0,0) cell.  When k=1 then the InputBox() gets the value of Quantity and assigns it into Products(0,1) cell. When k=2 gets Unit Price and in the next step assigns Products(0,3)=0. The outer loop with control variable run only once with zero value as 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 j control variable.

When control comes out of the loop the ProductPrint() Function is called by passing the Products variable as 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 ByVal or ByRef key word before the Parameter variable it assumes that Variable List holds 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.

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

UBound(List, 2) gets the number of columns value.  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 at the right most cell before printing the values of the Sales record item within the next For…Next loop, on the debug window.

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

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 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 comparing the difficulty we had in memorising each array elements logical names according to their array position.


Share:

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Variable from the calling function or the Location Address of a Variable to the Called Function.  If you have not visited the earlier page the link is given below:

Now, we will learn:

  1. how to pass the location address of a single element of an Array  to the called Function and change it’s value
  2. how to pass the Location Address of an array and sort the values in the array in Descending Order.

First, let us write two small programs for the first example. The first Program Code is given below.

Public Function ArrayArg_Test1()
Dim NumArray(1 To 5) As Integer, j As Integer

'load array with numbers 1 to 5
For j = 1 To 5
   NumArray(j) = j
Next

'pass a single element value
Call ArrayArg_Test2(NumArray(4))

'Print the Array values after change
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

In the first line of code we have defined an Array Variable (NumArray()) with five elements to store Integer type values. Variable j is a control variable defined for For...Next Loop.

Inside the For....Next loop the Array is loaded with values 1 to 5, i.e. NumArray(1) = 1, Numarray(2)=2 and so on up to 5 elements.

Next, we call the function ArrayArg_Test2() function with the NumArray(4) element passed as parameter.  The number 4 within brackets is the index number of the element not the value itself.  But, we have the value 4 in that element too.  The called function ArrayArg_Test2() receives the passed value/location depends on the parameter definition there. If we use ByRef  or omit the ByRef specification before the Variable Name and Data Type then the called function takes the passed variable's location address to work with the value stored in the original Variable.  We will go with the second method and will not use the ByRef specification in the next function’s  parameter definition.

Within the next For...Next loop the array contents are printed in the Debug Window.  If any change done by the function ArrayArg_Test2() will show up in the printed list.  Since, we already knew that the array elements 1 to 5 contains the values 1,2,3,4,5 we have not printed those values before calling the second function.

The ArrayArg_Test2() Function VBA Code is given below:

Public Function ArrayArg_Test2(NA As Integer) 'The word ByRef is omited
'multiply NumArray(4) value * 5 = 20
    NA = NA * 5
End Function

The Variable NA is assigned with the NumArray’s 4th element location address.  The ArrayArg_Test2() picks the value from NumArray(4) itself, multiply it by 5 and store the result back into the same location.

This was working with a single element of an Array.  What about passing the full Array’s location address and work with hundreds of elements of this array in the called Function. 

We will use the same array we have used in the above example and sort the values in Descending order by passing the full array to the Sorting function

The modified version of the First Function Code is given below. 

Public Function ArrayArg_Test3()
Dim NumArray(1 To 5) As Integer, j As Integer

For j = 1 To 5
   NumArray(j) = j
Next

'Pass the array to the called function
Call ArrayArg_Test4(NumArray())

'Print the Sorted Array
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

Check the function call statement. NumArray() is passed without the element number, as we did in the earlier example. The opening and closing brackets are required along with the array variable name to indicate that the parameter passed is an array not a single variable.

When control is returned from the ArrayArg_Test4() function the sorted list of numbers are printed in the debug window. The value printed at the left side is the array element number and the right side value is array value itself,  sorted in Decending order.

The Data Sorting Program is given below:

Public Function ArrayArg_Test4(apple() As Integer)
Dim j As Integer, k As Integer, Temp As Integer

'Bubble Sort the Array in Descending order
' 1st loop runs maximum array elements minus 1 times

For j = 1 To 5 - 1 ' in place of 5-1 you may use Ubound(apple)-1

   ' inner loop starts with outer loop's current value + 1
   ' and runs to the maximum number of array elements times

      For k = j + 1 To 5 ' replace 5 with Ubound(apple)

     If apple(k) > apple(j) Then 'if second value is greater
     
        Temp = apple(j) 'copy 1st value to Temp Variable
        
        apple(j) = apple(k) 'move greater value up
        
        apple(k) = Temp ' move the smaller value down
        
     End If
    Next k ' compare next two elements
Next j
End Function

If you want to sort the values in Ascending Order then the only change in this program required is to change Greater Than (>) symbol to Less Than (<) symbol. The number we have loaded into the array was already in Ascending Order.

If you remove the 5-1 constant from the first For...Next loop and replace with Ubound(apple)-1 and replace 5 with Ubound(apple) in the second loop you can use the program to sort the array with any number of elements without change in the Program.

Notice that we have omitted the ByRef specification in the called ArrayArg_Test4()Function Parameter definition. VBA, by default, takes it as ByRef Parameter Variable.

Share:

Function Parameter ByVal and ByRef Usage

Before taking up the above subject let us look at some fundamentals on variables for the benefit of novices.

When we define a variable in VBA or in any other programming language the computer reserves some memory location and allocates some memory cells (the number of cells allocated depends on the declared variable type, like Integer, Double, String etc.) to store values passed to it. 

In layman's analogy we can imagine a variable as a box with the name 'Apple' or whatever name we give to the box and uses that name to pick the value stored in it.  Assume that you have put 5 in the Apple box.  We can give these apples to someone in two ways.

  1. Make copies of the apples (the number) from the box ourselves and put it into another box and pass it.  The target box's name will be different.  The recipient of the new box can work with his copy of the apples, like add more apples into his box or remove some of them etc.  There will not be any change in the first box contents. 
  2. We can tell the other person (or Function), which area of the room (location) you have kept the original box of  apples, get the box contents from there and work with it.  In this room (or within the function body) there may be other boxes (Variables) with different names and contents.

In the first method explained above, unlike the physical box, you can make copies of the original value and store them into different Variables.  The original value will not change in the first Variable.  The Function that gets the new Variable with the copy have no access to the first Variable.  He can do whatever he wants to do with the copy he has.

In the second case you can tell the location of the Apple_Box1 to the other Function so that it can go there and find it's contents and do whatever the Function wants to do with them (add, subtract, multiply etc. or use it as part of other calculations) or whatever operations you would like to do with them. 

To prove the first point above let us take a closer look at the next two example functions Test_1A() (the calling function) and Test_1B() (the called function with the copy of the value).

Method-1 Examples:

Public Function Test_1A()

'Define two variables(boxes)to hold values of
'Long Integer type
Dim Apple_Box1 As Long, ApplesReceived As Long

'Put an initial value of 10

'into the variable

Apple_Box1 = 10

'sending a copy of Apple_Box1 Value to Apple_Box2

'Whatever value changes happened in Apple_Box2 in Test_1B()
'Received back into the third box:ApplesReceived

ApplesReceived = Test_1B(Apple_Box1)

'call funcction Test_1B() with the value
'Display the result in MsgBox.
MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

In the above program we are defining two Variables Apple_Box1 and ApplesReceived both to store Long Integer type values. Here, we are not going to bother about what is Long Integer or Short Integer and their range of values that we can store in them etc.

Next line Apple_Box1 = 10, value 10 is stored in Apple_Box1. 

The next three lines are remarks explaining what we are doing in the next line. 

We are calling Test_1B() Function and passing the value of Apple_Box1 to the function to do something with the value received in a Variable (ByVal Apple_Box2).  The ByVal before Apple_Box2 given in Test_1B() function states that take a copy of the value from Apple_Box1.  The ‘As Long’ appearing after the closing parenthesis indicates that the second function does some calculations with the received value and returns the result to the first function.  The new value received is stored in ApplesReceived Variable. 

Next line MsgBox() function displays the Apple_Box1 contents and the result value (after modification done to the original copy of value) received from Test_1B() function.

Public Function Test_1B(ByVal Apple_Box2 As Long) As Long

'Take Note of the Key-Word 'ByVal' (says take a Copy of the Passed Value)
'Return the value back into the first function Test_1A
'After adding 15 to the original value of 10
'copied into Apple_Box2

Test_1B = Apple_Box2 + 15

End Function

There is only one executable statement in the above function.  Immediately after the function definition four lines of remarks indicating what happens in the function.

We will take a closer look at the next line of statement. This statement have two parts – first part appears left of the = sign and the second part is at the right side of the equal sign. 

In this expression, the left side of the equal sign will be a Variable or a Function name.  By now you will be asking yourself as why a function name there. That will be explained in a moment.

The computer always evaluates the expression given at the right side of the equal sign first and arrives at a single value and moves that result into the Variable given at the left side of the equal sign.  Any existing value in the variable will be lost. You can write that expression in two lines to arrive at the same result as below:


Apple_Box2 = Apple_Box2 + 15

Test_1B = Apple_Box2

In the first expression you can see that we have used the Apple_Box2 variable to the left-side and right side of the = sign. As I said earlier the expression at the right side of the equal sign is evaluated first. So it takes the existing value of 10 from Apple_Box2 for calculations and Adds 15 to it, arriving at the single result value of 25 and moves that value into Variable Apple_Box2, replacing earlier value 10.


If the Function name (the function name given at the first line of Code) appears at the left side of equal sign then the meaning of the statement is that the final result of the expression must be returned to the Calling Function.  Here, the Function Name acts as a Variable with the Data Type (As Long) specified immediately after the closing brackets on the first line.

This function name appears on the calling statement in the calling function to the right side of the = sign and a Variable Name on the left side of the = sign that saves the received result value (ApplesReceived = Test_1B(Apple_Box1).

Method-2 Examples:

In this method we have defined only one variable Apple_Box1 as Long Integer Type.  In the next line the Variable is loaded with an initial value of 10.  Next two lines are remarks explaining what is happening in the next line that calls the second function Test_1D().

Compare this statement with the statement that calls Test_1B.  Unlike the statement that calls Test_1B() the Function Name Test_1D and the function parameter Variable Apple_Box1 is only appears here.  The opening and closing brackets are omitted from the function name. The parameter variable is the second item.  Test_1D() function is not returning any value back into the calling function Test_1C.  Therefore we don't need to write this line of code in the form of an expression as we did in Test_1A Function.  But, you cannot write the statement as:

Test_1D(Apple_Box1).,

Once we use the parenthesis (normally used with the function name) around the parameter variable then VBA assumes that some value is returned from the called function and you are forced to write it like we did it in Function Test_1A:

x = Test_1D(Apple_Box1) 'Expression
'OR use Call statement 
Call Test_1D(Apple_Box1)

There will not be any value in the variable x because no value is returned from the called function.

If you feel comfortable with this method then you may do so. You will be defining one more variable for this purpose and your program takes up more memory.

Usage of Call statement requires the parenthesis around the parameter variable. If no parameters to pass to the called function even then you should use the opening and closing parenthesis at the end of Function Name like Call Test_1D().

When control returns from Test_1D() the next line displays the changed value in Apple_Box1 Variable.

Public Function Test_1C()
Dim Apple_Box1 As Long

'put 10 into Variable
Apple_Box1 = 10

'here Test_1D function takes the
'location address of Apple_Box1

Test_1D Apple_Box1 'compare this statement with Test_1A function

MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

Test_1D() function takes the location address of the parameter variable passed to it. It works directly with the value stored in the variable Apple_Box1's own location.


Public Function Test_1D(ByRef Apple_Box2 As Long)

Apple_Box2 = Apple_Box2 + 15

End Function

 Test_1D() takes the location address (this is the memory location number of the variable) of variable Apple_Box1 into variable Apple_Box2, defined in the parameter area of Test_1D() function.

Now, look at the expression:

Apple_Box2 = Apple_Box2 + 15

Apple_Box2 contains the location address, not the contents of Apple_Box1 variable. But, no need to make any change in the expression to do any kind of calculations.  Computer uses the location address to pick the value from there and use the value in calculations.

Even though we have used ByRef to the Parameter Variable, to accept the location address of the variable passed to it (always a number irrespective of different variable types), rest of the parameter definition is like any other variable with variable Type (as Long) specification.  Compare Test_1D() function definition with the Test_1B().  Test_1D doesn't have the As Long at the end of the line because it is not returning any value from the function but it changes the original value at it's location directly. 

You may omit the usage ByRef from the Function declaration. By default VBA assumes that the Function Parameter declaration is ByRef (By Reference), if you have not explicitly define the parameter as ByVal like:

Public Function Test_1D(Apple_Box2 As Long)

Each Variable Type, like Byte, Integer, Long (integer), Double, String  etc. gets allocated with enough memory cells, besides it's own address, to hold their maximum range of values. This is different among Programming Languages: VBA, C, C++, C# etc.

We don't have to bother about going too deep into all those things but it does no harm to have a basic understanding of them.

If you get in touch with C language and it's variants, like the examples given above, you need to deal with these things, sooner or later.

Our discussion here was on a single variable and it's value. How we can work with an Array of Variables and Values. We will explore it's methods in the coming weeks.


Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Objects Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Class Module Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for ...

Labels

Blog Archive

Recent Posts