title: VBA: How to best store triplets of information together
body: <p>I need to find some rows by searching for what the rows is called, which is stored in column A. So far, every time I need to do this, I have called the function (<code>FindRow</code>) in a new line. Meaning my code looks like this:</p>
  <pre><code>Option Explicit
  
  Public fRowType As Long
  Public fRowClosing As Long
  Public fRowHPPlanDate As Long
  Public fRowLoan As Long
  Public fRowDoS As Long
  Public fRowShare As Long
  Public fRowInvestmentType As Long
  Public fRowObjectNumber As Long
  Public fRowObjectName As Long
  Public fRowRisk As Long
  Public fRowMacro As Long
  Public fRowCountry As Long
  Public fRowCity As Long
  Public fRowConstruction As Long
  Public fRowModernization As Long
  Public fRowUsage As Long
  Public fRowHPPlanYear As Long
  Public fRowHP As Long
  Public fRowInterest As Long
  Public fRowBorrowed As Long
  Public fRowLTV As Long
  Public fRowEquity As Long
  Public fRowSPVRev As Long
  Public fRowSPVExp As Long
  Public fRowNCF As Long
  Public fRowIRR As Long
  Public fRowIRRIM As Long
  Public fRowCapRate As Long
  Public fRowNOIAcq As Long
  Public fRowLeased As Long
  Public fRowRentalUnits As Long
  Public fRowParking As Long
  Public fRowTotalArea
  Public fRowRent As Long
  Public fRowNOI As Long
  Public fRowWalt As Long
  Public fRowPriceNet As Long
  Public fRowExchange As Long
  Public fRowCurrency As Long
  Public fRowPriceGross As Long
  Public fRowGIK As Long
  Public fRowBook As Long
  Public fRowMarketValue As Long
  Public fRowMarketValuePerc As Long
  Public fRowDeterminationMarketValue
  Public fRowRepatriation As Long
  Public fRowSalesPrice As Long
  
  Sub Test()
      Call DefiningRows(ActiveSheet)
  End Sub
  
  Sub DefiningRows(ws As Worksheet) 
      fRowType = FindRow(&quot;Type&quot;, &quot;A&quot;)
      fRowClosing = FindRow(&quot;Closing&quot;, &quot;A&quot;)
      fRowHPPlanDate = FindRow(&quot;Holding Period Plan Date (BP)&quot;, &quot;A&quot;)
      fRowLoan = FindRow(&quot;End of Loan&quot;, &quot;A&quot;)
      fRowDoS = FindRow(&quot;Date of Sale&quot;, &quot;A&quot;)
      fRowShare = FindRow(&quot;BVK-Share (%)&quot;, &quot;A&quot;)
      fRowInvestmentType = FindRow(&quot;Investmet Type&quot;, &quot;A&quot;)
      fRowObjectNumber = FindRow(&quot;Objectnumber&quot;, &quot;A&quot;)
      fRowObjectName = FindRow(&quot;Objectname&quot;, &quot;A&quot;)
      fRowRisk = FindRow(&quot;Risk Allocation&quot;, &quot;A&quot;)
      fRowMacro = FindRow(&quot;Macro Allocation&quot;, &quot;A&quot;)
      fRowCountry = FindRow(&quot;Country&quot;, &quot;A&quot;)
      fRowCity = FindRow(&quot;City&quot;, &quot;A&quot;)
      fRowConstruction = FindRow(&quot;Construction Year&quot;, &quot;A&quot;)
      fRowModernization = FindRow(&quot;Modernization Year&quot;, &quot;A&quot;)
      fRowUsage = FindRow(&quot;Main Usage&quot;, &quot;A&quot;)
      fRowHPPlanYear = FindRow(&quot;Holding Period Plan Year (BP)&quot;, &quot;A&quot;)
      fRowHP = FindRow(&quot;Holding Period Plan Year (BP)&quot;, &quot;A&quot;) + 2
      fRowInterest = FindRow(&quot;Interest on debt (ytd.)&quot;, &quot;A&quot;) + 1
      fRowBorrowed = FindRow(&quot;Borrowed Capital (Delta)&quot;, &quot;A&quot;) + 1
      fRowLTV = FindRow(&quot;LTV (Delta)&quot;, &quot;A&quot;) + 1
      fRowEquity = FindRow(&quot;Equity Investment (Delta)&quot;, &quot;A&quot;) + 1
      fRowSPVRev = FindRow(&quot;SPV Revenues (ytd.)&quot;, &quot;A&quot;) + 1
      fRowSPVExp = FindRow(&quot;SPV Expenses (ytd.)&quot;, &quot;A&quot;) + 1
      fRowNCF = FindRow(&quot;NCF (ytd.)&quot;, &quot;A&quot;) + 1
      fRowIRRIM = FindRow(&quot;IRR (IM)&quot;, &quot;A&quot;)
      fRowIRR = FindRow(&quot;IRR (Forecast)&quot;, &quot;A&quot;) + 1
      fRowCapRate = FindRow(&quot;CapRate (Acquisition)&quot;, &quot;A&quot;) + 1
      fRowLeased = FindRow(&quot;Leased Area (m²)&quot;, &quot;A&quot;) + 1
      fRowRentalUnits = FindRow(&quot;Rental Units&quot;, &quot;A&quot;, PartOrWhole:=xlPart)
      fRowParking = FindRow(&quot;Parking Spaces&quot;, &quot;A&quot;)
      fRowTotalArea = FindRow(&quot;Total Area (m²)&quot;, &quot;A&quot;)
      fRowRent = FindRow(&quot;Contractual Rent&quot;, &quot;A&quot;) + 1
      fRowNOI = FindRow(&quot;NOI (ytd.)&quot;, &quot;A&quot;) + 1
      fRowWalt = FindRow(&quot;WALT&quot;, &quot;A&quot;) + 1
      fRowPriceNet = FindRow(&quot;Purchase Price (net)&quot;, &quot;A&quot;) + 1
      fRowExchange = FindRow(&quot;Exchange Rate&quot;, &quot;A&quot;)
      fRowCurrency = FindRow(&quot;Currency&quot;, &quot;A&quot;)
      fRowPriceGross = FindRow(&quot;Purchase Price (gross)&quot;, &quot;A&quot;) + 1
      fRowGIK = FindRow(&quot;Total Costs (GIK)&quot;, &quot;A&quot;) + 1
      fRowBook = FindRow(&quot;Book Value&quot;, &quot;A&quot;) + 1
      fRowMarketValue = FindRow(&quot;Market Value&quot;, &quot;A&quot;) + 1
      fRowMarketValuePerc = FindRow(&quot;Market Value&quot;, &quot;A&quot;)
      fRowDeterminationMarketValue = FindRow(&quot;Determination of market value&quot;, &quot;A&quot;)
      fRowSalesPrice = FindRow(&quot;Sales Price&quot;, &quot;A&quot;)
      fRowRepatriation = FindRow(&quot;Equity Repatriation&quot;, &quot;A&quot;)
  End Sub
  
  Function FindRow(ByVal searchTerm As String, ByVal col As String, Optional ws As Worksheet, Optional ByVal PartOrWhole As XlLookAt = xlWhole) As Long
      Dim searchRng   As Range            ' the range to search, based on the column passed to the function
      Dim foundCell   As Range            ' the found match
      
      If ws Is Nothing Then Set ws = ActiveSheet
      With ws
          Set searchRng = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
      End With
      
      Set foundCell = searchRng.Find(searchTerm, LookAt:=PartOrWhole)
      If Not foundCell Is Nothing Then
          FindRow = foundCell.Row
      Else
          MsgBox searchTerm &amp; &quot; couldn't be found. Macro will continue.&quot;
      End If
  End Function
  </code></pre>
  <p>My idea was to make an array with all the <code>searchTerm</code> and loop through it. The problem is, I realized I have three pieces of information each time around: the name of the variable I want to create (like <code>fRowType </code>, the <code>searchTerm</code> and the rows I add to this (0, 1, 2). I might be overthinking, but I couldn't come up with something and decided to google my problem. One post brought up using a <code>Collection</code> object. I tried it, but quickly realized it's probably not a good idea. I didn't even create the array with the variable names because it looked like this:</p>
  <pre><code>Sub DefiningRows(ws As Worksheet) 'Loop -&gt; is xlPart necessary? if so, separate array
  
      Dim fRowCollection As Collection
      Set fRowCollection = New Collection
  
      fRowCollection.Add Array(&quot;Type&quot;, &quot;Closing&quot;, &quot;Holding Period Plan Date (BP)&quot;, &quot;End of Loan&quot;, &quot;Date of Sale&quot;, &quot;BVK-Share (%)&quot;, &quot;Investmet Type&quot;, &quot;Objectnumber&quot;, _
      &quot;Objectname&quot;, &quot;Risk Allocation&quot;, &quot;Macro Allocation&quot;, &quot;Country&quot;, &quot;City&quot;, &quot;Construction Year&quot;, &quot;Modernization Year&quot;, &quot;Main Usage&quot;, _
      &quot;Holding Period Plan Year (BP)&quot;, &quot;Holding Period Plan Year (BP)&quot;, &quot;Interest on debt (ytd.)&quot;, &quot;Borrowed Capital (Delta)&quot;, &quot;Equity Investment (Delta)&quot;, _
      &quot;SPV Revenues (ytd.)&quot;, &quot;SPV Expenses (ytd.)&quot;, &quot;NCF (ytd.)&quot;, &quot;IRR (IM)&quot;, &quot;IRR (Forecast)&quot;, &quot;CapRate (Acquisition)&quot;, &quot;NOI (Aquisition)&quot;, &quot;Leased Area (m²)&quot;, _
      &quot;Parking Spaces&quot;, &quot;Total Area (m²)&quot;, &quot;Contractual Rent&quot;, &quot;NOI (ytd.)&quot;, &quot;WALT&quot;, &quot;Purchase Price (net)&quot;, &quot;Exchange Rate&quot;, &quot;Currency&quot;, &quot;Purchase Price (gross)&quot;, _
      &quot;Total Costs (GIK)&quot;, &quot;Book Value&quot;, &quot;Market Value&quot;, &quot;Market Value&quot;, &quot;Determination of market value&quot;, &quot;Sales Price&quot;, &quot;Equity Repatriation&quot;), &quot;searchTerm&quot;
  
      fRowCollection.Add Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0), &quot;additionalRow&quot;
  
  End Sub
  </code></pre>
  <p>That doesn't look very readable to me and if I or someone else were to remove something from the array, they'd have to count through the elements to make sure they delete the right entry in <code>additionalRow</code> array. Doesn't seem practical.</p>
  <p>What's a good way to go about this?</p>