December 2018 ~ LEARN MS-ACCESS TIPS AND TRICKS

Ms-Access and Collection Object Basics

a.p.r. pillaiDecember 31, 2018Accesstips, Array, Collection Object 3 comments

Collection Object Basics.

A Collection object is a built-in VBA object that provides a convenient way to store and manage a group of related objects or values as a single unit. It is available in all Microsoft Office applications that support VBA, including Microsoft Access, Excel, Word, Outlook, and PowerPoint.

Unlike an array, a Collection is dynamic, meaning that items can be added or removed at run time without requiring the collection to be resized. Each item in the Collection is automatically assigned a numeric index, and it may also be assigned a unique string key for direct retrieval.

Key Characteristics

Dynamic storage – Items can be added or removed at any time during program execution.
Ordered collection – Items are maintained in the order in which they are added unless inserted at a specific position.
Heterogeneous contents – A Collection can contain objects, variables, or values of different data types.
Indexed access – Items can be accessed by their numeric position (1-based indexing).
Key-based access – Items may optionally be assigned unique string keys for faster identification.
Automatic memory management – VBA manages the internal storage of Collection, eliminating the need for manual resizing.

Typical Uses

The Collection object is widely used to:

Store instances of custom class objects.
Maintain lists of Forms or Controls.
Organize records or business objects in memory.
Build reusable object frameworks.
Manage wrapper class instances in Microsoft Access applications.
Replace dynamically resized arrays where frequent additions or deletions are required.

In VBA, Arrays are more commonly used than Collection objects for storing multiple sets of related values (in rows and columns). We have already used Arrays to store User-Defined Types and Class Module objects. Now, it’s time to explore something new—the use of Collection and Dictionary objects. The Collection object is particularly convenient for grouping related items together. As for the Dictionary object, we will discuss its usage at the appropriate time.

When using Arrays, we must dimension a variable, User-Defined Type, or Class Module object with the required number of elements in advance—or re-dimension it later to increase or decrease its size—before storing values in it. This extra step is not necessary with a Collection object. Once a Collection object is instantiated, we can dynamically add any number of items to it. Its members can be of any data type, including built-in objects, Class Module objects, or even other Collection objects with their own item members.

Collection Object Demo Program.

Let us write a simple program to demonstrate the usage of a Collection Object.

Public Sub CollTest1()
Dim C As Collection
Dim j As Integer

‘instantiate the Object
Set C = New Collection
                            
C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

'Print the items in debug window
GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
Debug.Print
For j = 1 To C.Count
    Debug.Print C.Item(j)
Next
Return

End Sub

Code Review Line By Line.

The first two lines declare the Variable C as a Collection Object. The next line declares the Variable j as an Integer type, as a control variable for the For... Next Loop. The third line instantiates the Collection Object C in memory.

The Collection Object has four built-in methods: Add, Count, Item, and Remove, for managing the Collection items in memory.

An image of the Collection Object instance below, displaying its list of methods.

Right-click to open Large Image in New Tab/Window.

The Add method adds a new Item to the Collection. The Remove method removes an item by its numeric index or key.
To access a specific item, we can use the Item method with its index number. The Count property returns the total number of items in the Collection.
In the current example, we are not working with object-type items but with a few simple mixed data types—Integer, String, and Double—as members of the Collection. Using the Add method of the Collection object named C, we have inserted five items: the first two are integers, the next two are strings, and the last one is a double-precision number.
The syntax: C.Add (Item, [Key], [Before], [After]) The Add method is followed by a space, and then the actual value to be added. Other parameters are optional.
The Add method accepts four optional parameters, as shown in the image below.

Right-click to open Large Image in New Tab/Window.
The parameters: Item, [Key], [Before], [After]. The first parameter, Item, is mandatory; the Value to be added to the Collection.
The next three parameters are optional.
When using any of the optional parameters of the Add method, you must insert comma placeholders to skip over unused parameters—except when you are specifying parameters from the rightmost side in order.
Alternatively, you can explicitly use parameter names with their values, which allows you to provide the parameters in any order.

