title: Assigning Properties to Object using loop
body: <p>I'm new to objects and Excel VBA so apologies if this is a redundant question, but I've spent the last 2 days trying to load a new object with properties that are stored in an Excel sheet.</p>
  
  <p>I have about 60 properties that need to be loaded. 
  My main sub loads each new instance using a Load subroutine in the class. </p>
  
  <pre><code>Sub Main()
  Dim loadedCompound As Compound    'my class
  
  Set loadedCompound = New Compound  
  
  loadedCompound.Load Selection      'a sub in Compound class
  </code></pre>
  
  <p>I tried to create an array with each variable name as a level and loop through the array with the iterator linked to both the array and the offset value. But VBA wouldn't let me use the array string as a variable. </p>
  
  <pre><code>Sub Main()
  Dim loadedCompound As Compound    'my class
  Dim arrayProperties() As String   'Array of the class property names
  
  Set loadedCompound = New Compound  
  arrayProperties = 
  Split(",CDKFingerprint,SMILES,NumBatches,CompType,MolForm",",")
  
  For i = 1 To UBound(arrayProperties)
      loadedCompound.arrayProperties(i) = Selction.Offset(0, i)
  Next i
  </code></pre>
  
  <p>Currently, I manually load each variable like below, but its a lot of redundant code and is poorly readable.</p>
  
  <pre><code>Sub Load(ARID As Range)
  
  pCDKFingerprint = ARID.Offset(0, 1)
  pSMILES = ARID.Offset(0, 2)
  pNumBatches = ARID.Offset(0, 3)
  pCompType = ARID.Offset(0, 4)
  pMolForm = ARID.Offset(0, 5)
  pMW = ARID.Offset(0, 6)
  pChemName = ARID.Offset(0, 7)
  pDrugName = ARID.Offset(0, 8)
  pNickName = ARID.Offset(0, 9)
  pNotes = ARID.Offset(0, 10)
  pSource = ARID.Offset(0, 11)
  pPurpose = ARID.Offset(0, 12)
  pRegDate = ARID.Offset(0, 13)
  pCLOGP = ARID.Offset(0, 14)
  pCLOGS = ARID.Offset(0, 15)
  </code></pre>
  
  <p>The data for the variables are stored on a worksheet in rowformat.</p>
  
  <p>Is there an easy concise way to code this?</p>