(Solved) – Refer to object properties dynamically


I have an exemplar of the class ClsFruit With following member variables:

enter image description here

I also have an excel sheet with data, like this:

enter image description here

I don’t feel like Populating the object using direct references like

Fruit.Name = FruitSheet.Cells(1,2).Value
Fruit.Color = FruitSheet.Cells(2,2).Value
Fruit.Price = FruitSheet.Cells(3,2).Value

is the way to go because it’s tons of repetitive code and positions of items on the worksheet might change in the future. So I wanted to loop through the first column in excel Name-Color-Priceand populate the object dynamically something like this:

Dim rg As Excel.Range
Set rg = FruitSheet.Range("A1", "A3")
Dim Cell As Variant

For Each Cell In rg
    Fruit(Cell.Value) = Cell.Offset(0, 1).Value
Next Cell

But this Fruit(Cell.Value) construct doesn’t work, I get “Object doesn’t support this property or method” error. Is there a way around it?

Leave a Reply

Your email address will not be published. Required fields are marked *