We will explore this technique in another VBA example.

Note: Do not misinterpret the Item Parameter of the Add Method with the Item() Method of the Collection Object.

We have added five items as Collection Object members with the Add method. Two integer-type values, two String-type Values, and one double-precision number.

It demonstrates that you can add any data type, except User-Defined Type (UDTs), into the Collection. When you want to add UDTs into a Collection, convert your UDTs into a Class Module Object.

Next, the program calls a printing subroutine that outputs the Collection members to the Debug window. This subroutine uses a For...Next loop that runs from 1 to the total number of items (C.Count) in the Collection. The loop control variable j is used as the index parameter of the Collection’s Item() method to retrieve each value and print it to the Debug window.

The next Line removes the third item (iPhone) from the item members by calling the Remove method.

The printing subroutine is called one more time to print the list of items, after removing the third item from the earlier list.

The Exit Sub statement prevents the program control from dropping into the internal subroutine lines and stops the program. The listing will appear in the Debug Window as shown below.

The Output in the Debug Window.

5 
15 
iPhone
Disk 2TB
35.75

5 
15 
Disk 2TB
35.75

We can insert a value before a particular item member by specifying the item number with the Before key Name.

C.Add 2, Before:=1 ‘add value 2 Before existing first item

C.Add 2,,1

The above statement will add value 2 as the first item in the above program, pushing all existing items down.

C.Add 20, After:=3 ‘Add value 20 After existing item number 3

C.Add 20,,,3

This statement inserts the value 20 after the third item, after value 15, in the list.

The Code below demonstrates the Before:= and After:= Parameter Names.

Public Sub CollTest2()
Dim C As Collection
Dim j As Integer

Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, Before:=1 'Insert the item before the first item
C.Add 20, After:=3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing
Exit Sub

Listing:
'Print the items
Debug.Print
For j = 1 To C.Count
   Debug.Print C(j)

Next
Return

End Sub

Note: By using parameter names, you can pass values in any order you want when you need to use more than one parameter in a statement.

C.Add After:=3,Item:=20

The second example demonstrates parameter use without explicitly specifying their parameter names, placing each value in its defined correct positional order.

Public Sub CollTest2_2() Dim C As Collection Dim k As Integer

Set C = New Collection C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75 GoSub Listing C.Add 2, , 1 'Insert the item before the first item C.Add 20, , , 3 'Insert the item after first 3 items GoSub Listing Set C = Nothing Exit Sub Listing: 'Print the items Debug.Print For k = 1 To C.Count Debug.Print C(k) Next: Debug.Print Return End Sub

Sample printout in the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75

In all our printing examples, we have used the Item’s index number to retrieve values for display in the Debug Window. However, when a collection contains many items, it becomes difficult to remember the index number of a specific item we want to access. To overcome this, we can associate each item with an easily memorable Key along with its Value—for example, using a contact’s first name as the Key in an address book Collection—so we can retrieve the item’s value directly by its Key instead of relying on its index number.

Usage of Item Keys.

Let’s write a new program to demonstrate how to use Keys with Values in a Collection.

Public Sub CollTest3()
Dim C As Collection
Dim strKey As String
Dim strGet As String

Set C = New Collection

C.Add 5, Key:="FIVE" 
C.Add 15, Key:="FIFTEEN"
C.Add "iPhone", "7+"     'you can omit the KEY param name
C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY
C.Add 35.75, "999"

'add value 2 with Key "TWO" before the first item.
'this item will be the first item in the collection
'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO"

'add value 7 with Key "SEVEN" as third item in the collection
'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2

'Retrieve value using it's KEY from collection

strKey = ""
Do While strKey = ""
    strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "")
    
    Select Case strKey
        Case "Q"
           Exit Do
        Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999"
           strGet = C(strKey)
        Case Else
           strGet = " Not Found!"
   End Select
   
MsgBox "Key:<<" & strKey & ">> Value: " & strGet
strKey = ""
Loop

Set C = Nothing

End Sub

