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.

  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:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export 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 Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts