Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access VBA Class Object Arrays

Introduction.

Last week we briefly introduced a simple Custom Class Object with only two Properties, Length, and Width Values. A Method for calculating the Area of anything, with Length and Width Values. Hope you understood the basics of a Ms-Access Custom Class Object.  If not, here is the link: Ms-Access Class Module and VBA. Please visit the page before continuing. Class Module Objects save 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 the 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
  Erase CA


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 the Keyboard for 5 different Items, one after the other.

The 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, declared the variable's title the 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 a 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.

The next three lines of code serve as 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  elements  (1 to 1 time) by preserving the existing Object elements, if any (this is the first Object in the Array).  This will keep incrementing, through the ReDim statement, for 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 keyword ensures that the existing array objects are not erased.

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

The Set statement in the next line copies the tmpA Object, with its 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 in the CA Object Array.

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

The next Debug.Print statement prints a 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 an 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() result in 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 address any of the Object properties 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 of 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 into the same Standard Module, and change the name to 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
Erase CA

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 the Code, and paste it below the Dim statements in the ClassPrint() Program.  The modified version of both Codes is 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
Erase CA

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 make corrections, wherever applicable.  Click in the middle of the 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 the 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.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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 Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript 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 Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box 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