Assigning Properties to Object using loop

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.

I have about 60 properties that need to be loaded. My main sub loads each new instance using a Load subroutine in the class.

Sub Main()
Dim loadedCompound As Compound    'my class

Set loadedCompound = New Compound  

loadedCompound.Load Selection      'a sub in Compound class

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.

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

Currently, I manually load each variable like below, but its a lot of redundant code and is poorly readable.

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)

The data for the variables are stored on a worksheet in rowformat.

Is there an easy concise way to code this?

Solution

you could use CallByName() function:

arrayProperties = Split(",CDKFingerprint,SMILES,NumBatches,CompType,MolForm", ",")
For i = 1 To UBound(arrayProperties)
    CallByName loadedCompound, "p" & arrayProperties(i), VbLet, Selection.Offset(0, i).Value
Next i

I'd add that a more robust object handling approach would require some encapsulation, to prevent accidental properties writing So, instead of exposing a Public property you would keep it Private and expose some Public Let method to set it:

so your Compound class would be:

Private pCDKFingerprint As Variant
Private pSMILES As Variant
....

Public Property Let CDKFingerprint(val As Variant)
    pCDKFingerprint = val
End Property

Public Property Let SMILES(val As Variant)
    SMILES = val
End Property

....

and hence your code would exploit it as follows:

Sub Main()
    Dim loadedCompound As Compound    'my class
    Dim arrayProperties() As String   'Array of the class property names
    Dim i As Long

    Set loadedCompound = New Compound
    arrayProperties = Split(",CDKFingerprint,SMILES,NumBatches,CompType,MolForm", ",")

    For i = 1 To UBound(arrayProperties)
        CallByName loadedCompound, arrayProperties(i), VbLet, Selection.Offset(0, i).Value
    Next i
End Sub