Declaring variable

[confusion123]

In the code below, is there any reason why these are declared twice, once in the Sub and once in the Function?

Dim objLoan As Loan

Dim objLoans As Collection

Could they not been declared once only at the top of the module?

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


Set rg = rg.Offset(1, 0)

Loop

Set objLoan = Nothing

Set CollectLoanObjects = objLoans

Set objLoans = Nothing

End Function

 

[ScottR]

Re: Declaring varable

I see people who always use a variable of "i" the first time they do a loop in any procedure (if they have a second loop, they will use j, etc). Nothing wrong with that.

I, to give another example, absolutely cannot shake the habit of using x and y (and thinking of them as axis) when looping through rows and columns in Excel.

Almost every example you'll find online of a Javascript for loop will also use i (including W3 examples). Eg

For(var i=0;i<50;i++)