Modular declaration

[tiredofit]

This is in a class called Loan:

Option Explicit
  
    Dim mvPrincipalAmount As Variant
    Dim mvInterestRate As Variant
    Dim mvLoanNumber As Variant
    Dim mvTerm As Variant

Public Property Get PrincipalAmount() As Variant
    PrincipalAmount = mvPrincipalAmount
End Property

Public Property Let PrincipalAmount(ByVal vNewValue As Variant)
    mvPrincipalAmount = vNewValue
End Property

Public Property Get InterestRate() As Variant
    InterestRate = mvInterestRate
End Property

Public Property Let InterestRate(ByVal vNewValue As Variant)
    mvInterestRate = vNewValue
End Property

Public Property Get LoanNumber() As Variant
    LoanNumber = mvLoanNumber
End Property

Public Property Let LoanNumber(ByVal vNewValue As Variant)
    mvLoanNumber = vNewValue
End Property

Public Property Get Term() As Variant
    Term = mvTerm
End Property

Public Property Let Term(ByVal vNewValue As Variant)
    mvTerm = vNewValue
End Property

Public Property Get Payment() As Variant
    Payment = Application.WorksheetFunction.Pmt(mvInterestRate / 12, mvTerm, -mvPrincipalAmount)
End Property

This is in a standard module:

Option Explicit

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

The code works fine but I noticed both objLoan and objLoans have been declared twice, once in TestCollectionObject and once in the function.

Would it be possible, possibly even preferable, to declare it only once at the modular level, ie:

Dim objLoan As Loan
Dim objLoans As Collection

Sub TestCollectionObject()

    ' etc.

or have I missed the point of why they have been declared separately?

Thanks

 

[tiredofit]

Not sure if there is something that I'm missing, but in my opinion the code appears to be over-engineered enough to work either way, my thought was something more like the quick edit below, although I am wondering if the function is even necessary, or if the entire collection could be populated directly in the sub as an array without looping?

Option Explicit

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

End Sub

Function CollectLoanObjects(rg As Range) As Collection

    Dim FobjLoan As Loan
    Dim FobjLoans As Collection
    Set FobjLoans = New Collection

    ' loop until we find an empty row

    Do Until IsEmpty(rg)

        Set FobjLoan = New Loan

        With FobjLoan

            .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

        FobjLoans.Add FobjLoan, CStr(FobjLoan.LoanNumber)

        ' move to next row

        Set rg = rg.Offset(1, 0)

    Loop

    Set CollectLoanObjects = FobjLoans

End Function

Perhaps the source material is trying to do more than is necessary.

No idea but thanks for your help.