Assigning Properties to Object using loop

Question

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?

Without seeing the main subscript we couldn't really guess. We know you are putting a value on a lot of cells based on the given range but we don't know how you get those values on the first place, so this seems to be the best method so far. Do those come from a UserForm? if so you can skip the variable assignment and put the value directly on the Cells. — Ricardo A, Jun 08 '18 at 15:24
it looks like you may be able to use a dictionary object - you can read about them [here](https://stackoverflow.com/a/915333/7099906), and should be able to set the value with something of the form `dict.item("key")=value` — Taylor Alex Raine, Jun 08 '18 at 15:41
Thanks Ricardo. I updated with more information including the main sub. The values are already in the worksheet and not a userform I'm making an object because working with the 60 variables is challenging and my old code is becoming spaghetti! — St. Jimmy, Jun 08 '18 at 15:42
How do you know `Selection` is in the right place and thus that you're loading the right values into the right properties?? Anyway when you need a bunch of key-value pairs, the data structure to think about is a `Dictionary` (or a `Collection`, if you don't need to iterate the keys themselves). — Mathieu Guindon, Jun 08 '18 at 16:41
There is a data validator step and the appropriate selection values ALWAYS start with the same 4 char. I will update it to a little more sophisticated code, Im just trying to handle objects for now. Thanks for the dictionary and collection advice, Im reading up on them and think these will work! Thanks again! — St. Jimmy, Jun 08 '18 at 16:56

DisplayName · Accepted Answer · 2018-06-09T07:32:48.510

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

Assigning Properties to Object using loop

1 Answers1