Modular declaration

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,416
This is in a class called Loan:

Code:
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:

Code:
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:

Code:
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,416
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?
VBA Code:
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,440
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top