## Listing Collecting Loans the Hard

Sub TestCollectLoansTheHardWayC) Dim rg As Range Dim vLoans() As Variant Dim nLoan As Integer Dim dPayment As Double

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

vLoans = CollectLoansTheHardWay(rg)

Debug.Print "There are " & UBound(vLoans) + 1 & " loans."

For nLoan = 0 To UBound(vLoans)

dPayment = Payment(vLoans(nLoan, 2), _ vLoans(nLoan, 1), vLoans(nLoan, 3)) Debug.Print "Loan Number " & vLoans(nLoan, 0) & _

" has a payment of " & Format(dPayment, "Currency") Next nLoan

Set rg = Nothing End Sub

Function CollectLoansTheHardWay(rg As Range) As Variant() Dim vTerm As Variant Dim vInterestRate As Variant Dim vPrincipalAmount As Variant Dim vLoans() As Variant Dim nRows As Integer Dim nItem As Integer

' figure out how many rows there are nRows = rg.End(xlDown).Row - rg.Row

' resize the array to reflect the number ' of rows.

ReDim vLoans(nRows, 3)

' initialize array loan index nItem = 0

' ok - read in the values Do Until IsEmpty(rg) ' loan number vLoans(nItem, 0) = rg.Value ' term vLoans(nItem, 1) = rg.Offset(0, 1).Value ' interest rate vLoans(nItem, 2) = rg.Offset(0, 2).Value ' principal amount vLoans(nItem, 3) = rg.Offset(0, 3).Value Set rg = rg.Offset(1, 0)

Loop

CollectLoansTheHardWay = vLoans

### End Function

Flip back and forth between this listing and the previous listing. If I took out all of the comments, to what extent could you tell exactly what was going on in each of these listings? Trust me, as your projects get more complex, the mental simplification that using classes affords is a huge deal.

There is another significant difference between this listing and the previous listing. Listing 11.7 is bound to the display of the data in two procedures versus only one procedure in the previous listing. So if the layout of the data changes (the term and interest rate swap columns, for example), you need to fix two procedures. This issue would become a big deal in a more complicated (in other words, real-life) application. You'd basically need to review and repair any procedure that used the array of loan data. With a collection of Loan objects, the only thing you'd need to do is adjust the procedure that reads the data from the worksheet.

Of course, you could mitigate the impact of this problem using constants that map a constant name to a column number (for example, CONST INTEREST_RATE = 3). This would help, but you still wouldn't have nearly as much flexibility as you do when you're using an object.

0 0