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.
What's the best option here since I need always need a pair of values (column and first row)?
Is there an efficient or readable way to fill the array with all these variables?