The KEY value must be of String Type. The KEY value must be a unique identifier.

Refer to the second image on this page as a reference to view the proper order of the Parameters of the Collection Object Add Method, displayed by VBA Intellisense.

The sample programs shown earlier, with mixed data types, were intended solely for demonstration purposes. In practical use, a Collection object typically stores items of a single data type, most commonly used to hold objects—such as Forms, Reports, Class Module objects, or Database objects—along with their associated properties and methods.

CLASS MODULES.

COLLECTION OBJECT.

DICTIONARY OBJECT.

Wrapper Class Functionality Transformation

a.p.r. pillaiDecember 16, 2018Array, Calculation, Class Module, VBA, Wrapper Classes 7 comments

Wrapper Class and Creation of New Objects.

Last week, we created a new Wrapper Class named ClsTiles, which used the ClsArea Class twice within the same Class Module—one instance to store the room’s floor dimensions and another to store the tile dimensions—to calculate the number of tiles needed for the room.

The Wrapper Class Module ClsVolume2 and transform it into a new ClsSales Class. With a complete facelift to the ClsVolume2 and repurposing it to calculate the product's selling price with a discount.

Interestingly, the ClsVolume2 Class already has all the necessary properties to store sales-related data: strDesc, dblLength, dblWidth, and dblHeight can be repurposed to represent Description, Quantity, Unit Price, and Discount Percentage, respectively.

Remember, the ClsVolume2 Class is a derived class, originally built using ClsArea as its base class.ClsVolume2 Class Re-Visited.

But, first, the VBA Code of the ClsVolume2 Class Module (the Base Class for our new ClsSales Class Module) is reproduced below for reference:

Option Compare Database
Option Explicit

Private p_Height As Double
Private p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

Private Sub Class_Initialize()
Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub

The only obstacle preventing us from using the ClsVolume2 Class directly for sales data entry is that its Property Procedure names—dblLength, dblWidth, and dblHeight—do not correspond to the required sales-related properties: Quantity, Unit Price, and Discount Percentage.

Fortunately, all these properties in the ClsVolume2 Class are of the Double data type, which is suitable for our sales calculations, so no data type change.

Similarly, the public function names Area() and Volume() are not meaningful in the context of sales, but their underlying calculation logic can be repurposed for sales-related computations without modification.

a) Area = dblLength * dblWidth is suitable for TotalPrice = Quantity * UnitPrice

b) Volume = Area * dblHeight is good for DiscountAmount = TotalPrice * DiscountPercentage

Here, there are two choices to repurpose the ClsVolume2 as a ClsSales Class:

The easiest approach is to create a copy of the ClsVolume2 Class and save it as a new Class Module named ClsSales. Then, update the Property Procedure names and public Function names to reflect sales-related values and calculations. You can also add any additional functions needed for handling sales operations directly within this new class module.
Create a Wrapper Class based on the ClsVolume2 Base Class. In the Wrapper Class, implement corresponding property procedures and public functions that encapsulate and rename the Base Class’s property procedures and functions, effectively masking their original names. Add new functions, if required, to the Wrapper Class as needed.

The first option is relatively straightforward and easy to implement. However, we will choose the second option to learn how to access the Base Class’s properties within the new Wrapper Class and how to mask their original names with new property names.

The Transformed ClsVolume2 Class.

Open your Database and display the VBA Editing Window (Alt+F11).
Select the Class Module from the Insert Menu to insert a new Class Module.
Change the Class Module’s Name property Value from Class1 to ClsSales.

Copy and Paste the following VBA Code into the Module and Save the Code:

Option Compare Database
Option Explicit

Private m_Sales As ClsVolume2

Private Sub Class_Initialize()
    'instantiate the Base Class in Memory
    Set m_Sales = New ClsVolume2
End Sub

Private Sub Class_Terminate()
    'Clear the Base Class from Memory
    Set m_Sales = Nothing
End Sub

Public Property Get Description() As String
  Description = m_Sales.CArea.strDesc 'Get from Base Class
End Property

