VBA: Calling one sub repeatedly and scope of variables

Question

My code as a whole works for me without issues, but what I want to improve is a small part right out of the middle of it. I hope it's ok to post a simplified snippet here that just shows the structure (it's not something you can copy paste and it works), but I'll try to explain what it is about. If this is against the rules, let me know.

---

I have a workbook that functions as a database (wbDB). I look for the number of an object (objectNumber) and, if found, add it to a sheet (wsTest) in another workbook via Sub AddObject.

Option Explicit

Public Const fRowType As Long = 4
Public Const fRowClosing As Long = fRowType + 1
Public Const fRowLoan As Long = fRowType + 4
'[there are about 30 of those fRow variables; they are needed in another _
 module, that's why they are public]

Dim wbDB As Workbook
Dim wsDB As Worksheet, wsTest As Worksheet

Dim lColTest As Long

Sub AddObject(ByVal objectNumber As String, ByVal rngObjects As Range)
    Const colType As String = "A"
    Const colClosing As String = "BN"
    Const colLoan As String = "CD"
    '[same as fRows, I have over 30 of these "col"-variables]

    Dim rowObject As Long
    Dim foundCell As Range

    Set wbDB = Application.Workbooks("Database.xlsx")
    Set wsDB = wbDB.Sheets(2)
    Set wsTest = ActiveSheet

    '---Find object in rngObjects and set rowObject to respective row
    Set foundCell = rngObjects.Find(What:=objectNumber, LookAt:=xlWhole)
    If foundCell Is Nothing Then
        MsgBox "Object " & objectNumber & "wasn't found and will be skipped."
        Exit Sub
    Else
        rowObject = foundCell.Row
    End If

    lColTest = wsTest.Cells(12, wsTest.Columns.Count).End(xlToLeft).Column
'Type
    Call ImportData(rowObject, colType, fRowType)
'Closing
    Call ImportData(rowObject, colClosing, fRowClosing)
'Loan
    Call ImportData(rowObject, colLoan, fRowLoan)
'[again, there are 30+ of these calls]

End Sub


Sub ImportData(ByVal rowDB As Long, ByVal colDB As String, ByVal fRowSection As Long)
    wsTest.Cells(fRowSection, lColTest + 1).Value = wsDB.Cells(rowDB, colDB).Value
End Sub

If you look at Sub ImportData: It requires lColTest, a module-wide variable, which I initialize in Sub AddObject. On the other hand, rowObject is needed just as often, but I put it as one of the parameters of ImportData. What is better practice? From what I've read is that the wider the scope of a variable, the less desirable it is. However, if it is one of the parameters, I currently copy paste it about 30 times with every Call and every additional parameter makes it a little less readable too.

My idea as the "cleanest" solution would be: put it in as parameter and instead of typing out every Call, I loop through it. Would you agree with that? If the answer is yes, I'd need an array or something similar.

1. What's the best option here since I need always need a pair of values (column and first row)?

2. Is there an efficient or readable way to fill the array with all these variables?

Answer 1

Public Const fRowType As Long = 4
Public Const fRowClosing As Long = fRowType + 1
Public Const fRowLoan As Long = fRowType + 4
'[there are about 30 of those fRow variables; they are needed in another _
 module, that's why they are public]

First of all, they're not "variables" they're "constants".

Second, that's screaming for an array or scripting.dictionary to hold them instead of a hoard of Const. They could even be pulled into a class that can easily be initialized provided with Getter properties (if the values are as fixed as Const would indicate) then the class can be passed around wherever it's needed.