Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access VBA Class Object Arrays

Introduction.

Last Week we had a brief introduction of a simple Custom Class Object with only two Properties, 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 ClassArray() Sub-Routine.

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 L As Long, U 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
L = LBound(CA)
U = UBound(CA)

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

’stop

'Clear array objects from memory
For j = L To U
   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 

Review of Code Line by Line.

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, L and U 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 L and U 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.


Pass Class Object Array as Function Parameter.

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 L As Long, U 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
L = LBound(CA)
U = UBound(CA)

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

’stop

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

End Sub
 

The ClassArray2 Code Without Printing Section.

Create a new Sub-Routine as given below:

Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U 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 = L To U Set CA(j) = Nothing Next End Sub

The ClassPrint() Program.


Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U As Long
Dim j As Long

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

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  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.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form

Share:

4 comments:

  1. please can someone provide me with vba codes that can help me create a table with table name provided by a textbox
    and also provide field names depending on textboxes provided on a form with just a click

    ReplyDelete
  2. 1. Create a Form with three Text Boxes, with the names Text1, Text2, Text3.
    2. Create a Command Button with the Name: Command1.
    3. Copy and Paste the following Code into the Form's VBA Module and save the Form:

    Private Sub Command1_Click()
    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = CurrentDb

    ' Create a table with three fields and a unique
    ' index made up of all three fields.
    dbs.Execute "CREATE TABLE MyTable1 " _
    & "(" & Text1 & " CHAR, " & Text2 & " CHAR, " _
    & Text3 & " DATETIME, " _
    & "CONSTRAINT MyTableConstraint UNIQUE " _
    & "(" & Text1 & ", " & Text2 & ", " & Text3 & " ));"

    dbs.Close
    MsgBox "MyTable1 Created."
    End Sub

    4. Open the Form in Normal View.
    5. Type FirstName, LastName and DateOfBirth in Text Boxes.
    6. Click on the Command Button.

    The new Table MyTable1 will be created in your active database.

    ReplyDelete
  3. To get ClassArray2 to work without error, I had to define lower and upper as variables in that procedure. Otherwise, an error was thrown when encountering "For j = lower to upper / Set CA(j) = Nothing / Next." Or am I missing something?

    ReplyDelete
  4. Please check your VBA Library Files Reference List, whether your other Library Files, like Excel or any other that uses lower and upper, you have attached to your VBA. Excel have Lower & Upper Functions (access LCase, UCase) thay may cause this issue. I used lower and upper key words without much thought, to give some meaning to the usage at that spot. You may change it to L & U in place of lower and Upper.

    I will make corrections, immediately.

    ReplyDelete

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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