Public Property Let Description(ByVal strValue As String)
  m_Sales.CArea.strDesc = strValue ' Assign to Base Class
End Property

Public Property Get Quantity() As Double
Quantity = m_Sales.CArea.dblLength
End Property

Public Property Let Quantity(ByVal dblValue As Double)
   If dblValue > 0 Then
     m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
     Do While m_Sales.CArea.dblLength <= 0
          m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
     Loop
   End If
End Property

Public Property Get UnitPrice() As Double
   UnitPrice = m_Sales.CArea.dblWidth
End Property

Public Property Let UnitPrice(ByVal dblValue As Double)
   If dblValue > 0 Then
      m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
       Do While m_Sales.CArea.dblWidth <= 0
          m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
       Loop
   End If
End Property

Public Property Get DiscountPercent() As Double
   DiscountPercent = m_Sales.dblHeight
End Property

Public Property Let DiscountPercent(ByVal dblValue As Double)
' Assign to Class .dblHeight of ClsVolume2
Select Case dblValue
    Case Is <= 0
       MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
       Do While m_Sales.dblHeight <= 0
          m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
       Loop
    Case Is >= 1
       m_Sales.dblHeight = dblValue / 100
    Case 0.01 To 0.75
       m_Sales.dblHeight = dblValue
End Select
End Property

Public Function TotalPrice() As Double
Dim Q As Double, U As Double
Q = m_Sales.CArea.dblLength
U = m_Sales.CArea.dblWidth
If (Q * U) = 0 Then
   MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
Else
   TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
End If
End Function

Public Function DiscountAmount() As Double
   DiscountAmount = TotalPrice * DiscountPercent
End Function

Public Function PriceAfterDiscount()
   PriceAfterDiscount = TotalPrice - DiscountAmount
End Function

So far in the Wrapper Class, we have created an instance of the ClsVolume2 class, renamed its property and function members to more suitable names, and added validation checks with appropriate error messages. We also ensured that invalid input does not trigger the Base Class’s own validation routines, which could otherwise display error messages like “Value of the dblLength property is invalid” from the ClsVolume2 class.

Review the highlighted lines in the code above; they should help you understand how the property values are assigned to and retrieved from the Base Class ClsVolume2.

First, review the ClsArea Class Module, and then examine the ClsVolume2 Class Module, which is derived from ClsArea. After understanding both of these, revisit the code in this Wrapper Class for a better perspective.

Test Program for ClsSales Class in Standard Module.

Let us write a Test Program to try out the Wrapper Class.

Copy and Paste the following VBA Code into a Standard Module.

Public Sub SalesTest()
Dim S As ClsSales

Set S = New ClsSales

S.Description = "Micro Drive"
S.Quantity = 12
S.UnitPrice = 25
S.DiscountPercent = 0.07

Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
With S
    Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
End With
End Sub

Run The Code.

