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
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?