Listing Using the Collection Object to Serve As a Container for Multiple Objects

Sub TestCollectionObject() Dim rg As Range Dim objLoans As Collection Dim objLoan As Loan

Set rg = ThisWorkbook.Worksheets("Loans"). _ Range("LoanListStart").Offset(1, 0)

' get the collection of loan objects Set objLoans = CollectLoanObjects(rg)

Debug.Print "There are " & objLoans.Count & _ " loans."

' iterate through each loan For Each objLoan In objLoans

Debug.Print "Loan Number " & objLoan.LoanNumber & _

" has a payment of " & Format(objLoan.Payment, "Currency")

Next

Set objLoans = Nothing Set objLoan = Nothing Set rg = Nothing End Sub

Function CollectLoanObjects(rg As Range) As Collection Dim objLoan As Loan Dim objLoans As Collection

Set objLoans = New Collection

' loop until we find an empty row Do Until IsEmpty(rg)

Set objLoan = New Loan With objLoan

.LoanNumber = rg.Value .Term = rg.Offset(0, 1).Value .InterestRate = rg.Offset(0, 2).Value .PrincipalAmount = rg.Offset(0, 3).Value End With

' add the current loan to the collection objLoans.Add objLoan, CStr(objLoan.LoanNumber)

' move to next row

Loop

Set objLoan = Nothing Set CollectLoanObjects = objLoans Set objLoans = Nothing End Function

The output of the TestCollectionObject procedure is as follows.

There are 5

loans.

Loan

Number

1

has

a

payment

of

$506.

.91

Loan

Number

2

has

a

payment

of

$1,342.51

Loan

Number

3

has

a

payment

of

$373.

.27

Loan

Number

4

has

a

payment

of

$852.

40

Loan

Number

5

has

a

payment

of

$944.

57

The procedure of interest in this listing is CollectLoanObjects. Notice that the Collection object needs to be instantiated using the New keyword. The other statement to pay attention to is the one you use to add the current loan in the objLoan variable to the objLoans collection. This statement uses the Add method of the Collection object.

objCollection.Add item,[key],[before],[after]

The parameters of the Add method are explained in the following list.

Item Item is required and represents the object to add to the collection.

Key Key (optional) is a unique string that can be used to refer to the item using the key value rather than the index number of the item.

Before Use Before to insert the new object at the position before the item given by this parameter. You can specify either an index number or a key value. You can't specify After if you specify Before.

After Use After to insert the new object at the position after the item given by this parameter. You can specify either an index number or a key value. You can't specify Before if you specify After.

Now, you can still perform this same kind of thing without using classes. However, I think you'll agree that things start getting tricky and the result is two procedures that are much harder to read than the two presented in Listing 11.6. Listing 11.7 produces exactly the same output as Listing 11.6.

0 0

Post a comment