Keep the Debug Window open (Ctrl+G).
Click somewhere in the middle of the Code and press the F5 key to run the Code and to print the output in the Debug Window.
You can further test the code by entering negative numbers for any of the input values and running it to trigger the new error messages. You may also try disabling one or more input lines by placing a comment symbol (') at the beginning of the line, then rerun the code and observe the results.

Calculate Price/Discount for an Array of Products.

The following test code creates an array of three Products and Sales Values by entering directly from the Keyboard.

Copy and paste the following Code into a Standard Module and run to test the Wrapper Class further.

Public Sub SalesTest2()

Dim S() As ClsSales
Dim tmp As ClsSales
Dim j As Long

For j = 1 To 3
Set tmp = New ClsSales
   tmp.Description = InputBox(j & ") Description")
   tmp.Quantity = InputBox(j & ") Quantity")
   tmp.UnitPrice = InputBox(j & ") UnitPrice")
   tmp.DiscountPercent = InputBox(j & ") Discount Percentage")
 ReDim Preserve S(1 To j) As ClsSales
 Set S(j) = tmp
 Set tmp = Nothing
Next

'Output Section
Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
For j = 1 To 3
   With S(j)
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
Next

For j = 1 To 3
  Set S(j) = Nothing
Next

End Sub

Once the correct values have been successfully entered into the array, the product names and corresponding sales values are displayed in the Debug Window.

CLASS MODULES.

Right-click to open Large Image in New Tab/Window.

Demo Database Download

WrapperClass.zip

COLLECTION OBJECT.

DICTIONARY OBJECT.

Access Class Module and Wrapper Classes

a.p.r. pillaiDecember 06, 2018 No comments

Class Module and Wrapper Classes.

There are times when a particular Class Module needs to be instantiated more than once to use a similar set of values for a specific application.

For example, our ClsArea Class Module, which we have designed for calculating the carpet area of Rooms, is a candidate for similar applications. Assuming we want to determine the number of floor tiles needed for a room, we should be able to input the Length and width of the tile, and the dimensions of the Floor. Since both Floor and Tile have similar Property values, we can use two instances of the ClsArea Class Module, one instance with the named FLOOR and the second instance named TILES, for area calculations. Floor Area / Tile Area gives the total number of tiles for a particular room.

Two Instances of the same Class Module.

We can do this by creating two different instances of the ClsArea Class Module in the Standard Module Program if there is only one Room.

Public Sub FloorTiles()
Dim FLOOR As ClsArea
Dim TILES As ClsArea
Dim flrArea As Double, tilearea As Double
Dim lngTiles As Long

Set FLOOR = New ClsArea
Set TILES = New ClsArea

FLOOR.strDesc = "Bed Room1"
FLOOR.dblLength = 25
FLOOR.dblWidth = 15
flrArea = FLOOR.Area()

TILES.strDesc = "Off-White"
TILES.dblLength = 2.5
TILES.dblWidth = 1.25
tilearea = TILES.Area()

lngTiles = flrArea / tilearea

Debug.Print FLOOR.strDesc & " Required Tiles: " & lngTiles & " Numbers - Color: " & TILES.strDesc

Set FLOOR = Nothing
Set TILES = Nothing

End Sub

Handling an Array of Objects.

But what if we want to apply the same method to an array of rooms, each with different dimensions or tile colors?

The solution is to create a new Class Module that contains two separate instances of the ClsArea Class—one instance to represent the Floor properties and the other to represent the Tile properties. Both of these instances can be encapsulated (wrapped) within the new Class Module.

Let us do that.

Open your Database and display the Code Editing Window (ALT+F11).
Select the Class Module from the Insert Menu.
Change the Name Property value to ClsTiles.
Copy and Paste the following VBA Code into the ClsTiles Class Module and save the Code:
```
Option Compare Database
Option Explicit

Private pFLOOR As ClsArea
Private pTILES As ClsArea

Private Sub Class_Initialize()
    Set pFLOOR = New ClsArea
    Set pTILES = New ClsArea
End Sub

Private Sub Class_Terminate()
    Set pFLOOR = Nothing
    Set pTILES = Nothing
End Sub

Public Property Get Floor() As ClsArea
 Set Floor = pFLOOR
End Property

Public Property Set Floor(ByRef NewValue As ClsArea)
  Set pFLOOR = NewValue
End Property

Public Property Get Tiles() As ClsArea
  Set Tiles = pTILES
End Property

Public Property Set Tiles(ByRef NewValue As ClsArea)
  Set pTILES = NewValue
End Property

Public Function NoOfTiles() As Long
   NoOfTiles = pFLOOR.Area() / pTILES.Area()
End Function
```
Both instances, pFLOOR and pTILES, are declared as Private Properties of the ClsTiles Class Object.
The Class_Initialize() Subroutine instantiates both objects in memory when the ClsTiles Class Module is instantiated in the user program.
The Class_Terminate() subroutine removes both instances (pFLOOR and pTILES) from memory when the ClsTiles Class Module instance is set to Nothing in the user program.
The Get and Set Property Procedures enable retrieving values from, and assigning values to, the pFLOOR instance within the ClsTiles Class Object.

We have added a new function, NoOfTiles(), in the New Class Module to calculate the number of tiles, based on the floor area and Tile dimensions.
Let us write a Program to learn several instances of the same Class Object usage in a new Class Module: ClsTiles. Or let us say encapsulation of different Class Objects into a new derived Class Object or Wrapper Class Object.
The member Class Objects are built for a particular function and considered error-free on their own merits, meaning they are individually tested, and all measures are taken to safeguard against any pitfalls that may occur during their use anywhere. It is considered a suitable object to be a part of a bigger Class object.
The next Get and Set Property Procedures allow the same operations in the pTILES instance of the ClsArea Class.

Copy and paste the following VBA Code into a Standard Module:

Public Sub TilesCalc()
Dim FTiles As ClsTiles
Dim TotalTiles As Long

Set FTiles = New ClsTiles

FTiles.Floor.strDesc = "Warehouse"
FTiles.Floor.dblLength = 100
FTiles.Floor.dblWidth = 50

FTiles.Tiles.dblLength = 2.5
FTiles.Tiles.dblWidth = 1.75

TotalTiles = FTiles.NoOfTiles()

Debug.Print "Site Name", "Floor Area", "Tile Area", "No. of Tiles"
Debug.Print FTiles.Floor.strDesc, FTiles.Floor.Area, FTiles.Tiles.Area, TotalTiles

End Sub

Keep the Debug Window Open (CTRL+G) to print the test data.
Click anywhere in the VBA Code and press the F5 Key to run the Code. The result is printed in the Debug Window.
If you need to calculate the tile requirements for multiple rooms or rooms across several floors of a high-rise building, running the above program repeatedly and manually recording the results would be impractical.

Finding Tile Requirements of Several Rooms.
Now, let us write another program to find the tile requirements for several rooms, using an Array of ClsTiles Objects by inputting the Property values directly from the keyboard.

Copy and Paste the following VBA Code into a Standard Module.

Public Sub TilesCalc2()
Dim tmpFT As ClsTiles
Dim FTiles() As ClsTiles
Dim j As Long, L As Long, H As Long

For j = 1 To 3
   Set tmpFT = New ClsTiles
      'Floor dimension
    With tmpFT.Floor
      .strDesc = InputBox(Str(j) & ") Floor Desc", , 0)
      .dblLength = InputBox(Str(j) & ") Floor Length", , 0)
      .dblWidth = InputBox(Str(j) & ") Floor Width", , 0)
    End With
    
    'Tile Dimension
    With tmpFT.Tiles
      .strDesc = InputBox(Str(j) & ") Tiles Desc", , 0)
      .dblLength = InputBox(Str(j) & ") Tile Length", , 0)
      .dblWidth = InputBox(Str(j) & ") Tile Width", , 0)
    End With
   
   ReDim Preserve FTiles(1 To j) As ClsTiles
   Set FTiles(j) = tmpFT
   
   Set tmpFT = Nothing
Next

'Take Printout
L = LBound(FTiles)
H = UBound(FTiles)

Debug.Print "FLOOR", "Floor Area", "TILES", "Tile Area", "Total Tiles"
For j = L To H
  With FTiles(j)
   Debug.Print .Floor.strDesc, .Floor.Area(), .Tiles.strDesc, .Tiles.Area(), .NoOfTiles
  End With
Next
   
'Remove all objects from memory
For j = L To H
   Set FTiles(j) = Nothing
Next
  
End Sub

Keep the Debug Window open to print the output there.
Run the Code as before and input values for Floor and Tile dimensions for three Rooms.

As you can see from the code, both Room and Tile dimensions values are input into the same Class Object Array instance, side by side.

The above program is a demo that runs only for three sets of values within the For... Next loop. It can be modified with a conditional loop that runs a required number of times till a conditional break code terminates the program.

The program can be modified to save each set of data values and calculation results in a Table for future reference.

A Wrapper Class is a container class designed to hold instances of other classes, data structures, or collections of objects, with all the capabilities of the member objects plus the new Object.